学堂 学堂 学堂公众号手机端
首页 技术正文

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

lewis 5年前 (2020-01-29) 阅读数 5 #技术

什么是逻辑备份

逻辑备份是把数据备份成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

mysqlshell是MySQL8.0中增加的备份恢复工具。
典型案例：
1、导出sbtest库

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

2、导入sbtest库

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 性能对比

1、备份速度，由快到慢
MySQL Shell
mydumper
mysqlpump
mysqldump
2、备份集大小
MySQL Shell < mydumper = mysqlpump = mysqldump

版权声明

本文仅代表作者观点，不代表博信信息网立场。

上一篇：数据从哪里来？构建数据库的源头活水 下一篇：MySQL参数优化
相关文章
lewis

lewis

作者文章
浏览更多
热门
最新文章
标签列表
Copyright boxinxx.com.Some Rights Reserved.