Easily Backup and Restore MySQL using MyDumper

December 11, 2022

backupdatabasedumpmydumpermysqlrestoresqltools
Easily 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 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.