If you want to execute logical backups using an alternative to mysqldump that works with parallel threads giving faster execution times mydumper is the correct tool.
wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm
In order to use mydumper and myloader you can use the following sample commands:
For dumping/exporting database:
mydumper -u <username> -p <password> -B <database name> -d /path/to/dump
For importing database (for percona or master-master replication):
myloader -u <username> -p <password> -B <database name> -d /dump/path --enable-binlog
For importing database (for standalone mysql):
myloader -u <username> -p <password> -B <database name> -d /dump/path
Detailed Syntax:
Syntax mydumper [options] Applicat=ion Options: -B, --database Database to dump -T, --tables-list Comma delimited table list to dump (does not exclude regex option) -o, --outputdir Directory to output files to -s, --statement-size Attempted size of INSERT statement in bytes, default 1000000 -r, --rows Try to split tables into chunks of this many rows -c, --compress Compress output files -e, --build-empty-files Build dump files even if no data available from table -x, --regex Regular expression for ‘db.table' matching -i, --ignore-engines Comma delimited list of storage engines to ignore -m, --no-schemas Do not dump table schemas with the data -k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups -l, --long-query-guard Set long query timer in seconds, default 60 --kill-long-queries Kill long running queries (instead of aborting) -b, --binlogs Get a snapshot of the binary logs as well as dump data -D, --daemon Enable daemon mode -I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60 -L, --logfile Log file name to use, by default stdout is used -h, --host The host to connect to -u, --user Username with privileges to run the dump -p, --password User password -P, --port TCP/IP port to connect to -S, --socket UNIX domain socket file to use for connection -t, --threads Number of threads to use, default 4 -C, --compress-protocol Use compression on the MySQL connection -V, --version Show the program version and exit -v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
This is how you would use Mydumper for create a backup of a MySQL database, replace the variables (bash words starting with $) with the actual values. Once this process is complete you can zipup the folder and transfer it to the destination folder.
mydumper \ --database=$DB_NAME \ --host=$DB_HOST \ --user=$DB_USER \ --password=$DB_PASS \ --outputdir=$DB_DUMP \ --rows=500000 \ --compress \ --build-empty-files \ --threads=2 \ --compress-protocol
If you want to restore these backup you can use Myloader
myloader \ --database=$DB_NAME \ --directory=$DB_DUMP \ --queries-per-transaction=50000 \ --threads=10 \ --compress-protocol \ --verbose=3
Src:
Extremely Fast MySQL Backup and Restore Using Mydumper/Myloader
https://2bits.com/backup/fast-parallel-mysql-backups-and-imports-mydumper.html
