阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

mysqlpump与mysqldump及mydumper的备份速度测试

211次阅读
没有评论

共计 6380 个字符,预计需要花费 16 分钟才能阅读完成。

准备工作:先建立一个 test 库,并包含 10 个 10000000 行记录的表。
sysbench –test=/data/software/sysbench-0.5/sysbench/tests/db/oltp.lua –MySQL-table-engine=innodb –oltp-table-size=10000000 –max-requests=10000 \
 –num-threads=100 –oltp_tables_count=10 –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=admin –mysql-password=Dmjxjbgc6u \
 –mysql-db=test –max-time=600 –mysql-socket=/tmp/mysql3306.sock prepare
对比测试:
1.mysqlpump 压缩备份 test 数据库 (21G) 三个并发线程备份,消耗时间:2m42.515s,gzip 压缩率要比 LZ4 大
time mysqlpump -uroot -p -h127.0.0.1 –single-transaction –default-character-set=utf8 –compress-output=LZ4 –default-parallelism=3 -B test

2.mysqldump 压缩备份 test 数据库(21G),消耗时间:28m9.930s,gzip 压缩率要比 LZ4 大
time mysqldump -uroot -p -h127.0.0.1 –default-character-set=utf8 -P3306 –skip-opt –add-drop-table –create-options  –quick –extended-insert –single-transaction -B test | gzip > /data/mysql/mysql3306/data/test_db.sql.gz

3.mydumper 压缩备份 test 数据库(21G),开三个并发线程,消耗时间:10m10.207s,gzip 压缩率要比 LZ4 大
time mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -c -B test -o /data/mysql/mysql3306/data/

4.mydumper 压缩备份 test 数据库(21G),三个并发线程备份,并且开启对一张表多个线程以 chunk 的方式批量导出,消耗时间:10m9.518s
 time mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -r 300000 -c -B test -o /data/mysql/mysql3306/data/

从上面看出,mysqlpump 的备份效率是最快的,mydumper 次之,mysqldump 最差。所以在 IO 允许的情况下,能用多线程就别用单线程备份。并且 mysqlpump 还支持多数据库的并行备份,而 mydumper 要么备份一个库,要么就备份所有库。
 由于实际情况不同,测试给出的速度提升只是参考。到底开启多少个并行备份的线程,这个看磁盘 IO 的承受能力,若该服务器只进行备份任务,可以最大限制的来利用磁盘

总结:
mysqldump 和 mysqlpump 的使用方法绝大部分一致,mysqlpump 新的参数文章上已经标明,到底用那种工具备份数据库这个要在具体的环境下才能做出选择,
 有些时候可能用物理备份更好(xtrabackup),总之根据需要进行测试,最后再决定使用哪种备份工具进行备份。

 

MySQL5.7 之后多了一个备份工具:mysqlpump。它是 mysqldump 的一个衍生,mysqldump 就不多说明了,现在看看 mysqlpump 到底有了哪些提升,可以查看官方文档,这里针对如何使用做下说明。

mysqlpump 和 mysqldump 一样,属于逻辑备份,备份以 SQL 形式的文本保存。逻辑备份相对物理备份的好处是不关心 undo log 的大小,直接备份数据即可。它最主要的特点是:
•并行备份数据库和数据库中的对象的,加快备份过程。
•更好的控制数据库和数据库对象(表,存储过程,用户帐户)的备份。
•备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到 MySQL 的系统数据库。
•备份出来直接生成压缩后的备份文件。
•备份进度指示(估计值)。
•重新加载(还原)备份文件,先建表后插入数据最后建立索引,减少了索引维护开销,加快了还原速度。
•备份可以排除或则指定数据库。
 参数:绝大部分参数和 mysqldump 一致,顺便复习一下。
1:–add-drop-database:在建立库之前先执行删库操作。
DROP DATABASE IF EXISTS `…`;2:–add-drop-table:在建表之前先执行删表操作。
DROP TABLE IF EXISTS `…`.`…`;3:–add-drop-user:在 CREATE USER 语句之前增加 DROP USER,注意:这个参数需要和 –users 一起使用,否者不生效。
DROP USER ‘backup’@’192.168.123.%’;4:–add-locks:备份表时,使用 LOCK TABLES 和 UNLOCK TABLES。注意:这个参数不支持并行备份,需要关闭并行备份功能:–default-parallelism=0

LOCK TABLES `…`.`…` WRITE;…
 UNLOCK TABLES;5:–all-databases:备份所有库,-A。
6:–bind-address:指定通过哪个网络接口来连接 Mysql 服务器(一台服务器可能有多个 IP),防止同一个网卡出去影响业务。
7:–complete-insert:dump 出包含所有列的完整 insert 语句。
8:–compress:压缩客户端和服务器传输的所有的数据,-C。
9:–compress-output:默认不压缩输出,目前可以使用的压缩算法有 LZ4 和 ZLIB。
shell> mysqlpump –compress-output=LZ4 > dump.lz4
 shell> lz4_decompress dump.lz4 dump.txt
 shell> mysqlpump –compress-output=ZLIB > dump.zlib
 shell> zlib_decompress dump.zlib dump.txt10:–databases:手动指定要备份的库,支持多个数据库,用空格分隔,-B。
11:–default-character-set:指定备份的字符集。
12:–default-parallelism:指定并行线程数,默认是 2,如果设置成 0,表示不使用并行备份。注意:每个线程的备份步骤是:先 create table 但不建立二级索引(主键会在 create table 时候建立),再写入数据,最后建立二级索引。
13:–defer-table-indexes:延迟创建索引,直到所有数据都加载完之后,再创建索引,默认开启。若关闭则会和 mysqldump 一样:先创建一个表和所有索引,再导入数据,因为在加载还原数据的时候要维护二级索引的开销,导致效率比较低。关闭使用参数:–skip–defer-table-indexes。
14:–events:备份数据库的事件,默认开启,关闭使用 –skip-events 参数。
15:–exclude-databases:备份排除该参数指定的数据库,多个用逗号分隔。类似的还有 –exclude-events、–exclude-routines、–exclude-tables、–exclude-triggers、–exclude-users。
mysqlpump –exclude-databases=mysql,sys    #备份过滤 mysql 和 sys 数据库
mysqlpump –exclude-tables=rr,tt  #备份过滤所有数据库中 rr、tt 表
mysqlpump -B test –exclude-tables=tmp_ifulltext,tt #备份过滤 test 库中的 rr、tt 表
… 注意:要是只备份数据库的账号,需要添加参数 –users,并且需要过滤掉所有的数据库,如:
mysqlpump –users –exclude-databases=sys,mysql,db1,db2 –exclude-users=dba,backup  #备份除 dba 和 backup 的所有账号。16:–include-databases:指定备份数据库,多个用逗号分隔,类似的还有 –include-events、–include-routines、–include-tables、–include-triggers、–include-users,大致方法使用同 15。
17:–insert-ignore:备份用 insert ignore 语句代替 insert 语句。
18:–log-error-file:备份出现的 warnings 和 erros 信息输出到一个指定的文件。
19:–max-allowed-packet:备份时用于 client/server 直接通信的最大 buffer 包的大小。
20:–net-buffer-length:备份时用于 client/server 通信的初始 buffer 大小,当创建多行插入语句的时候,mysqlpump 创建行到 N 个字节长。
21:–no-create-db:备份不写 CREATE DATABASE 语句。要是备份多个库,需要使用参数 -B,而使用 - B 的时候会出现 create database 语句,该参数可以屏蔽 create database 语句。
22:–no-create-info:备份不写建表语句,即不备份表结构,只备份数据,-t。
23:–hex-blob:备份 binary 字段的时候使用十六进制计数法,受影响的字段类型有 BINARY、VARBINARY、BLOB、BIT。
24:–host:备份指定的数据库地址,-h。
25:–parallel-schemas=[N:]db_list:指定并行备份的库,多个库用逗号分隔,如果指定了 N,将使用 N 个线程的地队列,如果 N 不指定,将由 –default-parallelism 才确认 N 的值,可以设置多个 –parallel-schemas。
mysqlpump –parallel-schemas=4:vs,aa –parallel-schemas=3:pt  #4 个线程备份 vs 和 aa,3 个线程备份 pt。通过 show processlist 可以看到有 7 个线程。
mysqlpump –parallel-schemas=vs,abc –parallel-schemas=pt  #默认 2 个线程,即 2 个线程备份 vs 和 abc,2 个线程备份 pt

#### 当然要是硬盘 IO 不允许的话,可以少开几个线程和数据库进行并行备份 26:–password:备份需要的密码。

27:–port:备份数据库的端口。
28:–protocol={TCP|SOCKET|PIPE|MEMORY}:指定连接服务器的协议。
29:–replace:备份出来 replace into 语句。
30:–routines:备份出来包含存储过程和函数,默认开启,需要对 mysql.proc 表有查看权限。生成的文件中会包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句以用于恢复,关闭则需要用 –skip-routines 参数。
31:–triggers:备份出来包含触发器,默认开启,使用 –skip-triggers 来关闭。
31:–set-charset:备份文件里写 SET NAMES default_character_set 到输出,此参默认开启。— skip-set-charset 禁用此参数,不会在备份文件里面写出 set names…
 32:–single-transaction:该参数在事务隔离级别设置成 Repeatable Read,并在 dump 之前发送 start transaction 语句给服务端。这在使用 innodb 时很有用,因为在发出 start transaction 时,保证了在不阻塞任何应用下的一致性状态。对 myisam 和 memory 等非事务表,还是会改变状态的,当使用此参的时候要确保没有其他连接在使用 ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE 等语句,否则会出现不正确的内容或则失败。–add-locks 和此参互斥,在 mysql5.7.11 之前,–default-parallelism 大于 1 的时候和此参也互斥,必须使用 –default-parallelism=0。5.7.11 之后解决了 –single-transaction 和 –default-parallelism 的互斥问题。
33:–skip-definer:忽略那些创建视图和存储过程用到的 DEFINER 和 SQL SECURITY 语句,恢复的时候,会使用默认值,否则会在还原的时候看到没有 DEFINER 定义时的账号而报错。
34:–skip-dump-rows:只备份表结构,不备份数据,-d。注意:mysqldump 支持 –no-data,mysqlpump 不支持 –no-data
 35:–socket:对于连接到 localhost,Unix 使用套接字文件,在 Windows 上是命名管道的名称使用,-S。
36:–ssl:–ssl 参数将要被去除,用 –ssl-mode 取代。关于 ssl 相关的备份,请看官方文档。
37:–tz-utc:备份时会在备份文件的最前几行添加 SET TIME_ZONE=’+00:00’。注意:如果还原的服务器不在同一个时区并且还原表中的列有 timestamp 字段,会导致还原出来的结果不一致。默认开启该参数,用 –skip-tz-utc 来关闭参数。
38:–user:备份时候的用户名,-u。
39:–users:备份数据库用户,备份的形式是 CREATE USER…,GRANT…,只备份数据库账号可以通过如下命令:
mysqlpump –exclude-databases=% –users    #过滤掉所有数据库 40:–watch-progress:定期显示进度的完成,包括总数表、行和其他��象。该参数默认开启,用 –skip-watch-progress 来关闭。

使用说明:
mysqlpump 支持基于库和表的并行导出,mysqlpump 的并行导出功能的架构为:队列 + 线程,允许有多个队列(–parallel-schemas?),每个队列下有多个线程(N?),而一个队列可以绑定 1 个或者多个数据库(逗号分隔)。mysqlpump 的备份是基于表并行的,对于每张表的导出只能是单个线程的,这里会有个限制是如果某个数据库有一张表非常大,可能大部分的时间都是消耗在这个表的备份上面,并行备份的效果可能就不明显。这里可以利用 mydumper 其是以 chunk 的方式批量导出,即 mydumper 支持一张表多个线程以 chunk 的方式批量导出。但是相对于 mysqldump 还是有了很大的提升。

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-12/138170.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计6380字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中