MySQL5.7新特性之备份工具mysqlpump的使用

一、前言

mysql在备份方面本身有mysqldump工具来进行数据备份，但是该工具只支持单线程工作，这就使得备份数据的速度大大降低。mysql官方从5.7开始推出了mysqlpump工具，该工具在mysqldump的基础上又新增了很多新功能，使用起来更加直观，备份速度也有了一定的提升，具体如下：

二、mysqlpump备份原理：

mysqlpump的并行导出功能的架构为：队列+线程，允许有多个队列，每个队列下有多个线程，而一个队列可以绑定1个或者多个数据库，从而达到提升备份速度的功能。

三、mysqlpump工具的优势：

• 基于表并行备份数据库和数据库中的对象的，加快备份过程。（--default-parallelism）
• 更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
• 备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
• 备份出来直接生成压缩后的备份文件。
• 备份进度指示，观察备份进度更直观。
• 重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
• 备份可以排除或则指定数据库。

四、mysqlpump工具使用的注意点：

• 官方表示在mysql5.7.11之前无法保证数据的一致性，所以5.7.11之前该工具基本无法使用
• mysqlpump的多线程备份是基于表的，当数据库中有很多张小表个别几张超大表的时候，mysqlpump的备份速度其实还不如mysqldump。

五、mysqlpump基本参数使用：

 -u                          备份用户名
 -p                          指定用户名密码
 -P                          连接数据库端口
 -h                          指定数据库地址
 --login-path=#              可以支持面密码备份数据
 -A, --all-databases 	     备份所有数据库
 --character-sets-dir=name   指定备份数据导出字符集
 --compress-output=name      将备份数据压缩输出，目前支持的压缩算法有LZ4和ZLIB
 -B, --databases             指定备份数据库，多个库之间用逗号分隔
 --default-parallelism=#     备份并行线程数，默认为2，若指定该参数为0，则表示不使用并行备份。
 --defer-table-indexes 	     延迟创建索引，将全部数据备份结束后再创建索引，默认开启。使用mysqldump备份时会先创建表和索引，然后加载原数据，资源消耗不仅有备份还有对二级索引的维护
 --exclude-databases=name    备份时排除该参数指定的数据库，多个数据库之前使用,分隔
 --exclude-tables=name       备份时排除该参数指定的表，多个表之前使用,分隔
 --include-databases=name    备份指定数据库，多个数据库之前使用,分隔
 --include-tables=name       备份指定表，多个表之前使用,分隔
 --parallel-schemas=[N:]db_list    指定并行备份的库，多个库之间用逗号分隔。也可以直接指定备份该库启用的线程队列数，若不指定则有-default-parallelism参数决定，默认为2。              
 -d, --skip-dump-rows        只备份表结构，不备份数据
 --users                     备份数据库用户，备份形式为create user ... ,grant ....
                             如果只需要备份数据库账号可以使用 mysqlpump --exclude-databases=% --users
 --watch-progress            显示备份进度，默认开启
 --single-transaction        对于innodb表，在备份开始的时候会开启一个事物，并且设置隔离级别为RR，保证备份数据的一致性。备份期间应避免DDL。
                             在MySQL5.7.11之前，--defaut-parallelism大于1的时候和该参数互斥，所以必须使用--default-parallelism=0。
                             MySQL 5.7.11后解决了--single-transaction和--default-parallelism互斥的问题。

六、性能测试：

每次测试之前都要重启数据库，清除缓冲池中的内容。

1.mysqlpump压缩test2库

# time mysqlpump -uroot -p --single-transaction --default-parallelism=3  -B test2 >test2_db.sql

Enter password: 
Dump progress: 1/2 tables, 0/1872 rows
Dump progress: 30/134 tables, 342783/5569940 rows
Dump progress: 32/134 tables, 766796/5569940 rows
Dump progress: 37/134 tables, 1004147/5569940 rows
Dump progress: 37/134 tables, 1079147/5569940 rows
Dump progress: 37/134 tables, 1235147/5569940 rows
Dump progress: 39/134 tables, 1540083/5569940 rows
Dump progress: 45/134 tables, 1865938/5569940 rows
Dump progress: 47/134 tables, 1985771/5569940 rows
Dump progress: 48/134 tables, 2066897/5569940 rows
Dump progress: 51/134 tables, 2134073/5569940 rows
Dump progress: 51/134 tables, 2210823/5569940 rows
Dump progress: 57/134 tables, 2409504/5569940 rows
Dump progress: 59/134 tables, 2466028/5569940 rows
Dump progress: 63/134 tables, 2537666/5569940 rows
Dump progress: 66/134 tables, 2779272/5569940 rows
Dump progress: 70/134 tables, 2917791/5569940 rows
Dump progress: 112/134 tables, 3003742/5569940 rows
Dump progress: 115/134 tables, 3299305/5569940 rows
Dump progress: 115/134 tables, 3525305/5569940 rows
Dump progress: 117/134 tables, 3749419/5569940 rows
Dump progress: 118/134 tables, 3893110/5569940 rows
Dump progress: 127/134 tables, 3940505/5569940 rows
Dump progress: 127/134 tables, 4307505/5569940 rows
Dump progress: 130/134 tables, 4745939/5569940 rows
Dump progress: 130/134 tables, 5193439/5569940 rows
Dump progress: 130/134 tables, 5382939/5569940 rows
Dump progress: 132/134 tables, 5538375/5569940 rows
Dump completed in 36222 milliseconds

real 0m38.179s
user 0m14.237s
sys 0m3.782s

        2.mysqldump备份test2库

# time mysqldump -uroot -p --single-transaction test2 >test2_db2.sql
Enter password: 

real 0m47.764s
user 0m10.009s
sys 0m1.619s