mydumper 的使用

mydumper myloade 优点：支持多线程、并行，支持position位置，支持表的恢复很强的工具

1、mydumper 参数

mydumper --help 查看选项参数：

Application 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. 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              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
-d, --no-data               Do not dump table data
-G, --triggers              Dump triggers
-E, --events                Dump events
-R, --routines              Dump stored procedures and functions
-k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
--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
--tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
--lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
-U, --updated-since         Use Update_time to dump only tables updated in the last U days
--trx-consistency-only      Transactional consistency only
-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

导出

全库导出

全库
mydumper -G -E -R --trx-consistency-only -t 8 -o /u01/mysql/mydump/full-`date +'%Y%m%d'`/

指定库导出
mydumper -G -E -R --trx-consistency-only --rows 10000 -t 4 -c \
 -B test -o /u01/mysql/mydump/full-`date +'%Y-%m-%d'`/

常用参数
-G, --triggers            
-E, --events             
-R, --routines              
-B --database 
-c --compress
-o --outputdir
-r --rows  按行数
-F --chunk-filesize 按大小

导出后文件命名规则大致如下：
dbname-schema-create.sql：建库语句。
dbname-schema-post.sql：包含事件、存储过程及函数创建语句（若存在则有该文件）。
dbname.tbname.metadata：记录这个表的行数。
dbname.tbname-schema.sql：此表的创建语句。
dbname.tbname-schema-triggers.sql：创建触发器语句（若该表存在触发器 则有此文件）。
dbname.tbname.sql：该表的插入数据语句（若该表为空 则不存在此文件）。
dbname.viewname-schema.sql：创建视图语句（只列举出视图字段）。
dbname.viewname-schema-view.sql：创建视图的真正语句。
metadata：记录开始及结束备份的时间以及二进制日志位置。

可以看出单表也可以并行导出，支持表的恢复，metadata 就是mata_data=1

2、myloader使用

使用myloader --help查看选项参数：

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -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

恢复

myloader -d /u01/mysql/mydump/fulldump-`date +'%Y-%m-%d'`/ -t 4 -B test1

如何排除（或包含）数据库

可以使用--regex功能，例如不转储mysql和测试数据库：
 mydumper --regex '^(?!(mysql\.|test\.))'
要仅转储 mysql 和测试数据库：

 mydumper --regex '^(mysql\.|test\.)'
要从 test 开始不转储所有数据库，请执行以下操作：

 mydumper --regex '^(?!(test))'
要转储，请在不同的数据库中指定表（注意：表的名称应以 $ 结尾。相关问题）：

 mydumper --regex '^(db1\.table1$|db2\.table2$)'
如果要转储几个数据库但丢弃一些表，可以执行以下操作：

 mydumper --regex '^(?=(?:(db1\.|db2\.)))(?!(?:(db1\.table1$|db2\.table2$)))'
这将转储 db1 和 db2 中的所有表，但它将排除 db1.table1 和 db2.table2

当然，正则表达式功能可用于描述几乎任何表列表。

配置参数文件

Mydumper 和 Myloader 部分：

[mydumper]
host = 127.0.0.1
user = root
password = p455w0rd
database = db
rows = 10000

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

参考：https://github.com/mydumper/mydumper