MyDumper is a MySQL Logical Backup Tool. It has 2 tools:
mydumper(Backup) which is responsible to export a consistent backup of MySQL databases.myloader(Restore) reads the backup from mydumper, connects the to the destination database, and imports the backup.
Both tools use multithreading capabilities.
Advantages of using Mydumper & Myloader
- Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
- Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
- Consistency — maintains snapshot across all threads, provides accurate master and slave log positions, etc
- Manageability — supports PCRE for specifying database and tables inclusions and exclusions
GitHub Link: https://github.com/mydumper/mydumper
Install mydumper on ubuntu
As always we’ll start with updating the package information.
sudo apt update
We need to install the dependencies.
sudo apt-get install libatomic1 -y
Install the latest mydumper version.
release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
wget https://github.com/mydumper/mydumper/releases/download/${release}/mydumper_${release:1}.$(lsb_release -cs)_amd64.debsudo dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb
Verify the installation.
mydumper --version
Sample Output:
Take Backup of MySQL
We’ll use mydumper command to take backup.
You can run the below command to get a list of all the available options.
mydumper --help
You can use the below command to take a Backup.
mydumper --threads 7 \ --host $MYSQL_HOSTNAME \ --user $MYSQL_USER \ --password $MYSQL_PASSWORD \ --database $DATABASE_NAME \ --compress \ --rows="10000000" \ --verbose 3 \ --long-query-guard 999999 \ --no-locks \ --compress-protocol \ --outputdir /<path-to-dump-data>/$DATABASE_NAME \ --logfile /<path-to-save-logs>/backup-$DATABASE_NAME.log
Don’t forget to replace the variables
Restore Database Using MySQL
We’ll use myloader command to take backup.
You can run the below command to get a list of all the available options.
myloader --help
You can use the below command to Restore the Database.
myloader --threads 7 \ --host $MYSQL_HOSTNAME \ --user $MYSQL_USER \ --password $MYSQL_PASSWORD \ --database $DATABASE_NAME --directory /<path-to-dump-data>/$DATABASE_NAME \ --queries-per-transaction 50000 \ --verbose 3 \ --compress-protocol \ --logfile /<path-to-save-logs>/restore-$DATABASE_NAME.log
Note: You can adjust the number of threads as per your system resources. For starting you can set the number of threads equal to the number of CPU cores.
I hope this article helps you with doing MySQL backup and restore.