表空間的資料恢複示範

一，批量删除表空間資料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;