Easily Backup and Restore MySQL using MyDumper

backup-and-restore-mysql-using-mydumper

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 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.deb
sudo dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb

Verify the installation.

mydumper --version

Sample Output:

mydumper version

Take Backup of MySQL

We’ll use mydumper command to take backup.

You can run below command to get list of all the available options.

mydumper --help

You can use below command to take 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 below command to get list of all the available options.

myloader --help

You can use below command to Restore 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 number threads equal to number of CPU cores.

I hope this article help you with doing MySQL backup and restore.