MySQL Backup mydumper
2018-12-28 17:10 H_Johnny 阅读(1036) 评论(0) 编辑 收藏 举报生产环境中有一实例每天使用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个子线程，可以根据机器配置进行适当增加子线程数加快备份效率。
参考
- https://github.com/maxbube/mydumper
- http://www.ywnds.com/?p=7267
- http://blog.51cto.com/l0vesql/2057223
☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆
