[root@wallet01 ~]# yum install -y glib2-devel MySQL-devel zlib-devel \
pcre-devel openssl-devel cmake
[root@wallet01 ~]# git clone https://github.com/maxbube/mydumper.git
Initialized empty Git repository in /root/mydumper/.git/
error: while accessing https://github.com/maxbube/mydumper.git/info/refs
fatal: HTTP request failed
[root@wallet01 ~]# yum update -y nss curl libcurl
[root@wallet01 ~]# git clone https://github.com/maxbube/mydumper.git
Initialized empty Git repository in /root/mydumper/.git/
remote: Enumerating objects: 1185, done.
remote: Total 1185 (delta 0), reused 0 (delta 0), pack-reused 1185
Receiving objects: 100% (1185/1185), 983.01 KiB | 573 KiB/s, done.
Resolving deltas: 100% (724/724), done.
[root@wallet01 ~]# cd mydumper
[root@wallet01 mydumper]# cmake .
[root@wallet01 mydumper]# make
[root@wallet01 mydumper]# make install
[root@wallet01 ~]# mydumper --help
-B, --database 需要备份的数据库，不指定则备份全部库
-T, --tables-list 需要备份的表，多表用逗号隔开
-O, --omit-from-file File containing a list of database.table entries to skip, one per line (skips before applying regex option)
-o, --outputdir 备份目录
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
-c, --compress 压缩备份文件
-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
-N, --insert-ignore Dump rows with INSERT IGNORE
-m, --no-schemas 不备份表结构，仅备份表数据
-d, --no-data 不备份表数据，仅备份表结构
-G, --triggers 备份与表关联的触发器
-E, --events 备份数据库的事件
-R, --routines 备份数据库的存储过程和函数
-W, --no-views 不备份数据库的视图
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
--no-backup-locks Do not use Percona backup locks
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard Set long query timer in seconds, default 60
-K, --kill-long-queries Kill long running queries (instead of aborting)
-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
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--lock-all-tables Use LOCK TABLE for all, instead of FTWRL
--trx-consistency-only Transactional consistency only
--complete-insert Use complete INSERT statements that include column names
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads 备份使用的线程数量，默认是 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
备份全部库：除了information_schema与performance_schema之外的库都会被备份
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -o /home/mysql/backup
备份指定的库
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -B tpcc100 -o /home/mysql/backup
[mysql@wallet01 ~]$ cd backup
[mysql@wallet01 backup]$ ls -lh
total 732M
-rw-rw-r-- 1 mysql mysql 141 Feb 1 09:08 metadata
-rw-rw-r-- 1 mysql mysql 1.4K Feb 1 09:07 tpcc100.customer-schema.sql
-rw-rw-r-- 1 mysql mysql 172M Feb 1 09:08 tpcc100.customer.sql
-rw-rw-r-- 1 mysql mysql 804 Feb 1 09:07 tpcc100.district-schema.sql
-rw-rw-r-- 1 mysql mysql 11K Feb 1 09:07 tpcc100.district.sql
-rw-rw-r-- 1 mysql mysql 481 Feb 1 09:07 tpcc100.history-schema.sql
-rw-rw-r-- 1 mysql mysql 19M Feb 1 09:07 tpcc100.history.sql
-rw-rw-r-- 1 mysql mysql 431 Feb 1 09:07 tpcc100.item-schema.sql
-rw-rw-r-- 1 mysql mysql 7.9M Feb 1 09:07 tpcc100.item.sql
-rw-rw-r-- 1 mysql mysql 304 Feb 1 09:07 tpcc100.new_order-schema.sql
-rw-rw-r-- 1 mysql mysql 1.1M Feb 1 09:07 tpcc100.new_order.sql
-rw-rw-r-- 1 mysql mysql 615 Feb 1 09:07 tpcc100.order_line-schema.sql
-rw-rw-r-- 1 mysql mysql 204M Feb 1 09:08 tpcc100.order_line.sql
-rw-rw-r-- 1 mysql mysql 525 Feb 1 09:07 tpcc100.orders-schema.sql
-rw-rw-r-- 1 mysql mysql 14M Feb 1 09:07 tpcc100.orders.sql
-rw-rw-r-- 1 mysql mysql 66 Feb 1 09:07 tpcc100-schema-create.sql
-rw-rw-r-- 1 mysql mysql 1.2K Feb 1 09:07 tpcc100.stock-schema.sql
-rw-rw-r-- 1 mysql mysql 316M Feb 1 09:08 tpcc100.stock.sql
-rw-rw-r-- 1 mysql mysql 730 Feb 1 09:07 tpcc100.warehouse-schema.sql
-rw-rw-r-- 1 mysql mysql 1.2K Feb 1 09:07 tpcc100.warehouse.sql
备份指定的表
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -B tpcc100 -T customer -o /home/mysql/backup
[mysql@wallet01 ~]$ cd backup
[mysql@wallet01 backup]$ ls -lh
total 172M
-rw-rw-r-- 1 mysql mysql 141 Feb 1 09:20 metadata
-rw-rw-r-- 1 mysql mysql 1.4K Feb 1 09:20 tpcc100.customer-schema.sql
-rw-rw-r-- 1 mysql mysql 172M Feb 1 09:20 tpcc100.customer.sql
-rw-rw-r-- 1 mysql mysql 66 Feb 1 09:20 tpcc100-schema-create.sql
备份多表
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -B tpcc100 -T customer,orders -o /home/mysql/backup
[mysql@wallet01 ~]$ cd backup
[mysql@wallet01 backup]$ ls -lh
total 186M
-rw-rw-r-- 1 mysql mysql 141 Feb 1 09:24 metadata
-rw-rw-r-- 1 mysql mysql 1.4K Feb 1 09:24 tpcc100.customer-schema.sql
-rw-rw-r-- 1 mysql mysql 172M Feb 1 09:24 tpcc100.customer.sql
-rw-rw-r-- 1 mysql mysql 525 Feb 1 09:24 tpcc100.orders-schema.sql
-rw-rw-r-- 1 mysql mysql 14M Feb 1 09:24 tpcc100.orders.sql
-rw-rw-r-- 1 mysql mysql 66 Feb 1 09:24 tpcc100-schema-create.sql
仅备份表结构
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -B tpcc100 -T customer -d -o /home/mysql/backup
[mysql@wallet01 ~]$ cd backup
[mysql@wallet01 backup]$ ls -lh
total 12K
-rw-rw-r-- 1 mysql mysql 141 Feb 1 09:25 metadata
-rw-rw-r-- 1 mysql mysql 1.4K Feb 1 09:25 tpcc100.customer-schema.sql
-rw-rw-r-- 1 mysql mysql 66 Feb 1 09:25 tpcc100-schema-create.sql
仅备份表数据
[mysql@wallet01 ~]$ mydumper -u root -p abcd.1234 -B tpcc100 -T customer -m -o /home/mysql/backup
[mysql@wallet01 ~]$ cd backup
[mysql@wallet01 backup]$ ls -lh
total 172M
-rw-rw-r-- 1 mysql mysql 141 Feb 1 09:27 metadata
-rw-rw-r-- 1 mysql mysql 172M Feb 1 09:27 tpcc100.customer.sql
[root@wallet01 ~]# myloader --help
-d, --directory 备份目录
-q, --queries-per-transaction Number of queries per transaction, default 1000
-o, --overwrite-tables 如果表已经存在则删除
-B, --database 还原到另一个数据库，例如备份A库，还原为B库
-s, --source-db 需要还原的数据库（备份文件中）
-e, --enable-binlog Enable binary logging of the restore data
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads 还原使用的线程数量，默认是 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
还原全部库
[mysql@wallet01 ~]$ myloader -u root -p abcd@1234 -o -d /home/mysql/backup
还原指定的库
[mysql@wallet01 ~]$ myloader -u root -p abcd@1234 -o -s tpcc100 -d /home/mysql/backup
备份的tpcc100库还原为tpcc200库
[mysql@wallet01 ~]$ myloader -u root -p abcd@1234 -o -B tpcc200 -s tpcc100 -d /home/mysql/backup
还原指定的表
[mysql@wallet01 ~]$ mysql -uroot -pabcd@1234 tpcc100 < /home/mysql/backup/tpcc100.customer-schema.sql
[mysql@wallet01 ~]$ mysql -uroot -pabcd@1234 tpcc100 < /home/mysql/backup/tpcc100.customer.sql
分享名称：【MySQL】数据库逻辑备份工具--mydumper
文章网址：
http://bjjierui.cn/article/psjsce.html
