MySQL并行导入导出工具——mysqlpump
一、mysqlpump简介mysql官方从5.7开始推出了mysqlpump工具,它和mysqldump一样属于逻辑备份。1. 优点基于表并行备份数据库和数据库中对象,加快备份过程。(--default-parallelism)更好地控制数据库和数据库对象(表,存储过程,用户帐户)的备份。备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到My...
一、 mysqlpump简介
mysql官方从5.7开始推出了mysqlpump工具,它和mysqldump一样属于逻辑备份。
1. 优点
- 基于表并行备份数据库和数据库中对象,加快备份过程。(--default-parallelism)
- 更好地控制数据库和数据库对象(表,存储过程,用户帐户)的备份。
- 备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到MySQL的系统数据库。
- 支持直接压缩导出
- 导出可显示进度(估计值)。
- 导出时可以排除或指定数据库。
- 导入备份文件时,先建表后插入数据最后建立索引,减少了索引维护开销,加快了还原速度。
2. 不足
- 官方表示在5.7.11之前无法保证数据的一致性,所以5.7.11之前该工具基本无法使用
- 5.7.11之前,--defaut-parallelism>0时与--single-transaction互斥,无法使用并行。直到5.7.11才解决了--single-transaction和--default-parallelism互斥的问题
- mysqlpump的并行是基于表的,对db中只有几个超大表其余表都很小的场景,并行备份基本无用,甚至速度还不如mysqldump
二、mysqlpump备份原理
并行导出的架构为:队列+线程。允许有多个队列(--parallel-schemas),每个队列下有多个线程(N),每个队列可以绑定1个或者多个数据库(逗号分隔)。mysqlpump的备份是基于表并行的,对于每张表的导出只能是单个线程的。如果某个库有一张表非常大,可能大部分的时间都是消耗在这个表的备份上面,mysqlpump并行备份的效果就不明显,此时可以利用mydumper导出。mydumper是以chunk方式批量导出,即它支持对表的并行导出。
三、mysqlpump重要参数
mysqlpump参数与mysqldump基本类似,下面只介绍重要及常用的参数,其余参数参考官方文档
MySQL :: MySQL 5.7 Reference Manual :: 4.5.6 mysqlpump — A Database Backup Program
参数 | 含义 | 备注 |
-A, --all-databases | 备份所有数据库 | |
--add-locks | 备份表时使用LOCK TABLES和UNLOCK TABLES | 这个参数不支持并行备份,需要关闭并行备份功能:default-parallelism=0 |
--character-sets-dir=name | 指定备份数据导出字符集 | |
--compress-output=name | 直接压缩导出 | 目前支持LZ4和ZLIB压缩算法 |
-B, --databases | 指定备份数据库,多个库之间用逗号分隔 | |
--default-parallelism | 默认备份并行线程数,设为0表示默认不使用并行备份 | 默认为2 |
--defer-table-indexes | 延迟创建索引,将全部数据备份结束后再创建索引,默认开启。 | mysqldump导入先创建表和索引,然后加载原数据,资源消耗不仅有备份还有对二级索引的维护 |
--exclude-databases=name | 备份时排除该参数指定的数据库,多个数据库之前使用,分隔 | |
--exclude-tables=name | 备份时排除该参数指定的表,多个表之前使用,分隔 | |
--include-databases=name | 备份指定数据库,多个数据库之前使用,分隔 | |
--include-tables=name | 备份指定表,多个表之前使用,分隔 | |
--parallel-schemas=[N:]db_list | 指定要并行备份的库及并行度N,多个库之间用逗号分隔。(注意是指定的库一共用N个线程,而不是每个用N个线程) | 若不指定并行度则由-default-parallelism参数决定,默认为2。 |
-d, --skip-dump-rows | 只备份表结构,不备份数据 | |
--users | 备份数据库用户,备份形式为create user ... ,grant .... | 如果只需要备份数据库账号可以使用 mysqlpump --exclude-databases=% --users |
--add-drop-user | 在CREATE USER语句之前增加DROP USER | 这个参数需要和--users一起使用,否则不生效 |
--watch-progress | 显示备份进度,默认开启 | |
--single-transaction | 对于innodb表,在备份开始的时候会开启一个事务,并且设置隔离级别为RR,保证备份数据的一致性。备份期间应避免DDL。 | 5.7.11后解决了--single-transaction才能与--default-parallelism共用 |
四、 常见用法
1. 指定库导出:(-B, --databases)
#单库导出
mysqlpump -B webplus > backup.sql
#多库导出
mysqlpump --databases webplus1webplus2 webplus3 > backup.sql
2. 全库导出:(-A, --all-databases)
mysqlpump --all-databases > backup.sql
3. 导出指定表:
mysqlpump 库名 表1 表2 > backup.sql
mysqlpump webplus T_ARTICLE T_SITEARTICLE > backup.sql
4. 导出时排查指定库、表:
mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2 > backup.sql
mysqlpump --user=root --password --exclude-databases=db1,db2 > backup.sql
5. 指定错误日志
mysqlpump --log-error-file=/opt/mysqlerror.logwebplus > backup.sql
6. 不锁表导出
--single-transaction参数使用时mysql不锁表热备份,但是备份时需要避免使用DDL语句
mysqlpump --single-transactionwebplus > backup.sql
7. 多线程导出
默认线程(--default-parallelism)和指定线程(--parallel-schemas)
mysqlpump --default-parallelism=5 --parallel-schemas=4:webplusA --parallel-schemas=3:webplusB,webplusC webplusD >backup.sql
此时webplusA调用4线程,webplusB、C调用3线程,webplusD调用5线程(默认值),show processlist将会看到4+3+5=12线程正在进行pump。图为pump 5线程与dump 单线程比较,运行前均重启数据库
8. 压缩导出
使用zlib/lz4进行压缩导出(导出时同时压缩,含有--compress-output时--compress为默认值,--compress可省略)
mysqlpump --compress --compress-output=LZ4> backup.lz4
mysqlpump --compress --compress-output=ZLIB> backup.zlib
实际压缩时间及文件大小:
- dump不压缩 57秒 大小约1800MB
- dump+tar: 2分05秒 压缩文件约333M 约占原文件18.5%
- pump zlib: 1分33秒 压缩文件约346M 约占原文件19.2%
- pump lz4: 30秒 压缩文件约509M 约占原文件28.2%
参考
更多推荐
所有评论(0)