数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

吾爱主题阅读：204 2023-12-19 15:06:00 评论：0

上篇文章（转战MySQL Shell！数据库备份新姿势，轻松搞定备份操作！）简单介绍了使用MySQL Shell进行数据库备份，本文基于上文的备份进行数据恢复演示操作。

一、恢复单表

因为上次备份的表是testdb1.test1表，如果恢复到当前库，则可以先删除该库中的表，再恢复。

1、先删除库里的表

# mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock
Please 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.35
Copyright (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 83
Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5
No default schema selected; type \use 
 
   to set one.
 MySQL  localhost  JS > \sql
Switching 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 >

2、修改参数

进行数据恢复时local_infile参数需要修改为on，因此需先调整参数，否则将会报错，例如：

MySQL  localhost  testdb1  SQL > \js
Switching 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 > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost  testdb1  SQL > set global local_infile=on;
Query OK, 0 rows affected (0.0002 sec)

3、进行单表恢复

上面已经删除了原库里的表，现在通过备份进行恢复。

MySQL  localhost  testdb1  SQL > \js
Switching 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-25
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
Executing common postamble SQL                       
100% (157 bytes / 157 bytes), 0.00 B/s, 1 / 1 tables done
Recreating 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"})

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-25
NOTE: 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 SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
100% (157 bytes / 157 bytes), 0.00 B/s, 0 / 1 tables done
Recreating 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 > \sql
Switching 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文件内容会增加一批：

二、恢复单个schema

因为恢复至和备份库名一致的库需删除原库，因此就不演示该操作，而采用恢复至其他库的方式演示。

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、进行数据恢复

从上期备份的schema中恢复至rec1库，添加schema参数即可。

具体步骤如下:

MySQL  localhost  rec1  SQL > \js
Switching 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-25
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
1 thds loading | 100% (14.06 MB / 14.06 MB), 27.90 MB/s, 11 / 11 tables done
Executing 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 >

3、查看恢复结果

可见，表已恢复至rec1库中。

执行恢复操作时，对应的备份目录也生成了load-progress.*.json文件，内容如下：

三、从整库备份中恢复

1、恢复整个实例的库

恢复整库时，如果目标库已经存在，则需要先删除（同恢复单表或单库类似），之后才可以进行恢复，否则会报库及表已经存在的错误。本文为了演示，先删除之前的库，生产环境千万别删库！！！

MySQL  localhost  rec1  JS > \sql
Switching 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 > \js
Switching 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-25
NOTE: 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 SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
1 thds loading | 100% (14.06 MB / 14.06 MB), 8.39 MB/s, 12 / 12 tables done
Executing 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 > \sql
Switching 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 >