表空间的数据恢复演示

一，批量删除表空间数据idb文件：

1使用navicat导入world.sql数据库，并备份数据库

[root@instance-r5y0pf5d ~]# cp -r /data/mysql/data/world /data/mysql/data/world.bak      

2批量生成删除表空间语句：

mysql> select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';
Query OK, 3 rows affected (0.34 sec)      

3设置跳过外键语句检查：

mysql> set foreign_key_checks=0;                                                                                                 
Query OK, 0 rows affected (0.19 sec)      

4读取生成的命令行文件

mysql> source /tmp/discard.sql
Query OK, 0 rows affected, 2 warnings (0.25 sec)      

5批量执行删除表空间语句（虽然报错，但是也将ibd文件删除了）

[root@instance-r5y0pf5d /tmp]# mysql -uroot -p123456 </tmp/discard.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1451 (23000) at line 2: Cannot delete or update a parent row: a foreign key constraint fails ()      

6查看是否已经批量删除了world里的idb文件（表空间）

[root@instance-r5y0pf5d /data/mysql/data/world]# ll
total 40
-rw-r----- 1 mysql mysql 8710 Jan 21 15:44 city.frm
-rw-r----- 1 mysql mysql 9172 Jan 21 15:44 country.frm
-rw-r----- 1 mysql mysql 8702 Jan 21 15:44 countrylanguage.frm
-rw-r----- 1 mysql mysql   65 Jan 21 15:44 db.opt      

 二，恢复表空间数据idb文件：

1将备份目录中的表空间文件拷贝至world目录中

[root@instance-r5y0pf5d /data/mysql/data/world.bak]# cp -a *.ibd ../world      

2授权用户mysql

[root@instance-r5y0pf5d /data/mysql/data/world.bak]# chown -R mysql. /data/*      

3进入mysql生成import拼接语句

mysql> select concat("alter table ",table_schema,".",table_name," import tablespace;") from information_schema.tables where table__schema='world' into outfile '/tmp/import.sql';      

4在mysql中读取生成的文件：

mysql> source /tmp/import.sql      

5在mysql中查看ibd表空间文件是否已经恢复

mysql> select * from city;