數據恢復新姿勢：使用MySQL Shell進行更高效靈活的數據恢復

作者：懂點IT的耿小廚 2023-12-19 07:30:58

MySQL Shell的數據恢復操作有很多可選參數便于靈活操作，大家可以自己實操實驗一下。也可以通過修改并發參數來對比其他邏輯備份工具的效率。

上篇文章（轉戰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 <schema> 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 >

可見，以上被刪除的庫已恢復。

四、結語

MySQL Shell的數據恢復操作有很多可選參數便于靈活操作，大家可以自己實操實驗一下。也可以通過修改并發參數來對比其他邏輯備份工具的效率。

責任編輯：姜華來源：數據庫干貨鋪

MySQL 數據恢復數據庫