一、mydumper&myloader
mydumper&myloader是用于对MySQL数据库进行多线程备份和恢复的开源 (GNU GPLv3）工具。开发人员主要来自MySQL、Facebook和SkySQL公司，目前由Percona公司开发和维护，是Percona Remote DBA项目的重要组成部分，包含在Percona XtraDB Cluster中。mydumper的第一版0.1发布于2010.3.26，最新版本0.9.5发布于2018.5月，每个版本都有详细的change log，使用时尽可能详细看看是否满足自己的需求。比如，0.9.5版本支持了JSON及生成列。
mydumper特点
1. C语言写的，轻量级。并且开源 (GNU GPLv3)，目前持续维护中。
2. 多线程备份，执行速度比mysqldump快N倍，并支持行级chunk备份。
3. 事务性和非事务性表一致的快照（适用于0.2.2以上版本），维护所有线程的快照，提供准确的主从日志位置等。
4. 快速的文件压缩。
5. 支持导出binlog。
6. 多线程恢复（适用于0.2.1以上版本）。
7. 以守护进程的工作方式，定时快照和连续二进制日志（适用于0.5.0以上版本）。
8. 支持从库备份时记录主库的position，且不需要关闭slave sql线程（mysqldump需要关闭sql线程）；并且支持同时记录主从position（mysqldump不支持）。
9. 更容易管理输出（每表单独文件，导出元数据等，易于查看/解析数据）
导入导出测试
下图是在SSD和HDD存储介质上将mydumper和同为逻辑备份的MySQL官方mysqldump所做的性能对比测试：
可以发现，在备份时间上，mydumper性能比mysqldump好将近1倍，通过分析不难发现，两者性能的差距主要跟备份实例的IO性能以及mydumper开启的线程数有关系，如果外部实例的IO性能很差，那么可能mysqldump单线程就能够将IO吃满，那改为使用mydumper也无法带来性能提升，但如果实例的IO性能较好，通过增加备份线程数（测试所用为2线程），就可能带来成倍的性能提升。导入时间分别使用与mydumper配合使用的myloader多线程sql导入工具以及mysqldump对应的source命令得到。相应的，在IO性能较好的SSD上，myloader的多线程优势体现明显，比source的单线程执行时间上节省将近2倍（测试所用为4线程）。不过在HDD上，由于其本身性能原因，myloader带来的效益变小。
二、多线程导出原理
mydumper原理与mysqldump原理类似，一致快照如何工作呢？
- 作为预防措施，在服务器上运行缓慢的查询要么中止dump，要么被Kill
- 读取各种元数据（“SHOW SLAVE STATUS”，“SHOW MASTER STATUS”）
- 其他线程连接并建立快照（“START TRANSACTION WITH CONSISTENT SNAPSHOT”）
- 一旦所有工作线程都宣布快照建立完成后，master将执行“UNLOCK TABLES”并开始排队作业。
当然，与mysqldump最大的区别是引入了多线程备份，每个备份线程备份一部分表，当然并发粒度可以到行级，达到多线程备份的目的。这里要解决最大一个问题是，如何保证备份的一致性，其实关键还是在于FTWRL。对于非Innodb表，在释放锁之前，需要将表备份完成。对于Innodb表，需要确保多个线程都能拿到一致性位点，这个动作同样要在持有全局锁期间完成，因为此时数据库没有读写，可以保证位点一致。
另外，在MySQL 5.7版本中，官方发布了一种新的备份工具mysqlpump，也是多线程的，其实现方式给人耳目一新的感觉，但遗憾的是其仍为表级别的并行，且不支持position点的记录。而mydumper能够实现记录级别的并行备份，其备份框架由主线程和多个工作线程组成，备份流程可见下图：
主线程负责建立数据一致性备份点、初始化工作线程和为工作线程推送备份任务：
- 对备份实例加全局读锁，阻塞写操作以建立一致性数据备份快照点，记录备份点BinLog信息；
- 创建工作线程，初始化备份任务队列，并向队列中推送数据库元数据（schema）、非InnoDB表和InnoDB表的备份任务；
工作线程负责将备份任务队列中的任务按顺序取出并完成备份：
- 分别建立与备份实例连接，将session的事务级别设置为repeatable-read，用于实现可重复读；
- 在主线程仍持有全局读锁时开启事务进行快照读，这样保证了读到的一致性数据与主线程相同，实现了备份数据的一致性；
- 按序从备份任务队列中取出备份任务，工作线程先进行MyISAM等非InnoDB表备份，再完成InnoDB表备份；这样可以在完成非InnoDB表备份通知后主线程释放读锁，尽可能减小对备份实例业务的影响；
mydumper的记录级备份由主线程负责任务拆分，由多个工作线程完成。主线程通过将表数据拆分为多个chunk，每个chunk作为一个备份任务。表数据拆分方式如下所述：mydumper优先选择主键索引的第一列作为chunk划分字段，若不存在主键索引，则选择第一个唯一索引作为划分依据，若还不存在，则选择区分度（Cardinality）最高的任意索引。前提是字段类型是整型，且不能是复合索引。如果都无法满足，则只能进行表级的并行备份。在确定了chunk划分字段后，先获取该字段的最大和最小值，再通过执行“explain select field from db.table”来估计该表的记录数，最后根据所设的每个任务（文件）记录数来将该表划分为多个chunk。如下图所示：
以上描述可知，mydumper并不能保证记录级备份时，每个备份任务中的记录数是相同的。另外，目前记录级备份存在一个bug：所用索引字段为负数时主线程会进入死循环无法退出，导致备份失败。https://bugs.launchpad.net/mydumper/+bug/1418355
与mysqldump另一个不同是， mydumper为每个备份任务建立至少一个备份文件。在0.9.1版本中，文件类型包括schema-create、schema、schema-post文件等表元数据文件分别用保存建数据库语句、建表语句（包括触发器）、函数/存储过程/事件定义语句等；数据文件可以根据用户设置为固定大小或固定记录数的文件。这样便以进行更细粒度的数据恢复和数据删除，比如可以在myloader的时候仅选择某几个数据库/表进行恢复。
三、网易对mydumper的改进
mydumper是一款优秀的备份工具，但也存在不足，包括多线程导出数据对实例业务的影响、逻辑备份方式对热点数据污染和持锁时对业务的阻塞等。网易RDS在mydumper实践中对其进行了多方面的优化。
多线程备份固然好，但在进行备份时往往数据库还在正常提供对外服务，多线程全表select数据会占用很大部分的系统IO能力，导致正常的业务IO性能下降，严重时甚至会使数据库连接爆掉。通过为mydumper增加负载自适应能力来最大限度缓解对线上业务影响：工作线程在每次数据导出前，都会首先观察实例的当前负载情况，举MySQL状态Thread_connected为例，其反映的是目前已连接到该实例的请求数，如果该数值大于设定的阈值，则本次导出操作会暂停，直到数值小于阈值才会恢复，这样就起到了根据实例业务负载情况，灵活调整用于数据导出的线程数来适应线上业务负载的作用。
逻辑备份的全表select不可避免会污染InnoDB Buffer Pool的热点数据，缓存的热点数据被换出，降低了命中率的同时增大了业务的IO量，在使用mydumper时应尽量减小对Buffer Pool的影响；通过调整Buffer Pool的热点算法，使得热点数据尽可能不被换出。修改innodb_old_blocks_time和innodb_old_blocks_pct，用于将全表select进入Buffer Pool放在其old sublist中，同时减小old sublist块在Buffer Pool中的比例，起到最小化污染的作用。其原理详见http://dev.mysql.com/doc/innodb-plugin/1.0/en/idm47548325357504.html
在进行数据备份时，由于MyISAM表是非事务的，为了得到一致性的数据，导出MyISAM表需要全程持有读锁。在通常的MySQL实例中，MyISAM表数据都是很少的，所以持锁时间很短，但若有实例存在大量的MyISAM表数据，那么就会因持锁时间过长对业务的数据更新和插入造成影响。通过为mydumper增加持锁超时时间来避免该问题，所在数据备份过程中，持锁时间超过所设置时间，则mydumper返回失败，通过将MyISAM表转化为InnoDB表后再开始导出。
此外，在对大数据量数据库进行备份时，往往需要耗费较长时间，如果能够实时了解备份进度，相信是一个很好的体验，为此，给mydumper增加了进度查询功能，能够查询mydumper所需执行的所有备份任务数、当前已经完成的备份任务数及每个备份任务所花费时间。
四、安装mydumper&myloader
从Github上面下载即可，有源码包及RPM包。
如果编译安装，需要先安装依赖，然后进行编译。不想嫌麻烦可以直接使用打好的包即可。
|
1 2 |
$ yum groupinstall "Development tools" "Compatibility libraries" $ yum install cmake make glib2 glib2-devel pcre pcre-devel zlib zlib-devel |
解压安装
|
1 2 3 4 |
$ tar xvf mydumper-0.9.5.tar.gz $ cd mydumper-0.9.5 $ cmake . $ make |
安装完成后会生成2个工具：mydumper（备份），myloader（导入），你可以直接复制放入到bin目录下。
五、mydumper&myloader语法
mydumper（备份工具）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
$ mydumper --help Usage: mydumper [OPTION...] multi-threaded MySQL dumping Help Options: -?, --help Show help options Application Options: -B, --database #需要备份的数据库，一个数据库一条命令备份，要不就是备份所有数据库，包括mysql -T, --tables-list #需要备份的表，用逗号分隔 -o, --outputdir #备份文件目录 -s, --statement-size #生成插入语句的字节数，默认1000000，这个参数不能太小，不然会报Row bigger than statement_size for tools.t_serverinfo -r, --rows #尝试将表拆分为多行的chunk，该参数关闭--chunk-filesize -F, --chunk-filesize #将表拆分为此输出文件大小的chunk，单位为mb -c, --compress #压缩输出文件 -e, --build-empty-files #即使表没有数据，也产生一个空文件 -x, --regex #正则表达式匹配，如'db.table' -i, --ignore-engines #忽略的存储引擎，用逗号分隔 -m, --no-schemas #不导出表结构 -d, --no-data #不导出表数据 -G, --triggers #导出触发器 -E, --events #导出事件 -R, --routines #导出存储过程 -k, --no-locks #不执行共享读锁，警告：这将导致不一致的备份 --less-locking #减到最小的锁在innodb表上 -l, --long-query-guard #设置长查询时间，默认60秒，超过该时间则会报错：There are queries in PROCESSLIST running longer than 60s, aborting dump -K, --kill-long-queries #kill掉长时间执行的查询，备份报错：Lock wait timeout exceeded; try restarting transaction -D, --daemon #启用守护进程模式 -I, --snapshot-interval #dump快照间隔时间，单位分钟，默认60分钟，需要在daemon模式下有效 -L, --logfile #使用日志文件，默认标准输出到终端 --tz-utc #备份的时候允许备份Timestamp，这样会导致不同时区的备份还原会出问题，默认关闭，参数：--skip-tz-utc to disable --skip-tz-utc --use-savepoints #使用保存点记录元数据的锁信息，需要SUPER权限 --success-on-1146 #Not increment error count and Warning instead of Critical in case of table doesn't exist --lock-all-tables #使用LOCK TABLE锁所有备份表，代替FLUSH TABLE WITH READ LOCK -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 #备份执行的线程数，默认4个线程 -C, --compress-protocol #在mysql连接上使用压缩协议 -V, --version #Show the program version and exit -v, --verbose #更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 --defaults-file #Use a specific defaults file --ssl #Connect using SSL --key #The path name to the key file --cert #The path name to the certificate file --ca #The path name to the certificate authority file --capath #The path name to a directory that contains trusted SSL CA certificates in PEM format --cipher #A list of permissible ciphers to use for SSL encryption |
myloader（恢复工具）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ myloader --help Usage: myloader [OPTION...] multi-threaded MySQL loader Help Options: -?, --help Show help options Application Options: -d, --directory #备份文件所在的目录 -q, --queries-per-transaction #每个事务的query数量, 默认1000 -o, --overwrite-tables #如果表存在则先删除，使用该参数，需要备份时候要备份表结构，不然还原会找不到表 -B, --database #指定需要还原到的数据库 -s, --source-db #需要还原的数据库 -e, --enable-binlog #启用二进制日志恢复数据 -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 #使用的线程数量，默认4 -C, --compress-protocol #连接上使用压缩协议 -V, --version #Show the program version and exit -v, --verbose #更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 |
六、测试案例
案例一：备份单个事务表
|
1 |
$ mydumper -u root -h localhost -P 3306 -t 3 -l 300 -B sbtest -T sbtest1 -o /backup/ -v 3 |
备份sbtest数据库下的一个事务表到/backup目录中，是否多线程（开启3个），备份日志如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
** Message: Connected to a MySQL server ** Message: Started dump at: 2019-01-16 03:26:44 ** Message: Written master status ** Message: Multisource slave detected. ** Message: Thread 1 connected using MySQL connection ID 9019 ** Message: Thread 2 connected using MySQL connection ID 9020 ** Message: Thread 3 connected using MySQL connection ID 9021 ** Message: Non-InnoDB dump complete, unlocking tables ** Message: Thread 1 dumping data for `sbtest`.`sbtest1` ** Message: Thread 2 dumping schema for `sbtest`.`sbtest1` ** Message: Thread 3 shutting down ** Message: Thread 2 shutting down ** Message: Thread 1 shutting down ** Message: Finished dump at: 2019-01-16 03:27:11 |
默认情况下，由于没有检测到非事务表，所以在一致性快照开启后就释放了全局锁（在下个案例可以看出这个效果）。然后进行事务表的备份，这样整个数据就是一致的了。 另外，可以看到进行了多线程备份，由于没有任务 Thread-3 启动后就被关闭了。
查看整个备份产生的执行语句（开启general log）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
190115 03:26:44 8368 Connect root@localhost as anonymous on sbtest 8368 Query SET SESSION wait_timeout = 2147483 8368 Query SET SESSION net_write_timeout = 2147483 8368 Query SHOW PROCESSLIST 8368 Query SELECT @@have_backup_locks 8368 Query FLUSH TABLES WITH READ LOCK 8368 Query SELECT @@tokudb_version 8368 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8368 Query /*!40101 SET NAMES binary*/ 8368 Query SHOW MASTER STATUS 8368 Query SELECT @@gtid_binlog_pos 8368 Query SELECT @@default_master_connection 8368 Query SHOW SLAVE STATUS 8369 Connect root@localhost as anonymous on 8369 Query SET SESSION wait_timeout = 2147483 8369 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8369 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8369 Query /*!40103 SET TIME_ZONE='+00:00' */ 8369 Query /*!40101 SET NAMES binary*/ 8370 Connect root@localhost as anonymous on 8370 Query SET SESSION wait_timeout = 2147483 8370 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8370 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8370 Query /*!40103 SET TIME_ZONE='+00:00' */ 8370 Query /*!40101 SET NAMES binary*/ 8371 Connect root@localhost as anonymous on 8371 Query SET SESSION wait_timeout = 2147483 8371 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8371 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8371 Query /*!40103 SET TIME_ZONE='+00:00' */ 8371 Query /*!40101 SET NAMES binary*/ 8368 Init DB sbtest 8368 Query SHOW TABLE STATUS 8368 Query SHOW CREATE DATABASE `sbtest` 8368 Query UNLOCK TABLES /* FTWRL */ 8369 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='sbtest' and TABLE_NAME='sbtest1' and extra like '%GENERATED%' 8368 Quit 8371 Query SHOW CREATE TABLE `sbtest`.`sbtest1` 8371 Quit 8369 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` 8370 Quit 190115 03:26:44 8369 Quit |
如果你使用mysqldump的话可以看到整个过程都只有一个线程，而使用mydumper备份单个库或多个库时就会发现使用多线程，SELECT阶段根据我们设置的线程数同时工作了。这里由于我们备份单表，所以只有一个线程。
分析：mydumper把数据和表结构分开备份，并且把二进制日志备份出来单独放到一个文件中。
|
1 2 3 4 5 6 |
ll /backup/ total 1063384 -rw-r--r-- 1 root root 148 Jan 15 22:31 metadata -rw-r--r-- 1 root root 512 Jan 15 22:31 sbtest.sbtest1-schema.sql -rw-r--r-- 1 root root 1088891865 Jan 15 22:31 sbtest.sbtest1.sql -rw-r--r-- 1 root root 65 Jan 15 22:31 sbtest-schema-create.sql |
各个文件说明：
metadata：元数据记录备份开始和结束时间，以及master binlog position点（默认记录），如果在slave上备份默认会记录master binlog position及slave binlog position点，并且不会关闭SQL线程，这个很友好，比mysqldump及mysqlpump都强。
table.sql：每个表一个文件。
table-schema.sql：表结构文件。
db-schema.sql：库结构文件。
如何进行表级别多线程备份呢？当表中有主键或唯一索引时，加上 -r 或者 -F 可以执行 chunk 大小，此时就可以对表级别进行多线程备份了。关于行的多线程备份原理和要求在上面的“多线程导出原理”部分已经介绍了。
比如，这里指定以行为单位，1000000为一个chunk进行多线程备份。
|
1 |
$ mydumper -u root -h localhost -P 3306 -t 3 -l 300 -r 1000000 -B sbtest -T sbtest1 -o /backup/ -v 3 |
基于行拆分的语句如下：
|
1 2 3 4 5 6 |
SELECT /*!40001 SQL_NO_CACHE */ MIN(`id`),MAX(`id`) FROM `sbtest`.`sbtest1` EXPLAIN SELECT `id` FROM `sbtest`.`sbtest1` SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE `id` IS NULL OR (`id` >= 1 AND `id` < 1250001) SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE (`id` >= 2500001 AND `id` < 3750001) SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE (`id` >= 1250001 AND `id` < 2500001) SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` WHERE (`id` >= 3750001 AND `id` < 5000001) |
这个可以更好地提升数据库备份速度，不管数据库里是否有大表，而MySQL官方的mysqlpump虽说也是多线程但是只能针对多表多线程，遇到大表时也是无能为力。
案例二：备份事务表和非事务表
|
1 |
$ mydumper -u root -h localhost -P 3306 -t 3 -l 300 -B sbtest -T sbtest1,sbtest1_myisam -o /backup/ -v 3 |
备份sbtest数据库下的一个事务表和一个非事务表备份到/backup目录中，备份日志如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
** Message: Connected to a MySQL server ** Message: Started dump at: 2019-01-16 03:20:09 ** Message: Written master status ** Message: Multisource slave detected. ** Message: Thread 1 connected using MySQL connection ID 9008 ** Message: Thread 2 connected using MySQL connection ID 9009 ** Message: Thread 3 connected using MySQL connection ID 9010 ** Message: Thread 1 dumping data for `sbtest`.`sbtest1_myisam` ** Message: Thread 2 dumping data for `sbtest`.`sbtest1` ** Message: Thread 3 dumping schema for `sbtest`.`sbtest1` ** Message: Thread 3 dumping schema for `sbtest`.`sbtest1_myisam` ** Message: Non-InnoDB dump complete, unlocking tables ** Message: Thread 3 shutting down ** Message: Thread 1 shutting down ** Message: Thread 2 shutting down # 事务表有多大，此线程就会备份多久 ** Message: Finished dump at: 2019-01-16 03:20:36 |
可以看到，当事务表和非事务表混合情况下，在默认参数下，释放锁是非事务表备份完成后，也就是说锁持有时间取决于非事务表大小。对比上面的单事务表备份，同样在默认参数下，可以看出mydumper在默认情况下就是保证了数据的绝对一致性。
查看整个备份产生的执行语句（开启general log）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
190115 03:20:09 8406 Connect root@localhost as anonymous on sbtest 8406 Query SET SESSION wait_timeout = 2147483 8406 Query SET SESSION net_write_timeout = 2147483 8406 Query SHOW PROCESSLIST 8406 Query SELECT @@have_backup_locks 8406 Query FLUSH TABLES WITH READ LOCK 8406 Query SELECT @@tokudb_version 8406 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8406 Query /*!40101 SET NAMES binary*/ 8406 Query SHOW MASTER STATUS 8406 Query SELECT @@gtid_binlog_pos 8406 Query SELECT @@default_master_connection 8406 Query SHOW SLAVE STATUS 8407 Connect root@localhost as anonymous on 8407 Query SET SESSION wait_timeout = 2147483 8407 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8407 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8407 Query /*!40103 SET TIME_ZONE='+00:00' */ 8407 Query /*!40101 SET NAMES binary*/ 8408 Connect root@localhost as anonymous on 8408 Query SET SESSION wait_timeout = 2147483 8408 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8408 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8408 Query /*!40103 SET TIME_ZONE='+00:00' */ 8408 Query /*!40101 SET NAMES binary*/ 8409 Connect root@localhost as anonymous on 8409 Query SET SESSION wait_timeout = 2147483 8409 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8409 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8409 Query /*!40103 SET TIME_ZONE='+00:00' */ 8409 Query /*!40101 SET NAMES binary*/ 8406 Init DB sbtest 8406 Query SHOW TABLE STATUS 8406 Query SHOW CREATE DATABASE `sbtest` 8407 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='sbtest' and TABLE_NAME='sbtest1_myisam' and extra like '%GENERATED%' 8408 Query SHOW CREATE TABLE `sbtest`.`sbtest1` 8409 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='sbtest' and TABLE_NAME='sbtest1' and extra like '%GENERATED%' 8408 Query SHOW CREATE TABLE `sbtest`.`sbtest1_myisam` 8407 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1_myisam` 8409 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` 8406 Query UNLOCK TABLES /* FTWRL */ 8406 Quit 8407 Quit 8408 Quit 190115 03:20:36 8409 Quit |
案例三：--trx-consistency-only
你可以将此视为mysqldump的--single-transaction参数，但仍然具有binlog位置。显然，这个位置仅适用于事务表（包括TokuDB）。使用此选项的一个优点是全局读锁仅用于线程协调，因此一旦启动事务就会释放它。
|
1 |
$ mydumper -u root -h localhost -P 3306 -t 3 -l 300 --trx-consistency-only -B sbtest -T sbtest1,sbtest1_myisam -o /backup/ -v 3 |
备份sbtest数据库下的一个事务表和一个非事务表备份到/backup目录中，查看备份日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
** (mydumper:22013): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables. ** Message: Connected to a MySQL server ** Message: Started dump at: 2019-01-16 03:36:57 ** Message: Written master status ** Message: Multisource slave detected. ** Message: Thread 1 connected using MySQL connection ID 9037 ** Message: Thread 2 connected using MySQL connection ID 9038 ** Message: Thread 3 connected using MySQL connection ID 9039 ** Message: Transactions started, unlocking tables ** Message: Thread 1 dumping data for `sbtest`.`sbtest1` ** Message: Thread 3 shutting down ** Message: Thread 2 shutting down ** Message: Thread 1 shutting down ** Message: Finished dump at: 2019-01-16 03:37:26 |
从日志可以看出，在所有线程都获取到一致性快照后，就释放了锁。然后备份非事务表和事务表，这不就跟--single-transaction参数效果一样。但同时也会有一个问题，就是对于非事务表数据可能是不一致的，使用时需要确保没有非事务表。
查看整个备份产生的执行语句（开启general log）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
190115 03:36:57 8471 Connect root@localhost as anonymous on sbtest 8471 Query SET SESSION wait_timeout = 2147483 8471 Query SET SESSION net_write_timeout = 2147483 8471 Query SHOW PROCESSLIST 8471 Query SELECT @@have_backup_locks 8471 Query FLUSH TABLES WITH READ LOCK 8471 Query SELECT @@tokudb_version 8471 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8471 Query /*!40101 SET NAMES binary*/ 8471 Query SHOW MASTER STATUS 8471 Query SELECT @@gtid_binlog_pos 8471 Query SELECT @@default_master_connection 8471 Query SHOW ALL SLAVES STATUS 8472 Connect root@localhost as anonymous on 8472 Query SET SESSION wait_timeout = 2147483 8472 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8472 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8472 Query /*!40103 SET TIME_ZONE='+00:00' */ 8472 Query /*!40101 SET NAMES binary*/ 8473 Connect root@localhost as anonymous on 8473 Query SET SESSION wait_timeout = 2147483 8473 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8473 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8473 Query /*!40103 SET TIME_ZONE='+00:00' */ 8473 Query /*!40101 SET NAMES binary*/ 8474 Connect root@localhost as anonymous on 8474 Query SET SESSION wait_timeout = 2147483 8474 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8474 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8474 Query /*!40103 SET TIME_ZONE='+00:00' */ 8474 Query /*!40101 SET NAMES binary*/ 8471 Query UNLOCK TABLES /* trx-only */ 8471 Init DB sbtest 8471 Query SHOW TABLE STATUS 8471 Query SHOW CREATE DATABASE `sbtest` 8471 Quit 8474 Query SHOW CREATE TABLE `sbtest`.`sbtest1` 8474 Query SHOW CREATE TABLE `sbtest`.`sbtest1_myisam` 8474 Quit 8473 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='sbtest' and TABLE_NAME='sbtest1_myisam' and extra like '%GENERATED%' 8473 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1_myisam` 8472 Query select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='sbtest' and TABLE_NAME='sbtest1' and extra like '%GENERATED%' 8472 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest1` 8473 Quit 190115 03:37:26 8472 Quit |
可以看到加了--trx-consistency-only参数后，锁的释放确实在所有线程事务开启后就进行了。
进一步测试：测试一些常用的参数
1）备份指定表（-T），并且不要导出表结构（-m）
|
1 |
$ mydumper -u root -p -h localhost -P 3306 -m -B test -T b,a,c,d,e,g,f,h,i -o /backup/test |
2）压缩备份文件（-c），备份binlog（-b），正则表达式备份表（-x）
|
1 |
$ mydumper -u root -p -h localhost -P 3306 -m -c -b --regex=sbtest.* -B sbtest -o /backup/test |
如上所示，备份文件已经是压缩的了（用gzip -d 解压），并且备份出了以sbtest开头匹配出来的所有表，二进制日志也被备份到了binlog_snapshot文件中，并且也是被压缩的。这里说明下备份指定数据库的方法：--regex正则匹配
|
1 2 3 4 5 |
# 指定备份数据库：备份abc、bcd、cde; $ mydumper -u root -p 123456 -h localhost -P 3306 -t 3 -c -l 300 -s 10000000 -e --regex 'abc|bcd|cde' -o bbb/ # 指定不备份的数据库：不备份abc、mysql、test，备份其他数据库; $ mydumper -u root -p 123456 -h localhost -P 3306 -t 3 -c -l 300 -s 10000000 -e --regex '^(?!(abc|mysql|test))' -o bbb/ |
3）恢复数据，表存在先删除（-o）：这里需要注意，使用该参数，备份目录里面需要有表结构的备份文件。
|
1 |
$ myloader -u root -p -h localhost -P 3306 -o -B sbtest -d /backup |
最后再来说一下，mydumper这个神器，支持在从库备份并且同时记录master binlog position及slave binlog position，并且不需要关闭SQL线程，很强。有了这个功能就可以大胆在从库备份，从而达到随意做从库，无论是依赖master还是slave都可以做从库。相关的信息都存在metadata文件中，一般信息如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Started dump at: 2019-01-16 03:34:38 SHOW MASTER STATUS: Log: mysql-bin.000017 Pos: 228116182 GTID: SHOW SLAVE STATUS: Host: 10.10.0.111 Log: mysql-bin.000018 Pos: 221983380 GTID: Finished dump at: 2019-01-16 03:34:47 |
关于mydumper的基本使用到这里就结束了。
七、守护模式
Mydumper具有守护进程模式，该模式将经常对 dump data 进行快照，同时不断地检索二进制日志文件（不太明白这句话的意思）。这样可以提供持续一致的备份，直到数据库服务器出现故障。
在以下示例中，mydumper将使用守护程序模式，每半小时创建一个快照并记录到输出文件：
|
1 |
$ mydumper --daemon -o /backup --snapshot-interval=30 --logfile=dump.log |
产生的文件如下：
|
1 2 3 4 5 |
$ ll /backup/ total 8 drwx------ 2 root root 4096 Jan 16 01:14 0 drwx------ 2 root root 4096 Jan 16 01:13 1 lrwxrwxrwx 1 root root 1 Jan 16 01:14 last_dump -> 0 |
这两个目录会来回进行覆盖轮回，而last_dump就是指向最近一次目录，而目录里面的数据与我们直接备份数据相同。需要注意的是每次都是全量的，并没有增量的概念。
<参考>
https://github.com/maxbube/mydumper
https://www.percona.com/blog/2015/11/12/logical-mysql-backup-tool-mydumper-0-9-1-now-available/
