What is mydumper?

Mydumper is a logical MySQL backup tool that aims to combine both speed and performance. The tool is frequently used to back up MySQL database servers quickly since it is faster than the mysqldump tool provided by MySQL. The key advantages of mydumper are:

  • Aiming to combine both speed and performance – mydumper is built on efficient code, it also avoids expensive character set conversion routines;
  • Output – the tool provides data that is easy to view and parse, separate files for tables, etc.;
  • Consistency – mydumper provides accurate master and slave log positions, etc.;
  • Ease of use – mydumper supports Perl Compatible Regular Expressions for specifying database and table inclusions or exclusions.

Usage

In order to use mysqldumper, you need to install it first. To do so, get the needed release, then run the following (CentOS / RedHat):

$ yum install

https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-1.el7.x86_64.rpm

The tool can be invoked by typing mydumper. It also has a pretty long set of options, so these can be provided too. Here’s a few of them:

  • The --host or -h option enables to provide the hostname of the server;
  • The --user or -u option enables to provide the username of the MySQL user that has the privileges to execute the dump;
  • The --password or -p option enables to provide the password of the MySQL user;
  • The --port or -p option enables to specify the port;
  • The --database or -B option enables to specify the database to be dumped;
  • The --table-list or T option enables to specify a comma separated list of tables to be dumped;
  • The –threads or -t option enables to specify the number of threads to be used for dumping data (default – 4);
  • The --outputdir or o option enables to specify the output directory;
  • The --statement-size or s option enables to specify the maximum size of an insert statement before breaking into a new statement;
  • The --rows or -r option enables to split a table into a chunk of x rows;
  • The -compress or -c option enables to compress the output files;
  • The --regex or -x option enables to specify a regular expression to match against databases and tables;
  • The --ignore-engines or -i option enables to specify a comma separated list of database engines to ignore;
  • The --long-query-guard or -l option enables to specify what query execution time is required for it to timeout (default value – 60);
  • The -kill-long-queries or -k option enables to kill long queries;
  • The --no-schemas or -m option enables to avoid dumping schemas with the data.

Since mydumper 0.9.1, the following options are also available:

  • The --no-data or -d option enables to avoid dumping table data;
  • The --triggers or -G option enables to dump triggers;
  • The --events or -E option enables to dump events;
  • The -routines or -R option enables to dump stored procedures and functions;
  • The --source-db or -s option enables to specify a database to restore.

Speed considerations

Since mydumper has the ability to run in a multi-threaded fashion backing up tables in parallel, it’s generally faster than mysqldump. The tool is able to run quickly because all it does is extract data and write it to files – fast. How fast? Well, according to its authors, when mydumper was tested on a database that is around 20GB in size, mysqldump backed up the data in 1 hour, 15 minutes and 18 seconds, mydumper, on the other hand, completed the same dump in 6 minutes and 44 seconds. MySQL was restarted before every test and the InnoDB flush method was set to O_DIRECT.

Summary

Mysqldumper is a tool that is specifically targeted towards logical data backups for large MySQL environments – since the tool has the ability to run in a multi-threaded fashion, it is generally faster than the usual MySQL backup tools. It is also worth mentioning that mysqldumper also has a FAQs page which can have answers to the questions that might arise, though note that it hasn’t been updated in a while.

Contributor
Comments to: mydumper

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.