數(shù)據(jù)恢復(fù)新姿勢：使用MySQL Shell進(jìn)行更高效靈活的數(shù)據(jù)恢復(fù)
作者：懂點(diǎn)IT的耿小廚
MySQL Shell的數(shù)據(jù)恢復(fù)操作有很多可選參數(shù)便于靈活操作，大家可以自己實操實驗一下。也可以通過修改并發(fā)參數(shù)來對比其他邏輯備份工具的效率。
上篇文章（轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫備份新姿勢，輕松搞定備份操作！）簡單介紹了使用MySQL Shell進(jìn)行數(shù)據(jù)庫備份，本文基于上文的備份進(jìn)行數(shù)據(jù)恢復(fù)演示操作。
一、恢復(fù)單表
因為上次備份的表是testdb1.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ù)時local_infile參數(shù)需要修改為on，因此需先調(diào)整參數(shù)，否則將會報錯，例如：
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ù)過程中，對應(yīng)的備份路徑下會生成一個load-progress.*.json文件，該文件記錄了恢復(fù)進(jìn)度及結(jié)果，以便于斷點(diǎn)續(xù)處理，文件存儲的具體內(nèi)容如下：
注：如果需多次進(jìn)行相同操作時，注意修改改文件內(nèi)容或刪除該文件，或者自定義一個文件，例如：
util.loadDump("PrefixPARURL", progressFile: "progress.json"})4、恢復(fù)至其他庫
很多實際情況下的數(shù)據(jù)恢復(fù)是為了將備份中的一部分?jǐn)?shù)據(jù)恢復(fù)至目標(biāo)表，或進(jìn)行數(shù)據(jù)對比，因此不能將已存在的表刪除。那么建議創(chuàng)建一個臨時恢復(fù)用的庫或在其他實例上創(chuàng)建新庫進(jìn)行恢復(fù)。那么，恢復(fù)至其他庫（庫名不一樣），該如何操作呢？具體操作如下：
先創(chuàng)建一個空庫：
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ù)一致。
此時的備份文件目錄里的load-progress文件內(nèi)容會增加一批：
二、恢復(fù)單個schema
因為恢復(fù)至和備份庫名一致的庫需刪除原庫，因此就不演示該操作，而采用恢復(fù)至其他庫的方式演示。
1、創(chuàng)建一個新庫
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ù)操作時，對應(yīng)的備份目錄也生成了load-progress.*.json文件，內(nèi)容如下：
三、從整庫備份中恢復(fù)
1、恢復(fù)整個實例的庫
恢復(fù)整庫時，如果目標(biāo)庫已經(jīng)存在，則需要先刪除（同恢復(fù)單表或單庫類似），之后才可以進(jìn)行恢復(fù)，否則會報庫及表已經(jīng)存在的錯誤。本文為了演示，先刪除之前的庫，生產(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 >
可見，以上被刪除的庫已恢復(fù)。
四、結(jié)語
MySQL Shell的數(shù)據(jù)恢復(fù)操作有很多可選參數(shù)便于靈活操作，大家可以自己實操實驗一下。也可以通過修改并發(fā)參數(shù)來對比其他邏輯備份工具的效率。
責(zé)任編輯：姜華
來源：
數(shù)據(jù)庫干貨鋪
