MySQL 8.0新特性 — 增强版逻辑备份恢复
原创MySQL 8.0新特性 — 增强版逻辑备份恢复
原创
brightdeng@DBA
发布于 2021-04-02 16:03:31
发布于 2021-04-02 16:03:31
前言
关于MySQL逻辑备份恢复，相信大家都不陌生，平时使用频率也是比较高；这里就简单介绍下：
（1）mysqldump：MySQL原生自带的逻辑备份恢复工具，支持整个实例、单个数据库、单张表等的备份与恢复，操作简单且灵活，但由于是单线程运行，备份恢复速度比较慢；
（2）mysqlpump：MySQL 5.7推出的逻辑备份恢复工具，相当于是mysqldump增强版，支持多线程导出，但由于导入仍然是单线程运行，速度依旧比较慢，个人认为还是比较鸡肋；
（3）mydumper/myloader：开源的一个逻辑备份恢复工具，支持多线程导出导入，速度比较快，强烈推荐使用；
在MySQL 8.0版本中，推出了MySQL Shell Utilities，其中就包含最新的逻辑备份恢复工具，可以支持多线程导出导入，解决了mysqldump/mysqlpump速度慢的问题，下面就简单介绍下其使用方法。
MySQL Shell Utilities
MySQL Shell Utilities是MySQL 8.0官方推出的管理工具集合，包括Upgrade Checker Utility、JSON Import Utility、Table Export Utility、Parallel Table Import Utility、Instance Dump Utility、Schema Dump Utility、Table Dump Utility、Dump Loading Utility等，可以支持整个实例、单个数据库、单张表的逻辑备份与恢复；由于测试机器性能一般，下面只介绍基本功能，不提供性能测试数据。
Table Export Utility
Table Export Utility，支持单张表的多线程导出，根据官方文档描述，它会在MySQL Server打开一个线程获取数据，然后自身打开多个线程去处理这些数据。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.export_table("test.sbtest1","/applog/output/sbtest1.txt")
Gathering information - done
Preparing data dump for table `test`.`sbtest1`
Data dump for table `test`.`sbtest1` will use column `id` as an index
Running data dump using 1 thread.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`sbtest1` will be written to 1 file
102% (10.00M rows / ~9.75M rows), 572.85K rows/s, 112.28 MB/s
Duration: 00:00:16s
Data size: 1.96 GB
Rows written: 10000000
Bytes written: 1.96 GB
Average throughput: 117.87 MB/s
The dump can be loaded using:
util.import_table("/applog/output/sbtest1.txt", {
"characterSet": "utf8mb4",
"schema": "test",
"table": "sbtest1"
})（2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| 141 | mysqlshell | 127.0.0.1:40976 | NULL | Query | 16 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest1` ORDER BY `id` /* mysqlsh export_table, dumping table `test`.`sbtest1`, chunk ID: 1 */ |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)Parallel Table Import Utility
Parallel Table Import Utility，配合Table Export Utility使用，支持单张表的多线程导入。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.import_table("/applog/output/sbtest1.txt",{"schema":"test","table":"sbtest1","threads":8})
Importing from file '/applog/output/sbtest1.txt' to table `test`.`sbtest1` in MySQL Server at 127.0.0.1:10080 using 8 threads
[Worker006] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.txt: Records: 256981 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.txt: Records: 256411 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest1.txt: Records: 256277 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.txt: Records: 256411 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker006] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker006] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker006] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest1.txt: Records: 45315 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker006] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.txt: Records: 255103 Deleted: 0 Skipped: 0 Warnings: 0
100% (1.96 GB / 1.96 GB), 11.00 MB/s
File '/applog/output/sbtest1.txt' (1.96 GB) was imported in 1 min 47.9677 sec at 18.14 MB/s
Total rows affected in test.sbtest1: Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0（2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 142 | mysqlshell | 127.0.0.1:41598 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 143 | mysqlshell | 127.0.0.1:41600 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 144 | mysqlshell | 127.0.0.1:41602 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 145 | mysqlshell | 127.0.0.1:41604 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 146 | mysqlshell | 127.0.0.1:41606 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 147 | mysqlshell | 127.0.0.1:41608 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 148 | mysqlshell | 127.0.0.1:41610 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
| 149 | mysqlshell | 127.0.0.1:41612 | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/applog/output/sbtest1.txt' INTO TABLE `test`.`sbtest1` FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.10 sec)Instance Dump Utility
Instance Dump Utility，支持整个实例的多线程导出。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.dump_instance("/applog/output/dumpinstance",{"threads":8})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `test`
Preparing data dump for table `test`.`sbtest2`
Writing DDL for table `test`.`sbtest2`
Data dump for table `test`.`sbtest2` will be chunked using column `id`
Writing DDL for table `test`.`sbtest1`
Preparing data dump for table `test`.`sbtest1`
Data dump for table `test`.`sbtest1` will be chunked using column `id`
Running data dump using 8 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`sbtest1` will be written to 71 files
1 thds chunking, 7 dumping - 3% (641.31K rows / ~18.34M rows), 1.01M rows/s, 197.08 MB/s uncompressed, 89.05 MB/s compresse
Data dump for table `test`.`sbtest2` will be written to 80 files
1 thds dumping - 109% (20.00M rows / ~18.34M rows), 361.68K rows/s, 70.89 MB/s uncompressed, 31.88 MB/s compressed
Duration: 00:00:34s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 3.92 GB
Compressed data size: 1.76 GB
Compression ratio: 2.2
Rows written: 20000000
Bytes written: 1.76 GB
Average uncompressed throughput: 112.03 MB/s
Average compressed throughput: 50.34 MB/s （2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 159 | mysqlshell | 127.0.0.1:42488 | NULL | Sleep | 18 | | NULL |
| 160 | mysqlshell | 127.0.0.1:42490 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 3675838 AND 3807924 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 29 */ |
| 161 | mysqlshell | 127.0.0.1:42492 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 3940012 AND 4063842 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 31 */ |
| 162 | mysqlshell | 127.0.0.1:42494 | test | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 4063843 AND 4187673 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 32 */ |
| 163 | mysqlshell | 127.0.0.1:42496 | test | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 4187674 AND 4311504 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 33 */ |
| 164 | mysqlshell | 127.0.0.1:42498 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 4311505 AND 4435335 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 34 */ |
| 165 | mysqlshell | 127.0.0.1:42500 | NULL | Query | 0 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 4559167 AND 4682997 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 36 */ |
| 166 | mysqlshell | 127.0.0.1:42502 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 3807925 AND 3940011 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 30 */ |
| 167 | mysqlshell | 127.0.0.1:42504 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 4435336 AND 4559166 ORDER BY `id` /* mysqlsh dump_instance, dumping table `test`.`sbtest2`, chunk ID: 35 */ |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)Schema Dump Utility
Schema Dump Utility，支持库级别的多线程导出。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.dump_schemas(["test"],"/applog/output/dumpschema",{"threads":8})
Acquiring global read lock
Global read lock acquired
Gathering information - done
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`.`sbtest2`
Writing DDL for schema `test`
Writing DDL for table `test`.`sbtest2`
Data dump for table `test`.`sbtest2` will be chunked using column `id`
Writing DDL for table `test`.`sbtest1`
Preparing data dump for table `test`.`sbtest1`
Data dump for table `test`.`sbtest1` will be chunked using column `id`
Running data dump using 8 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`sbtest1` will be written to 71 files
Data dump for table `test`.`sbtest2` will be written to 80 files
1 thds dumping - 109% (20.00M rows / ~18.34M rows), 392.64K rows/s, 76.96 MB/s uncompressed, 34.61 MB/s compressed
Duration: 00:00:36s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 3.92 GB
Compressed data size: 1.76 GB
Compression ratio: 2.2
Rows written: 20000000
Bytes written: 1.76 GB
Average uncompressed throughput: 107.28 MB/s
Average compressed throughput: 48.20 MB/s （2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 177 | mysqlshell | 127.0.0.1:42934 | NULL | Sleep | 12 | | NULL |
| 178 | mysqlshell | 127.0.0.1:42936 | NULL | Query | 3 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 386026 AND 551464 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 3 */ |
| 179 | mysqlshell | 127.0.0.1:42938 | test | Query | 2 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1047781 AND 1171859 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 8 */ |
| 180 | mysqlshell | 127.0.0.1:42940 | test | Query | 2 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 675544 AND 799622 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 5 */ |
| 181 | mysqlshell | 127.0.0.1:42942 | NULL | Query | 0 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1420018 AND 1544096 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 11 */ |
| 182 | mysqlshell | 127.0.0.1:42944 | NULL | Query | 2 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 923702 AND 1047780 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 7 */ |
| 183 | mysqlshell | 127.0.0.1:42946 | NULL | Query | 2 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1171860 AND 1295938 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 9 */ |
| 184 | mysqlshell | 127.0.0.1:42948 | NULL | Query | 2 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 799623 AND 923701 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 6 */ |
| 185 | mysqlshell | 127.0.0.1:42950 | NULL | Query | 3 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 551465 AND 675543 ORDER BY `id` /* mysqlsh dump_schemas, dumping table `test`.`sbtest2`, chunk ID: 4 */ |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)Table Dump Utility
Table Dump Utility，支持表级别的多线程导出。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.dump_tables("test",["sbtest1","sbtest2"],"/applog/output/dumptable",{"threads":8})
Acquiring global read lock
Global read lock acquired
Gathering information - done
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`.`sbtest2`
Writing DDL for schema `test`
Data dump for table `test`.`sbtest2` will be chunked using column `id`
Writing DDL for table `test`.`sbtest2`
Writing DDL for table `test`.`sbtest1`
Preparing data dump for table `test`.`sbtest1`
Data dump for table `test`.`sbtest1` will be chunked using column `id`
Running data dump using 8 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`sbtest1` will be written to 71 files
Data dump for table `test`.`sbtest2` will be written to 80 files
1 thds dumping - 109% (20.00M rows / ~18.34M rows), 366.45K rows/s, 71.82 MB/s uncompressed, 32.31 MB/s compressed
Duration: 00:00:34s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 3.92 GB
Compressed data size: 1.76 GB
Compression ratio: 2.2
Rows written: 20000000
Bytes written: 1.76 GB
Average uncompressed throughput: 114.27 MB/s
Average compressed throughput: 51.34 MB/s （2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 186 | mysqlshell | 127.0.0.1:43232 | NULL | Sleep | 4 | | NULL |
| 187 | mysqlshell | 127.0.0.1:43234 | NULL | Query | 3 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 923702 AND 1047780 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 7 */ |
| 188 | mysqlshell | 127.0.0.1:43236 | test | Query | 4 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 386026 AND 551464 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 3 */ |
| 189 | mysqlshell | 127.0.0.1:43238 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1171860 AND 1295938 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 9 */ |
| 190 | mysqlshell | 127.0.0.1:43240 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1047781 AND 1171859 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 8 */ |
| 191 | mysqlshell | 127.0.0.1:43242 | test | Query | 4 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 220587 AND 386025 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 2 */ |
| 192 | mysqlshell | 127.0.0.1:43244 | NULL | Query | 1 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1295939 AND 1420017 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 10 */ |
| 193 | mysqlshell | 127.0.0.1:43246 | NULL | Query | 0 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 1420018 AND 1544096 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 11 */ |
| 194 | mysqlshell | 127.0.0.1:43248 | NULL | Query | 3 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `test`.`sbtest2` WHERE `id` BETWEEN 799623 AND 923701 ORDER BY `id` /* mysqlsh dump_tables, dumping table `test`.`sbtest2`, chunk ID: 6 */ |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)Dump Loading Utility
Dump Loading Utility，配合Instance Dump Utility、Schema Dump Utility、Table Dump Utility使用，支持整个实例、库级别、表级别的多线程导入。
（1）基本语法
MySQL 127.0.0.1:10080 Py > util.load_dump("/applog/output/dumpinstance",{"threads":8})
Loading DDL and Data from '/applog/output/dumpinstance' using 8 threads.
Opening dump...
Target is MySQL 8.0.23. Dump was produced from MySQL 8.0.23
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
[Worker001] Executing DDL script for `test`.`sbtest2`
[Worker000] Executing DDL script for `test`.`sbtest1`
[Worker006] test@sbtest2@1.tsv.zst: Records: 110293 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] test@sbtest2@0.tsv.zst: Records: 110293 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] test@sbtest1@1.tsv.zst: Records: 128675 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] test@sbtest1@3.tsv.zst: Records: 140737 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] test@sbtest1@0.tsv.zst: Records: 147058 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] test@sbtest2@2.tsv.zst: Records: 165439 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] test@sbtest1@2.tsv.zst: Records: 160843 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] test@sbtest2@3.tsv.zst: Records: 165439 Deleted: 0 Skipped: 0 Warnings: 0
[Worker006] test@sbtest2@4.tsv.zst: Records: 124079 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] test@sbtest2@5.tsv.zst: Records: 124079 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] test@sbtest1@4.tsv.zst: Records: 140737 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] test@sbtest2@6.tsv.zst: Records: 124079 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] test@sbtest1@5.tsv.zst: Records: 140737 Deleted: 0 Skipped: 0 Warnings: 0
[Worker007] test@sbtest1@6.tsv.zst: Records: 140737 Deleted: 0 Skipped: 0 Warnings: 0
... 省略 ...
Executing common postamble SQL
151 chunks (20.00M rows, 3.92 GB) for 2 tables in 1 schemas were loaded in 3 min 40 sec (avg throughput 17.81 MB/s)
0 warnings were reported during the load.（2）processlist
mysql> show full processlist;
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 204 | mysqlshell | 127.0.0.1:43580 | test | Query | 4 | executing | /* mysqlsh loadDump(), thread 0, table `test`.`sbtest1`, chunk ID: 5 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest1@5.tsv.zst' REPLACE INTO TABLE `test`.`sbtest1` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 205 | mysqlshell | 127.0.0.1:43582 | test | Query | 2 | executing | /* mysqlsh loadDump(), thread 1, table `test`.`sbtest2`, chunk ID: 7 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest2@7.tsv.zst' REPLACE INTO TABLE `test`.`sbtest2` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 206 | mysqlshell | 127.0.0.1:43584 | NULL | Query | 6 | executing | /* mysqlsh loadDump(), thread 2, table `test`.`sbtest2`, chunk ID: 5 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest2@5.tsv.zst' REPLACE INTO TABLE `test`.`sbtest2` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 207 | mysqlshell | 127.0.0.1:43586 | NULL | Query | 3 | executing | /* mysqlsh loadDump(), thread 5, table `test`.`sbtest2`, chunk ID: 6 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest2@6.tsv.zst' REPLACE INTO TABLE `test`.`sbtest2` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 208 | mysqlshell | 127.0.0.1:43588 | test | Sleep | 12 | | NULL |
| 209 | mysqlshell | 127.0.0.1:43590 | NULL | Query | 4 | executing | /* mysqlsh loadDump(), thread 7, table `test`.`sbtest1`, chunk ID: 6 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest1@6.tsv.zst' REPLACE INTO TABLE `test`.`sbtest1` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 210 | mysqlshell | 127.0.0.1:43592 | NULL | Query | 6 | executing | /* mysqlsh loadDump(), thread 6, table `test`.`sbtest2`, chunk ID: 4 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest2@4.tsv.zst' REPLACE INTO TABLE `test`.`sbtest2` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 211 | mysqlshell | 127.0.0.1:43594 | NULL | Query | 5 | executing | /* mysqlsh loadDump(), thread 4, table `test`.`sbtest1`, chunk ID: 4 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest1@4.tsv.zst' REPLACE INTO TABLE `test`.`sbtest1` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
| 212 | mysqlshell | 127.0.0.1:43596 | NULL | Query | 3 | executing | /* mysqlsh loadDump(), thread 3, table `test`.`sbtest1`, chunk ID: 7 */ LOAD DATA LOCAL INFILE '/applog/output/dumpinstance/test@sbtest1@7.tsv.zst' REPLACE INTO TABLE `test`.`sbtest1` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `k`, `c`, `pad`) |
+-----+-----------------+-----------------+------+---------+--------+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.01 sec)总结
MySQL Shell Utilities相关的逻辑备份恢复工具，很好地解决了mysqldump/mysqlpump单线程速度慢的问题，推荐大家测试使用。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。