What is MyDumper?

MyDumper is a MySQL Logical Backup Tool. It has 2 tools:

• mydumper which is responsible to export a consistent backup of MySQL databases
• myloader reads the backup from mydumper, connects to the destination database and imports the backup.

Both tools use multithreading capabilities. MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB or MySQL product.

Why do we need MyDumper?

• 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

Documentation

Official Documentation (work in progress) Machine JSON Logging

How to install mydumper/myloader?

This sections has been migrated

Dependencies for building MyDumper

This sections has been migrated

Build Docker image

You can download the official docker image or you can build the Docker image either from local sources or directly from Github sources with the provided Dockerfile.

docker build --build-arg CMAKE_ARGS='-DWITH_ZSTD=ON' -t mydumper \
    https://github.com/mydumper/mydumper.git#master:docker

Keep in mind that the main purpose the Dockerfile addresses is development and build from source locally. It might not be optimal for distribution purposes, but can also work as a quick build and run solution with the above one-liner, though.

How to use MyDumper

See Usage

How does consistent snapshot work?

This is all done following best MySQL practices and traditions:

• As a precaution, slow running queries on the server either abort the dump, or get killed
• Global read lock is acquired ("FLUSH TABLES WITH READ LOCK")
• Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")
• Other threads connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT") ** On pre-4.1.8 it creates a dummy InnoDB table, and reads from it.
• Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.

This for now does not provide consistent snapshots for non-transactional engines - support for that is expected in 0.2 :)

How to exclude (or include) databases?

This sections has been migrated

How to use --exec?

You can execute external commands with --exec like this:

 mydumper --exec "/usr/bin/gzip FILENAME"

--exec is single threaded, similar implementation than Stream. The exec program must be an absolute path. FILENAME will be replaced by the filename that you want to be processed. You can set FILENAME in any place as an argument.

Defaults file

The default file (aka: --defaults-file parameter) is starting to be more important in MyDumper

• mydumper and myloader sections:

[mydumper]
host = 127.0.0.1
user = root
password = p455w0rd
database = db
rows = 10000
string-pk-planner = auto
string-pk-planner-timeout = 30
string-pk-planner-max-probes = 64
string-pk-planner-max-prefixes = 256
string-pk-planner-min-rows = 1000000
string-pk-planner-target-rows-per-prefix = 0

[myloader]
host = 127.0.0.1
user = root
password = p455w0rd
database = new_db
optimize-keys = AFTER_IMPORT_PER_TABLE

• Variables for mydumper and myloader executions:

Prior to v0.14.0-1:

[mydumper_variables]
wait_timeout = 300
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[myloader_variables]
long_query_time = 300
innodb_flush_log_at_trx_commit = 0

From to v0.14.0-1:

[mydumper_session_variables]
wait_timeout = 300
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mydumper_global_variables]
sync_binlog = 0
slow_query_log = OFF

[myloader_session_variables]
long_query_time = 300

[myloader_global_variables]
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0

For Aurora/MySQL 5.7 restores where SET SESSION SQL_LOG_BIN = 0 is rejected, use:

[myloader]
source-control-command = AWS
aws-session-command = CALL mysql.rds_disable_session_binlog()

aws-session-command is executed on every myloader connection after the normal session setup. You can repeat the option on multiple lines in the config file, or pass multiple statements separated by ;\n. When --source-control-command=AWS is set, --enable-binlog is ignored and myloader uses the AWS session binlog call instead. See myloader.cnf for a complete sample.

Examples:

[myloader]
source-control-command = AWS
aws-session-command = CALL mysql.rds_disable_session_binlog()
aws-session-command = SET SESSION some_aws_setting = 1
aws-session-command = CALL mysql.some_other_aws_proc()

myloader \
  --source-control-command=AWS \
  --aws-session-command='CALL mysql.rds_disable_session_binlog()' \
  --aws-session-command='SET SESSION some_aws_setting = 1' \
  --aws-session-command='CALL mysql.some_other_aws_proc()'

以上為 README 的開頭部分。其餘內容請閱讀原文。 檢視完整 README ↗