关于mysql:技术分享-MySQL史上最快逻辑备份工具

41次阅读

共计 6667 个字符，预计需要花费 17 分钟才能阅读完成。

作者：洪斌
爱可生南区负责人兼技术服务总监，MySQL ACE，善于数据库架构布局、故障诊断、性能优化剖析，实践经验丰盛，帮忙各行业客户解决 MySQL 技术问题，为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。
本文起源：转载自公众号 - 玩转 MySQL
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

MySQL Shell 8.0.21 减少了一种新的逻辑备份复原办法，有更快的备份复原效率，反对 zstd 实时压缩，反对分块并行导出，load data 并行导入，还能备份到 OCI 的对象存储。

util.dumpInstance() 用于备份整个实例
util.dumpSchemas() 用于备份指定 schema
util.loadDump() 用于复原备份

做了个比照测试，在零负载下 mysql 配置参数不变，备份 / 复原雷同 schema，其中混合了大表和小表，看下这几种形式的实际效果如何。

mysql shell
应用默认参数 zstd 压缩 +32M chunk 并行导出，复原时单表能够并行 load data，其备份和复原速度均优于非压缩 + 非分块。
测试中发现，若禁用压缩，也会禁用分块。
mysqldump
备份和复原都是单线程执行，不压缩的备份效率更快，zstd 的实时备份速度比 gzip 更快，复原速度最慢。
mysqlpump
备份反对并行速度也很快，然而单线程复原是硬伤。
mydumper
默认用 gzip 协定，备份速度与 mysqldump 根本一样，看来瓶颈在压缩上。在非压缩非分块备份速度会更快。复原速度中等，单表无奈并行。

综合上述测试后果，mysql shell 新的备份复原形式是最快的，得益于应用了 zstd 实时压缩算法，备份复原均能够并行，对于单个大表也能够并行。

上面是局部测试过程供参考：

备份

Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `test`.`customer1`
Writing DDL for schema `test`
Writing DDL for table `test`.`sbtest1`
Writing DDL for table `test`.`customer1`
Writing DDL for table `test`.`sbtest10`
Data dump for table `test`.`customer1` will be chunked using column `c_w_id`
Preparing data dump for table `test`.`sbtest1`
Data dump for table `test`.`sbtest1` will be chunked using column `id`
Preparing data dump for table `test`.`sbtest10`
Data dump for table `test`.`sbtest10` will be chunked using column `id`
Preparing data dump for table `test`.`sbtest2`
Data dump for table `test`.`sbtest2` will be chunked using column `id`
Preparing data dump for table `test`.`sbtest4`
Data dump for table `test`.`sbtest4` will be chunked using column `id`
Preparing data dump for table `test`.`sbtest6`
Data dump for table `test`.`sbtest6` will be chunked using column `id`
Preparing data dump for table `test`.`sbtest8`
Data dump for table `test`.`sbtest8` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `test`.`sbtest2`
Writing DDL for table `test`.`sbtest4`
Writing DDL for table `test`.`sbtest6`
Writing DDL for table `test`.`sbtest8`
Data dump for table `test`.`customer1` will be written to 3 files
Data dump for table `test`.`sbtest10` will be written to 1 file
Data dump for table `test`.`sbtest2` will be written to 1 file
Data dump for table `test`.`sbtest4` will be written to 1 file
Data dump for table `test`.`sbtest6` will be written to 1 file
Data dump for table `test`.`sbtest8` will be written to 1 file
Data dump for table `test`.`sbtest1` will be written to 160 files
1 thds dumping - 98% (10.46M rows / ~10.62M rows), 589.52K rows/s, 115.55 MB/s uncompressed, 51.66 MB/s compressed
Duration: 00:00:18s
Schemas dumped: 1
Tables dumped: 7
Uncompressed data size: 2.06 GB
Compressed data size: 922.35 MB
Compression ratio: 2.2
Rows written: 10464999
Bytes written: 922.35 MB
Average uncompressed throughput: 109.46 MB/s
Average compressed throughput: 48.97 MB/s

复原

util.loadDump("test1")
Loading DDL and Data from 'instance' using 4 threads.
Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
Executing DDL script for `test`.`sbtest1`
Executing DDL script for `test`.`sbtest4`
Executing DDL script for `test`.`sbtest2`
Executing DDL script for `test`.`sbtest8`
Executing DDL script for `test`.`sbtest10`
Executing DDL script for `test`.`sbtest6`
Executing DDL script for `test`.`customer1`
...
[Worker000] test@sbtest1@158.tsv.zst: Records: 65736  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL

168 chunks (10.46M rows, 2.06 GB) for 7 tables in 1 schemas were loaded in 1 min 26 sec (avg throughput 23.97 MB/s)

备份

/usr/bin/time mysqldump -umsandbox -pmsandbox -h127.0.0.1 -P8021 test | gzip > db.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
      169.40 real        24.65 user         1.34 sys

复原

 /usr/bin/time gzip -d < db.sql.gz | ./use test
      257.11 real         9.74 user         0.55 sys

备份

/usr/bin/time mysqlpump --default-parallelism=4 -umsandbox -pmsandbox -h127.0.0.1  -P8021 test | gzip > db2.sql.gz
Dump progress: 6/7 tables, 10421749/10406264 rows
Dump completed in 185352
      185.50 real        31.18 user         6.34 sys

复原

/usr/bin/time gzip -d < db2.sql.gz | ./use test
      121.17 real         9.66 user         0.76 sys

备份

/usr/bin/time mydumper -u msandbox -p msandbox -h 127.0.0.1 -P 8021 -B test -t 4 -v 3 -c -o dumper
** Message: 21:44:55.958: Connected to a MySQL server
** Message: 21:44:56.319: Started dump at: 2020-07-24 21:44:56

** Message: 21:44:56.341: Written master status
** Message: 21:44:56.420: Thread 1 connected using MySQL connection ID 22
** Message: 21:44:56.537: Thread 2 connected using MySQL connection ID 23
** Message: 21:44:56.651: Thread 3 connected using MySQL connection ID 24
** Message: 21:44:56.769: Thread 4 connected using MySQL connection ID 25
** Message: 21:44:56.878: Non-InnoDB dump complete, unlocking tables
** Message: 21:44:56.878: Thread 4 dumping data for `test`.`sbtest10`
** Message: 21:44:56.878: Thread 1 dumping data for `test`.`customer1`
** Message: 21:44:56.878: Thread 3 dumping data for `test`.`sbtest1`
** Message: 21:44:56.878: Thread 2 dumping data for `test`.`sbtest2`
** Message: 21:44:57.139: Thread 2 dumping data for `test`.`sbtest4`
** Message: 21:44:57.143: Thread 4 dumping data for `test`.`sbtest6`
** Message: 21:44:57.396: Thread 2 dumping data for `test`.`sbtest8`
** Message: 21:44:57.398: Thread 4 dumping schema for `test`.`customer1`
** Message: 21:44:57.409: Thread 4 dumping schema for `test`.`sbtest1`
** Message: 21:44:57.419: Thread 4 dumping schema for `test`.`sbtest10`
** Message: 21:44:57.430: Thread 4 dumping schema for `test`.`sbtest2`
** Message: 21:44:57.441: Thread 4 dumping schema for `test`.`sbtest4`
** Message: 21:44:57.453: Thread 4 dumping schema for `test`.`sbtest6`
** Message: 21:44:57.464: Thread 4 dumping schema for `test`.`sbtest8`
** Message: 21:44:57.475: Thread 4 shutting down
** Message: 21:44:57.636: Thread 2 shutting down
** Message: 21:45:03.706: Thread 1 shutting down
** Message: 21:47:40.297: Thread 3 shutting down
** Message: 21:47:40.307: Finished dump at: 2020-07-24 21:47:40
      164.54 real       167.58 user         2.28 sys

注：应用的并行备份线程数与 dumpSchema 雷同。

复原

/usr/bin/time myloader -u msandbox -p msandbox -h 127.0.0.1 -P 8021 -B test -t 4 -v 3  -d dumper/
** Message: 23:54:39.961: 4 threads created
** Message: 23:54:39.973: Creating table `test`.`sbtest4`
** Message: 23:54:40.055: Creating table `test`.`sbtest10`
** Message: 23:54:40.127: Creating table `test`.`customer1`
** Message: 23:54:40.201: Creating table `test`.`sbtest8`
** Message: 23:54:40.273: Creating table `test`.`sbtest2`
** Message: 23:54:40.346: Creating table `test`.`sbtest6`
** Message: 23:54:40.423: Creating table `test`.`sbtest1`
** Message: 23:54:40.488: Thread 2 restoring `test`.`sbtest2` part 0
** Message: 23:54:40.488: Thread 3 restoring `test`.`sbtest6` part 0
** Message: 23:54:40.488: Thread 4 restoring `test`.`sbtest8` part 0
** Message: 23:54:40.488: Thread 1 restoring `test`.`sbtest4` part 0
** Message: 23:54:40.833: Thread 2 restoring `test`.`sbtest1` part 0
** Message: 23:54:40.833: Thread 4 restoring `test`.`sbtest10` part 0
** Message: 23:54:40.834: Thread 3 restoring `test`.`customer1` part 0
** Message: 23:54:40.834: Thread 1 shutting down
** Message: 23:54:41.070: Thread 4 shutting down
** Message: 23:54:50.407: Thread 3 shutting down
** Message: 23:57:46.425: Thread 2 shutting down

正文完

发表至： mysql

2020-08-03

0

MySQL-5730-的安装升级所有可能的坑都在这里

关于mysql:这次被坑惨了MySQL的隐式转换导致了一个线上BUG

关于mysql:MySQL无主键表查找

关于mysql:昆仑分布式数据库存储集群-Fullsync-机制

关于javascript:webpack4学习笔记

关于flutter:flutter安装过程中的问题