MySQL8.0逻辑备份工具综合对比

什么是逻辑备份

逻辑备份是把数据备份成MySQL可以鸡西的格式：SQL语句或者文本文件。

逻辑备份分类

MySQL8.0一共支持四种方式的逻辑备份，接下来，我们挨个学习一下。

mysqldump

mysqldump是MySQL自带的备份工具，原理是将MySQL数据保存成create和insert语句。

典型案例：

1、创建一个全量备份，包括数据、存储过程和事件

mysqldump -uroot --all-databases --routines --events -p > /mysql/backup/mysqldump/all_databases.sql

2、创建一个一致备份集

mysqldump -uroot --all-databases --routines --events --single-transaction -p > /mysql/backup/mysqldump/all_databases.sql

3、恢复一个备份集

mysql -uroot -p < /mysql/backup/mysqldump/all_databases.sql

mysqlpump

mysqlpump是MySQL5.7中推出的一个备份工具，和mysqldump相比，语法兼容，功能更强大。

典型案例：

1、并行备份

mysqlpump --parallel-schemas=test --parallel-schemas=sbtest --default-parallelism=4 --result-file=/mysql/backup/mysqlpump/data_bak -uroot -p
Enter password: 
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 31/57 tables, 641717/3988 rows
Dump progress: 39/57 tables, 1536717/3988 rows
Dump progress: 47/57 tables, 2367217/3988 rows
Dump completed in 4215

2、只备份用户

mysqlpump --exclude-databases=% --users -uroot -p --result-file=/mysql/backup/mysqlpump/users_bak

3、加载数据后创建索引

如果先创建索引，再加载数据，会造成二级索引不断发生页分裂，影响恢复效率。--defer-table-indexes也是默认激活的。

mysqlpump --parallel-schemas=test --parallel-schemas=sbtest --default-parallelism=4 --defer-table-indexes --result-file=/mysql/backup/mysqlpump/data2_bak -uroot -p
Enter password: 
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 31/57 tables, 605467/3988 rows
Dump progress: 39/57 tables, 1488967/3988 rows
Dump progress: 48/57 tables, 2395717/3988 rows
Dump completed in 4193

mydumper

mydumper是一款开源的备份工具，具有以下优点：

并行一致性：并行备份时能保证不同线程之间数据一致性。
输出易于管理：备份输出文件元数据和实际数据是分开的。
使用正则表达式包含和排除数据库对象。

下载地址：

https://github.com/mydumper/mydumper

典型案例：

1、导出sbtest库数据

mydumper -u root -p mysql B sbtest -S /mysql/mysql.sock -o /mysql/backup/mydumper

2、并行一致性备份sbtest库数据

mydumper -u root -p mysql -B sbtest -t 8 --trx-consistency-only -S /mysql/mysql.sock -o /mysql/backup/mydumper

-rw-rw-r-- 1 mysql mysql      136 Sep  2 14:37 metadata
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest10-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest10.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest11-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest11.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest12-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest12.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest13-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest13.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest14-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest14.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest15-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest15.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest16-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest16.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest17-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest17.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest18-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest18.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest19-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest19.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest1-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest1.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest20-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest20.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest21-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest21.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest22-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest22.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest23-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest23.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest24-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest24.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest25-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest25.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest26-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest26.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest27-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest27.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest28-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest28.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest29-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest29.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest2-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest2.sql
-rw-rw-r-- 1 mysql mysql      465 Sep  2 14:37 sbtest.sbtest30-schema.sql
-rw-rw-r-- 1 mysql mysql 19889597 Sep  2 14:37 sbtest.sbtest30.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest3-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest3.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest4-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest4.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest5-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest5.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest6-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest6.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest7-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest7.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest8-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest8.sql
-rw-rw-r-- 1 mysql mysql      463 Sep  2 14:37 sbtest.sbtest9-schema.sql
-rw-rw-r-- 1 mysql mysql 19889577 Sep  2 14:37 sbtest.sbtest9.sql
-rw-rw-r-- 1 mysql mysql      130 Sep  2 14:37 sbtest-schema-create.sql

3、恢复sbtest库数据

myloader -u root -p mysql -B sbtest -t 8 -S /mysql/mysql.sock -d /mysql/backup/mydumper

mysqlshell

MySQL  localhost:33060+ ssl  Py > backup_schema = ["sbtest"];
 MySQL  localhost:33060+ ssl  Py > backup_path='/mysql/backup/mysqlshell';
 MySQL  localhost:33060+ ssl  Py > backup_options = {"consistent":True,"threads":4};
 MySQL  localhost:33060+ ssl  Py > util.dump_schemas(backup_schema,backup_path,backup_options);
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 `sbtest`.`sbtest9`
Data dump for table `sbtest`.`sbtest9` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest8`
Data dump for table `sbtest`.`sbtest8` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest7`
Data dump for table `sbtest`.`sbtest7` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest5`
Data dump for table `sbtest`.`sbtest5` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest4`
Data dump for table `sbtest`.`sbtest4` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest3`
Data dump for table `sbtest`.`sbtest3` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest17`
Data dump for table `sbtest`.`sbtest17` will be chunked using column `id`
.....
.....
.....
Data dump for table `sbtest`.`sbtest26` will be written to 1 file
NOTE: Table statistics not available for `sbtest`.`sbtest2`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `sbtest`.`sbtest2`;' first.
Data dump for table `sbtest`.`sbtest2` will be written to 1 file
NOTE: Table statistics not available for `sbtest`.`sbtest28`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `sbtest`.`sbtest28`;' first.
Data dump for table `sbtest`.`sbtest28` will be written to 1 file
1 thds dumping - ?% (3.00M rows / ?), 480.23K rows/s, 92.15 MB/s uncompressed, 41.89 MB/s compressed  
Duration: 00:00:06s                                                                                 
Schemas dumped: 1                                                                                   
Tables dumped: 30                                                                                   
Uncompressed data size: 575.67 MB                                                                   
Compressed data size: 261.73 MB                                                                     
Compression ratio: 2.2                                                                              
Rows written: 3000000                                                                               
Bytes written: 261.73 MB                                                                            
Average uncompressed throughput: 89.42 MB/s                                                         
Average compressed throughput: 40.65 MB/s

mysql> set global local_infile=ON;
Query OK, 0 rows affected (0.00 sec)

MySQL  localhost:33060+ ssl  Py > util.load_dump("/mysql/backup/mysqlshell");
Loading DDL and Data from '/mysql/backup/mysqlshell' using 4 threads.
Opening dump...
Target is MySQL 8.0.25. Dump was produced from MySQL 8.0.25
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `sbtest`
[Worker001] Executing DDL script for `sbtest`.`sbtest25`
[Worker003] Executing DDL script for `sbtest`.`sbtest28`
[Worker000] Executing DDL script for `sbtest`.`sbtest11`
[Worker002] Executing DDL script for `sbtest`.`sbtest26`
[Worker000] Executing DDL script for `sbtest`.`sbtest29`
[Worker002] Executing DDL script for `sbtest`.`sbtest6`
[Worker003] Executing DDL script for `sbtest`.`sbtest22`
[Worker001] Executing DDL script for `sbtest`.`sbtest21`
[Worker000] Executing DDL script for `sbtest`.`sbtest16`
[Worker002] Executing DDL script for `sbtest`.`sbtest23`
[Worker003] Executing DDL script for `sbtest`.`sbtest17`
[Worker001] Executing DDL script for `sbtest`.`sbtest3`
[Worker000] Executing DDL script for `sbtest`.`sbtest8`
[Worker002] Executing DDL script for `sbtest`.`sbtest4`
[Worker001] Executing DDL script for `sbtest`.`sbtest5`
[Worker003] Executing DDL script for `sbtest`.`sbtest2`
[Worker000] Executing DDL script for `sbtest`.`sbtest9`
[Worker001] Executing DDL script for `sbtest`.`sbtest7`
[Worker002] Executing DDL script for `sbtest`.`sbtest1`
[Worker003] Executing DDL script for `sbtest`.`sbtest14`
[Worker000] Executing DDL script for `sbtest`.`sbtest24`
[Worker002] Executing DDL script for `sbtest`.`sbtest15`
[Worker001] Executing DDL script for `sbtest`.`sbtest13`
[Worker003] Executing DDL script for `sbtest`.`sbtest10`
[Worker000] Executing DDL script for `sbtest`.`sbtest30`
[Worker002] Executing DDL script for `sbtest`.`sbtest12`
[Worker001] Executing DDL script for `sbtest`.`sbtest18`
[Worker003] Executing DDL script for `sbtest`.`sbtest19`
[Worker000] Executing DDL script for `sbtest`.`sbtest20`
[Worker002] Executing DDL script for `sbtest`.`sbtest27`
[Worker001] sbtest@sbtest29@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest3@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest23@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest20@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest15@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest2@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest28@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest7@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest4@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest26@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest19@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest27@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest5@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest17@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest18@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest25@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest13@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest11@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest9@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest8@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest24@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest14@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest21@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest12@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest22@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest6@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sbtest@sbtest1@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sbtest@sbtest30@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sbtest@sbtest10@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sbtest@sbtest16@@0.tsv.zst: Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                               
                                                            
30 chunks (3.00M rows, 575.67 MB) for 30 tables in 1 schemas were loaded in 1 min 0 sec (avg throughput 9.59 MB/s)
0 warnings were reported during the load.

四种工具对比

功能对比

工具	并行导出	大表并行导出	并行导入	默认压缩	支持Innodb表的热备	支持元数据和实际数据分开
mysqldump	N	N	N	N	Y	Y
-	-	-	-	-	-	-
mysqlpump	Y	N	N	N	Y	Y
-	-	-	-	-	-	-
mydumper	Y	Y	Y	N	Y	Y
-	-	-	-	-	-	-
MySQLShell	Y	Y	Y	Y	Y	Y

MySQL8.0逻辑备份工具综合对比

什么是逻辑备份

逻辑备份分类

mysqldump

mysqlpump

mydumper

mysqlshell

四种工具对比

功能对比

性能对比

继续阅读

2022秋招面试总结（cpp+java+测开）百度测开一面字节后端一面虾皮后端一面虾皮后端二面

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件