數(shù)據(jù)恢復(fù)新姿勢(shì)：使用MySQL Shell進(jìn)行更高效靈活的數(shù)據(jù)恢復(fù)

作者：懂點(diǎn)IT的耿小廚 2023-12-19 07:30:58

MySQL Shell的數(shù)據(jù)恢復(fù)操作有很多可選參數(shù)便于靈活操作，大家可以自己實(shí)操實(shí)驗(yàn)一下。也可以通過修改并發(fā)參數(shù)來對(duì)比其他邏輯備份工具的效率。

上篇文章（轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫備份新姿勢(shì)，輕松搞定備份操作！）簡(jiǎn)單介紹了使用MySQL Shell進(jìn)行數(shù)據(jù)庫備份，本文基于上文的備份進(jìn)行數(shù)據(jù)恢復(fù)演示操作。

一、恢復(fù)單表

因?yàn)樯洗蝹浞莸谋硎莟estdb1.test1表，如果恢復(fù)到當(dāng)前庫，則可以先刪除該庫中的表，再恢復(fù)。

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、修改參數(shù)

進(jìn)行數(shù)據(jù)恢復(fù)時(shí)local_infile參數(shù)需要修改為on，因此需先調(diào)整參數(shù)，否則將會(huì)報(bào)錯(cuò)，例如：

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、進(jìn)行單表恢復(fù)

上面已經(jīng)刪除了原庫里的表，現(xiàn)在通過備份進(jìn)行恢復(fù)。

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 >

查看恢復(fù)結(jié)果：表及數(shù)據(jù)已恢復(fù)。

恢復(fù)過程中，對(duì)應(yīng)的備份路徑下會(huì)生成一個(gè)load-progress.*.json文件，該文件記錄了恢復(fù)進(jìn)度及結(jié)果，以便于斷點(diǎn)續(xù)處理，文件存儲(chǔ)的具體內(nèi)容如下：

注：如果需多次進(jìn)行相同操作時(shí)，注意修改改文件內(nèi)容或刪除該文件，或者自定義一個(gè)文件，例如：

util.loadDump("PrefixPARURL", progressFile: "progress.json"})

4、恢復(fù)至其他庫

很多實(shí)際情況下的數(shù)據(jù)恢復(fù)是為了將備份中的一部分?jǐn)?shù)據(jù)恢復(fù)至目標(biāo)表，或進(jìn)行數(shù)據(jù)對(duì)比，因此不能將已存在的表刪除。那么建議創(chuàng)建一個(gè)臨時(shí)恢復(fù)用的庫或在其他實(shí)例上創(chuàng)建新庫進(jìn)行恢復(fù)。那么，恢復(fù)至其他庫（庫名不一樣），該如何操作呢？具體操作如下：

先創(chuàng)建一個(gè)空庫：

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 >

恢復(fù)rec庫并查看結(jié)果：

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 >

數(shù)據(jù)已恢復(fù)，且數(shù)據(jù)條數(shù)一致。

此時(shí)的備份文件目錄里的load-progress文件內(nèi)容會(huì)增加一批：

二、恢復(fù)單個(gè)schema

因?yàn)榛謴?fù)至和備份庫名一致的庫需刪除原庫，因此就不演示該操作，而采用恢復(fù)至其他庫的方式演示。

1、創(chuàng)建一個(gè)新庫

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、進(jìn)行數(shù)據(jù)恢復(fù)

從上期備份的schema中恢復(fù)至rec1庫，添加schema參數(shù)即可。

具體步驟如下:

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、查看恢復(fù)結(jié)果

可見，表已恢復(fù)至rec1庫中。

執(zhí)行恢復(fù)操作時(shí)，對(duì)應(yīng)的備份目錄也生成了load-progress.*.json文件，內(nèi)容如下：

三、從整庫備份中恢復(fù)

1、恢復(fù)整個(gè)實(shí)例的庫

恢復(fù)整庫時(shí)，如果目標(biāo)庫已經(jīng)存在，則需要先刪除（同恢復(fù)單表或單庫類似），之后才可以進(jìn)行恢復(fù)，否則會(huì)報(bào)庫及表已經(jīng)存在的錯(cuò)誤。本文為了演示，先刪除之前的庫，生產(chǎn)環(huán)境千萬別刪庫！！！

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 >