上篇文章（转战MySQL Shell！数据库备份新姿势，轻松搞定备份操作！）简单介绍了使用MySQL Shell进行数据库备份，本文基于上文的备份进行数据恢复演示操作。
1. 恢复单表
因为上次备份的表是testdb1.test1表，如果恢复到当前库，则可以先删除该库中的表，再恢复。
1.1 先删除库里的表
# mysqlsh -u root -p -S data/mysql/mysql3306/tmp/mysql.sockPlease provide the password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock': *********Save password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.35Copyright (c) 2016, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its affiliates.Other names may be trademarks of their respective owners.Type 'help' or '?' for help; 'quit' to exit.Creating a session to 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'Fetching schema names for auto-completion... Press ^C to stop.Your MySQL connection id is 83Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5No default schema selected; type use to set one. MySQL localhost JS > sqlSwitching to SQL mode... Commands end with ;Fetching global names for auto-completion... Press ^C to stop. MySQL localhost SQL > use testdb1;Default schema set to `testdb1`.Fetching global names, object names from `testdb1` for auto-completion... Press ^C to stop. MySQL localhost testdb1 SQL > show tables;+-------------------+| Tables_in_testdb1 |+-------------------+| test1 |+-------------------+1 row in set (0.0010 sec) MySQL localhost testdb1 SQL > drop table test1;Query OK, 0 rows affected (0.0518 sec) MySQL localhost testdb1 SQL >
1.2 修改参数
进行数据恢复时local_infile参数需要修改为on，因此需先调整参数，否则将会报错，例如：
MySQL localhost testdb1 SQL > jsSwitching to JavaScript mode... MySQL localhost testdb1 JS > util.loadDump('/data/backup/backup_tables');ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.Util.loadDump: local_infile disabled in server (MYSQLSH 53025) MySQL localhost testdb1 JS > sqlSwitching to SQL mode... Commands end with ; MySQL localhost testdb1 SQL > set global local_infile=on;Query OK, 0 rows affected (0.0002 sec)
1.3 进行单表恢复
上面已经删除了原库里的表，现在通过备份进行恢复。
MySQL localhost testdb1 SQL > jsSwitching to JavaScript mode... MySQL localhost testdb1 JS > util.loadDump('/data/backup/backup_tables');Loading DDL and Data from '/data/backup/backup_tables' using 4 threads.Opening dump...Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.33-25Scanning metadata - done Checking for pre-existing objects...Executing common preamble SQLExecuting DDL - done Executing view DDL - done Starting data loadExecuting common postamble SQL 100% (157 bytes 157 bytes), 0.00 B/s, 1 1 tables doneRecreating indexes - done 1 chunks (8 rows, 157 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 157.00 B/s)0 warnings were reported during the load. MySQL localhost testdb1 JS >
查看恢复结果：表及数据已恢复
恢复过程中，对应的备份路径下会生成一个load-progress.*.json文件，该文件记录了恢复进度及结果，以便于断点续处理，文件存储的具体内容如下：
注：如果需多次进行相同操作时，注意修改改文件内容或删除该文件，或者自定义一个文件，例如
util.loadDump("PrefixPARURL", progressFile: "progress.json"})
1.4 恢复至其他库
很多实际情况下的数据恢复是为了将备份中的一部分数据恢复至目标表，或进行数据对比，因此不能将已存在的表删除。那么建议创建一个临时恢复用的库或在其他实例上创建新库进行恢复。那么，恢复至其他库（库名不一样），该如何操作呢？具体操作如下：
先创建一个空库：
MySQL localhost testdb1 SQL > create database rec;Query OK, 1 row affected (0.0220 sec) MySQL localhost testdb1 SQL > use rec;Default schema set to `rec`.Fetching global names, object names from `rec` for auto-completion... Press ^C to stop. MySQL localhost rec SQL > show tables;Empty set (0.0011 sec) MySQL localhost rec SQL >
恢复rec库并查看结果
MySQL localhost rec JS > util.loadDump('/data/backup/backup_tables',{"schema":"rec"});Loading DDL and Data from '/data/backup/backup_tables' using 4 threads.Opening dump...Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.33-25NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.Scanning metadata - done Executing common preamble SQLExecuting DDL - done Executing view DDL - done Starting data load100% (157 bytes 157 bytes), 0.00 B/s, 0 1 tables doneRecreating indexes - done Executing common postamble SQL 1 chunks (8 rows, 314 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 157.00 B/s)0 warnings were reported during the load. MySQL localhost rec JS > sqlSwitching to SQL mode... Commands end with ; MySQL localhost rec SQL > use rec;Default schema set to `rec`.Fetching global names, object names from `rec` for auto-completion... Press ^C to stop. MySQL localhost rec SQL > show tables;+---------------+| Tables_in_rec |+---------------+| test1 |+---------------+1 row in set (0.0012 sec) MySQL localhost rec SQL > select count(*) from test1;+----------+| count(*) |+----------+| 8 |+----------+1 row in set (0.0033 sec) MySQL localhost rec SQL >
数据已恢复，且数据条数一致。
此时的备份文件目录里的load-progress文件内容会增加一批：
2. 恢复单个schema
因为恢复至和备份库名一致的库需删除原库，因此就不演示该操作，而采用恢复至其他库的方式演示。
2.1 创建一个新库
MySQL localhost rec SQL > use rec1;Default schema set to `rec1`.Fetching global names, object names from `rec1` for auto-completion... Press ^C to stop. MySQL localhost rec1 SQL > show tables;Empty set (0.0011 sec) MySQL localhost rec1 SQL >
2.2 进行数据恢复
从上期备份的schema中恢复至rec1库，添加schema参数即可
具体步骤如下:
MySQL localhost rec1 SQL > jsSwitching to JavaScript mode... MySQL localhost rec1 JS > util.loadDump('/data/backup/backup_schemas',{"schema":"rec1"})Loading DDL and Data from '/data/backup/backup_schemas' using 4 threads.Opening dump...Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.33-25Scanning metadata - done Checking for pre-existing objects...Executing common preamble SQLExecuting DDL - done Executing view DDL - done Starting data load1 thds loading | 100% (14.06 MB 14.06 MB), 27.90 MB/s, 11 11 tables doneExecuting common postamble SQL Recreating indexes - done 12 chunks (862 rows, 14.06 MB) for 11 tables in 1 schemas were loaded in 1 sec (avg throughput 14.06 MB/s)0 warnings were reported during the load. MySQL localhost rec1 JS >
2.3 查看恢复结果
可见，表已恢复至rec1库中
执行恢复操作时，对应的备份目录也生成了load-progress.*.json文件，内容如下：
3. 从整库备份中恢复
3.1 恢复整个实例的库
恢复整库时，如果目标库已经存在，则需要先删除（同恢复单表或单库类似），之后才可以进行恢复，否则会报库及表已经存在的错误。本文为了演示，先删除之前的库，生产环境千万别删库！！！
MySQL localhost rec1 JS > sqlSwitching to SQL mode... Commands end with ; MySQL localhost rec1 SQL > drop database testdb;Query OK, 11 rows affected (0.2389 sec) MySQL localhost rec1 SQL > drop database testdb1;Query OK, 1 row affected (0.0276 sec) MySQL localhost rec1 SQL > jsSwitching to JavaScript mode... MySQL localhost rec1 JS > util.loadDump('/data/backup');Loading DDL and Data from '/data/backup' using 4 threads.Opening dump...Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.33-25NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.Scanning metadata - done Executing common preamble SQLExecuting DDL - done Executing view DDL - done Starting data load1 thds loading | 100% (14.06 MB 14.06 MB), 8.39 MB/s, 12 / 12 tables doneExecuting common postamble SQL Recreating indexes - done 13 chunks (870 rows, 14.06 MB) for 12 tables in 2 schemas were loaded in 1 sec (avg throughput 14.06 MB/s)0 warnings were reported during the load. MySQL localhost rec1 JS > sqlSwitching to SQL mode... Commands end with ; MySQL localhost rec1 SQL > show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || rec || rec1 || sys || testdb || testdb1 |+--------------------+8 rows in set (0.0009 sec) MySQL localhost rec1 SQL >
可见，以上被删除的库已恢复。
4. 结语
MySQL Shell的数据恢复操作有很多可选参数便于灵活操作，大家可以自己实操实验一下。也可以通过修改并发参数来对比其他逻辑备份工具的效率。
往期精彩回顾
1. MySQL高可用之MHA集群部署
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
7. MySQL敏感数据加密及解密
8. MySQL数据备份及还原（一）
9. MySQL数据备份及还原（二）
扫码关注 
