暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL史上最快逻辑备份工具

MySQL史上最快逻辑备份工具

玩转MySQL 2020-07-29
2617

MySQL Shell 8.0.21 增加了一种新的逻辑备份恢复方法，有更快的备份恢复效率，支持zstd实时压缩，支持分块并行导出，load data并行导入，还能备份到OCI的对象存储。

  • util.dumpInstance() 用于备份整个实例

  • util.dumpSchemas() 用于备份指定schema

  • util.loadDump() 用于恢复备份

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

对比结果

Backup Method

Backup

time (sec)

Restore

time (sec)

Backup

size

utli.dumpSchemas

18 86 886MB

utli.dumpSchemas

(no compress no chunk)

35 1952GB
mysqldump (with gzip)‍‍169 255962MB
mysqldump (with zstd)28 246996MB
mysqldump (no compress)29 2482GB
mysqlpump (with gzip)‍‍‍‍185 121970M
mysqlpump(no gzip)50 1342GB

mydumper 

(compress no chunk)

‍

‍164 

187969MB

 mydumper

(no compress chunk 1K row)

582162GB

mydumper

(no compress no chunk)

15158 2GB

结论

  • mysql shell

    使用默认参数zstd压缩+32M chunk并行导出，恢复时单表可以并行load data，其备份和恢复速度均优于非压缩+非分块。

    测试中发现，若禁用压缩，也会禁用分块。

  •  mysqldump

    备份和恢复都是单线程执行，不压缩的备份效率更快，zstd的实时备份速度比gzip更快，恢复速度最慢。

  • mysqlpump 

    备份支持并行速度也很快，但是单线程恢复是硬伤。

  • mydumper 

    默认用gzip协议，备份速度与mysqldump基本一样，看来瓶颈在压缩上。

    在非压缩非分块备份速度会更快。

    恢复速度中等，单表无法并行。

综合上述测试结果，mysql shell新的备份恢复方式是最快的，得益于使用了zstd实时压缩算法，备份恢复均可以并行，对于单个大表也可以并行。

下面是部分测试过程供参考。

MySQL Shell

utli.dumpSchemas/utli.loadDump

  • 备份

    mysqlsh> util.dumpSchemas(["test"],"test1")
    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)
      复制

      mysqldump

      • 备份

        /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
          复制

          mysqlpump

          • 备份

            /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
              复制

              mydumper/myloader

              • 备份

                /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
                        187.10 real        14.24 user         1.97 sys
                  复制



                  mysql
                  最后修改时间：2020-08-03 09:55:07
                  文章转载自玩转MySQL，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                  评论

                  相关阅读

                  【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费！教你免费领考券
                  墨天轮小教习
                  2527次阅读
                  2025-04-25 18:53:11
                  MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
                  严少安
                  781次阅读
                  2025-04-25 15:30:58
                  【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
                  墨天轮编辑部
                  438次阅读
                  2025-04-17 17:02:24
                  MySQL 9.3 正式 GA，我却大失所望，新特性亮点与隐忧并存？
                  JiekeXu
                  407次阅读
                  2025-04-15 23:49:58
                  3月“墨力原创作者计划”获奖名单公布
                  墨天轮编辑部
                  366次阅读
                  2025-04-15 14:48:05
                  openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
                  严少安
                  356次阅读
                  2025-04-07 12:14:29
                  记录MySQL数据库的一些奇怪的迁移需求！
                  陈举超
                  262次阅读
                  2025-04-15 15:27:53
                  MySQL 8.0 OCP 1Z0-908 考试解析指南(二)
                  JiekeXu
                  217次阅读
                  2025-04-30 17:37:37
                  MySQL 8.4 新特性深度解析：功能增强、废弃项与移除项全指南
                  JiekeXu
                  210次阅读
                  2025-04-18 20:21:32
                  GreatSQL 新版发布：MySQL 牵手“鸭子”
                  严少安
                  146次阅读
                  2025-04-19 14:57:56