【MySQL】数据库逻辑备份工具--mydumper

[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.gitInitialized empty Git repository in /root/mydumper/.git/error:  while accessing https://github.com/maxbube/mydumper.git/info/refsfatal: HTTP request failed[root@wallet01 ~]# yum update -y nss curl libcurl[root@wallet01 ~]# git clone https://github.com/maxbube/mydumper.gitInitialized empty Git repository in /root/mydumper/.git/remote: Enumerating objects: 1185, done.remote: Total 1185 (delta 0), reused 0 (delta 0), pack-reused 1185Receiving 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 -lhtotal 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 -lhtotal 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 -lhtotal 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 -lhtotal 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 -lhtotal 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