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.
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
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:
-hoption enables to provide the hostname of the server;
-uoption enables to provide the username of the MySQL user that has the privileges to execute the dump;
-poption enables to provide the password of the MySQL user;
-poption enables to specify the port;
-Boption enables to specify the database to be dumped;
Toption 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);
ooption enables to specify the output directory;
soption enables to specify the maximum size of an insert statement before breaking into a new statement;
-roption enables to split a table into a chunk of x rows;
- The –
-coption enables to compress the output files;
-xoption enables to specify a regular expression to match against databases and tables;
-ioption enables to specify a comma separated list of database engines to ignore;
-loption enables to specify what query execution time is required for it to timeout (default value – 60);
- The –
-koption enables to kill long queries;
-moption enables to avoid dumping schemas with the data.
Since mydumper 0.9.1, the following options are also available:
-doption enables to avoid dumping table data;
-Goption enables to dump triggers;
-Eoption enables to dump events;
- The –
-Roption enables to dump stored procedures and functions;
-soption enables to specify a database to restore.
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
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.