一、前言

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

二、mysqlpump备份原理:

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

mysqldump 限制速度 mysqldump性能_mysql

三、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 c

-B, --databases 指定备份数据库,多个库之间用逗号分隔

备份并行线程数,默认为2,若指定该参数为0,则表示不使用并行备份。

延迟创建索引,将全部数据备份结束后再创建索引,默认开启。使用mysqldump备份时会先创建表和索引,然后加载原数据,资源消耗不仅有备份还有对二级索引的维护

备份时排除该参数指定的数据库,多个数据库之前使用,分隔

--exclude-tables=name 备份时排除该参数指定的表,多个表之前使用,分隔

备份指定数据库,多个数据库之前使用,分隔

--include-tables=name 备份指定表,多个表之前使用,分隔

指定并行备份的库,多个库之间用逗号分隔。也可以直接指定备份该库启用的线程队列数,若不指定则有-default-parallelism参数决定,默认为2。

-d, --skip-dump-rows 只备份表结构,不备份数据

--users 备份数据库用户,备份形式为create user ... ,grant ....

如果只需要备份数据库账号可以使用 mysqlpump --exclude-databases=% --users

显示备份进度,默认开启

--single-transaction 对于innodb表,在备份开始的时候会开启一个事物,并且设置隔离级别为RR,保证备份数据的一致性。备份期间应避免DDL。

在mysql5.7.11之前,--default-parallelism大于1的时候和此参也互斥,必须使用--default-parallelism=0。

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