Mydumper & Myloader Documentation

Multi-threaded MySQL/MariaDB backup and restore tool — a faster alternative to mysqldump with parallel dumping and consistent snapshots.

Originally published July 2015

On This Page

Archived Documentation

Centmin Mod is only hosting this mydumper documentation as a reference. The original mydumper.org website has been offline for some time. This content is provided as-is without technical support.

The mydumper project has moved to github.com/mydumper/mydumper — check there for the latest releases and documentation.

For current MySQL/MariaDB backup options in Centmin Mod (MariaBackup, mysqldump, Menu Option 21 Data Management), see the MySQL documentation page.

Mydumper is a tool for high-performance MySQL/MariaDB backups using multi-threaded parallel dumping. Benchmarks were published at vbtechsupport.com/1695/ and vbtechsupport.com/1716/.

Download: Get the latest mydumper version from github.com/mydumper/mydumper (previously hosted on Launchpad).

Authors

The code for mydumper was written by the following people:

Compiling

Requirements

Mydumper requires the following before it can be compiled:

  • CMake — cmake.org
  • Glib2 — gtk.org (with development packages)
  • PCRE — pcre.org (with development packages)
  • MySQL — mysql.com client libraries (with development packages)

Additionally the following packages are optional:

Ubuntu/Debian

apt-get install libglib2.0-dev libmysqlclient15-dev zlib1g-dev libpcre3-dev

Fedora/Redhat/CentOS

yum install glib2-devel mysql-devel zlib-devel pcre-devel

OpenSUSE

zypper install glib2-devel libmysqlclient-devel pcre-devel zlib-devel

Mac OS X

port install glib2 mysql5 pcre

CMake Build

CMake is used for mydumper’s build system and is executed as follows:

cmake .
make

You can optionally provide parameters for CMake:

  • -DMYSQL_CONFIG=/path/to/mysql_config — The path and filename for the mysql_config executable
  • -DCMAKE_INSTALL_PREFIX=/install/path — The path where mydumper should be installed

Building Documentation

If you wish to just compile the documentation you can do so with:

cmake .
make doc_html

Or for a man page output:

cmake .
make doc_man

Mydumper Usage

Synopsis

mydumper [OPTION...]

Description

mydumper is a tool used for backing up MySQL database servers much faster than the mysqldump tool distributed with MySQL. It also has the capability to retrieve the binary logs from the remote server at the same time as the dump itself. The advantages of mydumper are:

  • 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

Options

The mydumper tool has the following available options:

Option Description
--help, -? Show help text
--host, -h Hostname of MySQL server to connect to (default localhost)
--user, -u MySQL username with the correct privileges to execute the dump
--password, -p The corresponding password for the MySQL user
--port, -P The port for the MySQL connection. For localhost TCP connections use 127.0.0.1 for --host.
--socket, -S The UNIX domain socket file to use for the connection
--database, -B Database to dump
--table-list, -T A comma separated list of tables to dump
--threads, -t The number of threads to use for dumping data, default is 4. Other threads are used in mydumper; this option does not control those.
--outputdir, -o Output directory name, default is export-YYYYMMDD-HHMMSS
--statement-size, -s The maximum size for an insert statement before breaking into a new statement, default 1,000,000 bytes
--rows, -r Split table into chunks of this many rows, default unlimited
--compress, -c Compress the output files
--compress-input, -C Use client protocol compression for connections to the MySQL server
--build-empty-files, -e Create empty dump files if there is no data to dump
--regex, -x A regular expression to match against database and table
--ignore-engines, -i Comma separated list of storage engines to ignore
--no-schemas, -m Do not dump schemas with the data
--long-query-guard, -l Timeout for long query execution in seconds, default 60
--kill-long-queries, -k Kill long running queries instead of aborting the dump
--version, -V Show the program version and exit
--verbose, -v The verbosity of messages. 0 = silent, 1 = errors, 2 = warnings, 3 = info. Default is 2.
--binlogs, -b Get the binlogs from the server as well as the dump files
--daemon, -D Enable daemon mode
--snapshot-interval, -I Interval between each dump snapshot (in minutes), requires --daemon, default 60
--logfile, -L A file to log mydumper output to instead of console output. Useful for daemon mode.
--no-locks, -k Do not execute the temporary shared read lock. Warning: This will cause inconsistent backups.

Myloader Usage

Synopsis

myloader [OPTION...]

Description

myloader is a tool used for multi-threaded restoration of mydumper backups.

Options

The myloader tool has the following available options:

Option Description
--help, -? Show help text
--host, -h Hostname of MySQL server to connect to (default localhost)
--user, -u MySQL username with the correct privileges to execute the restoration
--password, -p The corresponding password for the MySQL user
--port, -P The port for the MySQL connection. For localhost TCP connections use 127.0.0.1 for --host.
--socket, -S The UNIX domain socket file to use for the connection
--threads, -t The number of threads to use for restoring data, default is 4
--version, -V Show the program version and exit
--compress-protocol, -C Use client protocol compression for connections to the MySQL server
--directory, -d The directory of the mydumper backup to restore
--database, -B An alternative database to load the dump into. For use with single database dumps. When using with multi-database dumps that have duplicate table names it may cause errors.
--queries-per-transaction, -q Number of INSERT queries to execute per transaction during restore, default is 1000
--overwrite-tables, -o Drop any existing tables when restoring schemas
--enable-binlog, -e Log the data loading in the MySQL binary log if enabled (off by default)
--verbose, -v The verbosity of messages. 0 = silent, 1 = errors, 2 = warnings, 3 = info. Default is 2.

Output Files

Mydumper generates several files during the dump. Many of these are for the table data itself since every table has at least one file.

Metadata

When a dump is executed a file called .metadata is created in the output directory. This contains the start and end time of the dump as well as the master binary log positions if applicable.

Example content of the metadata file:

Started dump at: 2011-05-05 13:57:17
SHOW MASTER STATUS:
Log: linuxjedi-laptop-bin.000001
Pos: 106

Finished dump at: 2011-05-05 13:57:17

Table Data

The data from every table is written into a separate file. If the --rows option is used then each chunk of table will be in a separate file. The file names are in the format:

database.table.sql(.gz)

Or if chunked:

database.table.chunk.sql(.gz)

Where chunk is a number padded with up to 5 zeros.

Table Schemas

When the --schemas option is used, mydumper will create a file for the schema of every table it is writing data for. The files are in the following format:

database.table-schema.sql(.gz)

Binary Logs

Binary logs are retrieved when the --binlogs option has been set. This will store them in the binlog_snapshot/ sub-directory inside the dump directory.

The binary log files have the same filename as the MySQL server that supplies them and will also have a .gz extension if they are compressed.

Daemon Mode

Daemon mode works differently. There are directories 0 and 1 inside the dump directory. These alternate when dumping so that if mydumper fails for any reason there is still a good snapshot. When a snapshot dump is complete the last_dump symlink is updated to point to that dump.

If binary logging is enabled, mydumper will connect as if it is a slave server and continuously retrieve the binary logs into the binlogs subdirectory.

Examples

Simple Usage

Running mydumper without any options will try to connect to a server using the default socket path. It will then dump the tables from all databases using 4 worker threads.

Regex Filtering

To use mydumper’s regex feature, use the --regex option. In the following example mydumper will ignore the test and mysql databases:

mydumper --regex '^(?!(mysql|test))'

Restoring a Dump

Mydumper includes myloader which is a multi-threaded restoration tool. To use myloader with a mydumper dump you simply need to pass it the directory of the dump along with a user capable of restoring the schemas and data. The following will restore a dump overwriting any existing tables:

myloader --directory=export-20110614-094953 --overwrite-tables --user=root

Daemon Mode

Mydumper has a daemon mode which will snapshot the dump data periodically whilst continuously retrieving the binary log files. This gives a continuous consistent backup right up to the point where the database server fails. In the following example mydumper will use daemon mode, creating a snapshot every half an hour and log to an output file:

mydumper --daemon --snapshot-interval=30 --logfile=dump.log

Looking for Current Backup Options?

This page is hosted as a reference for the open-source mydumper tool. For current MySQL/MariaDB backup and restore options in Centmin Mod, including MariaBackup, mysqldump, and Menu Option 21 Data Management:

View MySQL Backup Documentation