江苏龙网

  1. 主页 > 生活百科 > >

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

数据恢复


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

文章插图
恢复rec库并查看结果：
MySQLlocalhostrecJS > 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 - doneExecuting common preamble SQLExecuting DDL - doneExecuting view DDL - doneStarting data load100% (157 bytes / 157 bytes), 0.00 B/s, 0 / 1 tables doneRecreating indexes - doneExecuting common postamble SQL1 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.MySQLlocalhostrecJS > sqlSwitching to SQL mode... Commands end with ; MySQLlocalhostrecSQL > use rec;Default schema set to `rec`.Fetching global names, object names from `rec` for auto-completion... Press ^C to stop. MySQLlocalhostrecSQL > show tables;+---------------+| Tables_in_rec |+---------------+| test1|+---------------+1 row in set (0.0012 sec) MySQLlocalhostrecSQL > selectcount(*) from test1;+----------+| count(*) |+----------+|8 |+----------+1 row in set (0.0033 sec) MySQLlocalhostrecSQL >
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
数据已恢复，且数据条数一致 。
此时的备份文件目录里的load-progress文件内容会增加一批：
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
二、恢复单个schema因为恢复至和备份库名一致的库需删除原库，因此就不演示该操作，而采用恢复至其他库的方式演示 。
1、创建一个新库MySQLlocalhostrecSQL > use rec1;Default schema set to `rec1`.Fetching global names, object names from `rec1` for auto-completion... Press ^C to stop. MySQLlocalhostrec1SQL > show tables;Empty set (0.0011 sec) MySQLlocalhostrec1SQL >
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
2、进行数据恢复从上期备份的schema中恢复至rec1库 ， 添加schema参数即可 。
具体步骤如下:
MySQLlocalhostrec1SQL > jsSwitching to JavaScript mode... MySQLlocalhostrec1JS > 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 - doneChecking for pre-existing objects...Executing common preamble SQLExecuting DDL - doneExecuting view DDL - doneStarting data load1 thds loading | 100% (14.06 MB / 14.06 MB), 27.90 MB/s, 11 / 11 tables doneExecuting common postamble SQLRecreating indexes - done12 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.MySQLlocalhostrec1JS >
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
3、查看恢复结果可见，表已恢复至rec1库中 。
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
执行恢复操作时，对应的备份目录也生成了load-progress.*.json文件，内容如下：
数据恢复新姿势：使用MySQL Shell进行更高效灵活的数据恢复

文章插图
三、从整库备份中恢复1、恢复整个实例的库恢复整库时，如果目标库已经存在 ， 则需要先删除（同恢复单表或单库类似），之后才可以进行恢复，否则会报库及表已经存在的错误 。本文为了演示，先删除之前的库，生产环境千万别删库?。。?
MySQLlocalhostrec1JS > sqlSwitching to SQL mode... Commands end with ; MySQLlocalhostrec1SQL > drop database testdb;Query OK, 11 rows affected (0.2389 sec) MySQLlocalhostrec1SQL > drop database testdb1;Query OK, 1 row affected (0.0276 sec) MySQLlocalhostrec1SQL > jsSwitching to JavaScript mode... MySQLlocalhostrec1JS > 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 - doneExecuting common preamble SQLExecuting DDL - doneExecuting view DDL - doneStarting data load1 thds loading | 100% (14.06 MB / 14.06 MB), 8.39 MB/s, 12 / 12 tables doneExecuting common postamble SQLRecreating indexes - done13 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.MySQLlocalhostrec1JS > sqlSwitching to SQL mode... Commands end with ; MySQLlocalhostrec1SQL > show databases;+--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || rec|| rec1|| sys|| testdb|| testdb1|+--------------------+8 rows in set (0.0009 sec) MySQLlocalhostrec1SQL >


推荐阅读


上一篇：关于Android图像Bitmap类，你要知道的一切

下一篇：一口气看完 43 个关于 ElasticSearch 的使用建议