MySQL Backup mydumper

生产环境中有一实例每天使用mysqldump备份时长达到了2个小时53分钟，接近3个小时，还不算上备份文件归档的时间，这个时间对于逻辑备份来说有点久。为了提高逻辑备份效率，打算替换为使用mydumper。

对比mysqldump，mydumper具有如下特点：

多线程备份
备份执行速度更快
支持备份文件压缩
支持行级别切块备份

更多关于mydumper的说明，可以查看官方GitHub：https://github.com/maxbube/mydumper

安装

之前在测试mydumper时有使用过早期版本，是通过编译进行安装的，而mydumper是C语言写的，编译过程中出现了一系列的依赖问题。为了避免出现安装依赖问题，官方从0.9.3版本开始提供了编译后的安装包，建议采用RPM包的方式进行安装。同样通过官方GitHub获取安装包。

本文使用的RPM安装包为：

mydumper-0.9.5-2.el7.x86_64.rpm

安装完成后验证：

# rpm -qa |grep mydumper
mydumper-0.9.5-2.x86_64

# rpm -ql mydumper-0.9.5-2.x86_64
/usr/bin/mydumper
/usr/bin/myloader

mydumper：用来备份数据。
myloader：用来还原数据。

本文主要讨论mydumper，查看版本信息：

# mydumper -V
mydumper 0.9.5, built against MySQL 5.7.21-21

主要选项

# mydumper --help
Usage:
  mydumper [OPTION?] multi-threaded MySQL dumping

Help Options:
  -?, --help                  Show help options

Application Options:
  -B, --database              Database to dump
  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
  -O, --omit-from-file        File containing a list of database.table entries to skip, one per line (skips before applying 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
  -N, --insert-ignore         Dump rows with INSERT 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
  -W, --no-views              Do not dump 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
  --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
  --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               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
  --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

-B, --database
指定dump数据库
-T, --tables-list
指定dump表，多个表用逗号分隔(不排除正则匹配)
-O, --omit-from-file
指定dump需要跳过包含一行或多行database.table格式的文件，跳过dump的优先级大于dump正则匹配
-o, --outputdir
指定dump文件保存目录
-s, --statement-size
指定dump生成insert语句的大小，单位字节，默认是1000000
-r, --rows
把表多少行分割成chunks，这个选项会关闭选项 --chunk-filesize
-F, --chunk-filesize
表分割成chunks的大小，单位为MB，这个指定大小默认为加1MB，如果想切割成每个3MB大小的文件，则指定 -F 2，如果指定 -F 1，则不进行切割，不清楚为什么这么设置
-c, --compress
压缩输出文件
-e, --build-empty-files
表中如果没有数据也创建dump文件
-x, --regex
正则匹配，如'db.table'
-i, --ignore-engines
忽略存储引擎，如有多个用逗号分隔
-N, --insert-ignore
dump文件中不使用INSERT语句
-m, --no-schemas
dump文件中只有表数据而没有表结构信息
-d, --no-data
dump文件中只有表结构而没有表数据
-G, --triggers
dump触发器
-E, --events
dump事件
-R, --routines
dump存储过程和函数
-W, --no-views
不要dump视图
-k, --no-locks
不执行临时的共享读锁，这有可能会导致不一致的备份
--no-backup-locks
不使用Percona备份锁
--less-locking
最小化对InnoDB表的锁定时间
-l, --long-query-guard
设置长查询的时间, 单位秒，默认60秒
-K, --kill-long-queries
Kill长时间执行的查询 (instead of aborting)
-D, --daemon
指定为守护进程模式
-I, --snapshot-interval
每次dump的快照间隔，单位分钟，需要开启 --daemon，默认60分钟
-L, --logfile
指定输出日志文件名，默认为屏幕标准输出
--tz-utc
在dump一开始加入时区timestamp，数据移动或恢复至不同时区上的数据库适用，默认通过选项 --skip-tz-utc 来禁用
--skip-tz-utc
用法如上
--use-savepoints
通过使用savepoints来避免元数据锁的产生，需要SUPER权限
--success-on-1146
不统计增量错误和警告，除非是表不存在的错误
--lock-all-tables
使用LOCK TABLE锁定所有表，代替FTWRL
-U, --updated-since
指定Update_time天数来dump只在过去几天内更新的表
--trx-consistency-only
事务一致性备份导出
--complete-insert
dump文件中包含完整的INSERT语句，语句中包含所有字段的名称
-h, --host
指定连接host
-u, --user
指定连接用户，需有相应的权限
-p, --password
指定用户密码
-a, --ask-password
指定用户密码提示输入
-P, --port
指定连接port
-S, --socket
指定本地socket文件连接
-t, --threads
指定dump线程数, 默认是4
-C, --compress-protocol
在mysql连接时使用压缩协议
-V, --version
显示程序版本并退出
-v, --verbose
显示更详细的输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认是2
--defaults-file
指定默认参数文件
--ssl
使用SSL连接
--key
指定key file的文件路径
--cert
指定证书文件路径
--ca
指定证书授权文件路径
--capath
指定所有CA颁发的PEM格式文件路径
--cipher
指定允许使用SSL连接加密的密码列表

备份流程

测试MySQL版本为官方社区版5.7.24。

(root@localhost) [test] > select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

通过开启mysql的general log观察下mydumper在备份过程中做了哪些操作。

开启general log

(root@localhost) [(none)] > show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /data/mysql/3306/data/dbabd.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

(root@localhost) [(none)] > set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)] > show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /data/mysql/3306/data/dbabd.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

备份test库

# mydumper -h 192.168.58.3 -u root -a -P 3306 -B test -o /data/test/

备份文件结构(以test.t1表为例)：

# ll /data/test/
total 66728
-rw-r--r--. 1 root root      136 Dec 27 16:02 metadata
-rw-r--r--. 1 root root       63 Dec 27 16:02 test-schema-create.sql
-rw-r--r--. 1 root root      278 Dec 27 16:02 test.t1-schema.sql
-rw-r--r--. 1 root root 18390048 Dec 27 16:02 test.t1.sql

通过以上信息可知，备份所有文件都存放在一个目录当中，可以指定。如果没有指定路径，则在运行mydumper命令的当前目录下，生成一个新的目录，名称命名规则为：export-yyyymmdd-HHMMSS 。每个备份目录中主要产生的备份文件为：

metadata文件

metadata：备份元数据信息。包含备份开始和备份结束时间，以及MASTER LOG FILE和MASTER LOG POS。如果是在从库进行备份，则记录的是 SHOW SLAVE STATUS 中同步到的主库binlog文件及binlog位置。

# cat metadata
Started dump at: 2018-12-27 16:02:06
SHOW MASTER STATUS:
        Log: mysql-bin.000034
        Pos: 154
        GTID:

Finished dump at: 2018-12-27 16:02:35

库创建语句文件

test-schema-create.sql：test库的创建语句。

# cat test-schema-create.sql
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

每张表两个备份文件

test.t1-schema.sql：t1表的创建语句。

test.t1.sql：t1表数据文件，以INSERT语句存储。

如果涉及到大表进行表切片备份的话，会有多个表数据文件。

查看general log

-- 主线程连接数据库，设置临时session级别参数
 7   Connect   admin@dbabd on test using TCP/IP
 7   Query     SET SESSION wait_timeout = 2147483
 7   Query     SET SESSION net_write_timeout = 2147483
 7   Query     SHOW PROCESSLIST

-- 主线程执行FTWRL获取全局读锁，并开启一致性快照事务，记录当前binlog文件及位置
 7   Query     FLUSH TABLES WITH READ LOCK
 7   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
 7   Query     /*!40101 SET NAMES binary*/
 7   Query     SHOW MASTER STATUS
 7   Query     SHOW SLAVE STATUS

-- 产生了4个子进程，并且设置会话级事务隔离级别为REPEATABLE READ，4个子线程同时进行dump操作
 8   Connect   admin@dbabd on  using TCP/IP
 8   Query     SET SESSION wait_timeout = 2147483
 8   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
 8   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
 8   Query     /*!40103 SET TIME_ZONE='+00:00' */
 8   Query     /*!40101 SET NAMES binary*/

 9   Connect   admin@dbabd on  using TCP/IP
 9   Query     SET SESSION wait_timeout = 2147483
 9   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
 9   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
 9   Query     /*!40103 SET TIME_ZONE='+00:00' */
 9   Query     /*!40101 SET NAMES binary*/

10   Connect   admin@dbabd on  using TCP/IP
10   Query     SET SESSION wait_timeout = 2147483
10   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
10   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
10   Query     /*!40103 SET TIME_ZONE='+00:00' */
10   Query     /*!40101 SET NAMES binary*/

11   Connect   admin@dbabd on  using TCP/IP
11   Query     SET SESSION wait_timeout = 2147483
11   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
11   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
11   Query     /*!40103 SET TIME_ZONE='+00:00' */
11   Query     /*!40101 SET NAMES binary*/

-- 主线程获取备份库语句和表状态 
 7   Init DB   test
 7   Query     SHOW TABLE STATUS
 7   Query     SHOW CREATE DATABASE `test`

-- 4个子进程备份库中所有的表
 8   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='course' and extra like '%GENERATED%'
11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t' and extra like '%GENERATED%'
10   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t2' and extra like '%GENERATED%'
 9   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1' and extra like '%GENERATED%'
 8   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`course`
 7   Query     UNLOCK TABLES /* FTWRL */
 7   Quit
11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t`
 9   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t1`
10   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t2`
11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t3' and extra like '%GENERATED%'
11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t3`
11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='teacher' and extra like '%GENERATED%'
11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`teacher`
 8   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='teachercard' and extra like '%GENERATED%'
 8   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`teachercard`
 8   Query     SHOW CREATE TABLE `test`.`course`
 8   Query     SHOW CREATE TABLE `test`.`t`
 8   Query     SHOW CREATE TABLE `test`.`t1`
 8   Query     SHOW CREATE TABLE `test`.`t2`
 8   Query     SHOW CREATE TABLE `test`.`t3`
 8   Query     SHOW CREATE TABLE `test`.`teacher`
 8   Query     SHOW CREATE TABLE `test`.`teachercard`
 8   Query     SHOW CREATE TABLE `test`.`v9_pic_tag_content`
11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='v9_pic_tag_content' and extra like '%GENERATED%'
 8   Quit
11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`v9_pic_tag_content`
 9   Quit
10   Quit
11   Quit

总结下mydumper的工作流程：

主线程连接MySQL，查询当前服务线程状态确定是否中止dump或是KILL长查询；
通过FTWRL获取全局读锁，确保dump一致性，开启一致性快照事务，查询当前binlog信息写入metadata文件；
创建多个子线程(默认4个)，开启一致性快照事务，将session级事务隔离级别设置成REPEATABLE READ；
子线程备份非事务引擎表(non-InnoDB tables)；
待子线程备份完非事务引擎表后，主线程执行UNLOCK TABLES释放全局读锁；
子线程备份事务引擎表(InnoDB tables)；
(如有)子线程备份函数、存储过程、触发器和视图；
dump过程结束。

用法示例

备份全库

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -o /data/backupdir

备份某个库

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -o /data/test/

备份多个库(可使用正则匹配)

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -x '^(test.|test2.)' -o /data/

不备份某(几)个库

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -x '^(?!(mysql.|sys.))' -o /data/

备份某(几)张表

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t1,test2.t3 -o /data/

不备份某(几)张表

通过正则匹配

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -x '^(?!test.t2)' -o /data/test/

通过选项 -O, --omit-from-file

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -O nodump.file -o /data/test/

切割表数据文件，指定每份文件包含行数

test.t2表有100万行：

(root@localhost) [test] > select count(*) from t2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.45 sec)

现在指定备份test.t2表分割成每个chunks包含的行数为10万行：

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t2 -r 100000 -o /data/test/

查看表备份文件：

# ls /data/test/
metadata           test.t2.00001.sql  test.t2.00003.sql  test.t2.00005.sql  test.t2.00007.sql  test.t2.00009.sql
test.t2.00000.sql  test.t2.00002.sql  test.t2.00004.sql  test.t2.00006.sql  test.t2.00008.sql  test.t2-schema.sql

切割表数据文件，指定每份文件大小

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t2 -F 2 -o /data/test/

查看表备份文件：

# ll -h /data/test/
total 18M
-rw-r--r--. 1 root root  141 Dec 27 16:32 metadata
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00001.sql
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00002.sql
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00003.sql
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00004.sql
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00005.sql
-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00006.sql
-rw-r--r--. 1 root root 381K Dec 27 16:32 test.t2.00007.sql
-rw-r--r--. 1 root root  278 Dec 27 16:32 test.t2-schema.sql

对备份文件进行压缩

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -c -o /data/test/

没压缩之前的大小：

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -o /data/test/

# du /data/test  --max-depth=1 -h
53M     /data/test

压缩之后的大小：

# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -c -o /data/test/

# du /data/test/  --max-depth=1 -h
22M     /data/test/

对空表备份也生成数据文件
```
# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -e -o /data/test/
```
这样即使是张空表，不仅备份会生成table-schema.sql文件，也会生成table.sql文件。
指定备份子线程数
```
# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -e -t 8 -o /data/test/
```
没指定 -t 选项默认是4个子线程，可以根据机器配置进行适当增加子线程数加快备份效率。

参考

☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆

相关阅读:
12-factor应用和微服务架构应用的区别
 SAP云平台里Global Account和Sub Account的关系
 SAP成都研究院李三郎：SCP Application Router简介
 介绍两个好玩的和Github相关的Chrome扩展
 Java，JavaScript和ABAP通过代码取得当前代码的调用栈Callstack
SAP CRM和C4C的内容管理(Content Management)
CRM WebClient UI的浏览器打印实现
 通过ABAP代码判断当前系统类型，BYD还是S4 OP还是S4 Cloud
使用ABAP批量下载Markdown源文件里的图片到本地
 ABAP下载的病毒扫描Virus Scan
原文地址：https://www.cnblogs.com/dbabd/p/10191348.html