教你透過物理方式使MySQL恢復單表

這篇文章為大家帶來了關於MySQL的相關知識，其中主要介紹了怎麼透過物理方式來讓MySQL能夠快速的恢復單表，一步步教你！下面一起來看一下，希望對大家有幫助。

使用方法

#1、先建立一個測試表test1，並插入幾個資料：

mysql> create table test1 (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test1 (name) values ('张三'),('李四'),('王二');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

2、建立目標表test2：

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.10 sec)
查看数据目录里面的ibd文件（test2.ibd、test1.ibd）：
-rw-r-----. 1 * * 114688 Nov  2 16:20 test1.ibd
-rw-r-----. 1 * * 114688 Nov  2 16:23 test2.ibd

3、透過alter table discard的方法丟棄表格test2的idb檔案（為下一步複製test1的資料過來做準備）：

mysql> alter table test2 discard tablespace;   
Query OK, 0 rows affected (0.02 sec)

查看ibd文件情况，发现test2的ibd文件已经被删除

-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

4、執行下面的指令，產生一個test1的cfg文件，如下：

mysql> flush table test1 for export; 
Query OK, 0 rows affected (0.00 sec)

生成了一个test1.cfg的cfg文件

-rw-r----- 1 * *    655 Nov  2 16:25 test1.cfg
-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

5、拷貝來源表test1的cfg檔案和ibd檔案到目標表test2，並修改檔案權限：

cp test1.cfg test2.cfg
cp test1.ibd test2.ibd
chown -R mysql.mysql test2.*

6、複製完成之後，執行select指令發現出現以下報錯：

mysql> select * from test2;
ERROR 1100 (HY000): Table 'test2' was not locked with LOCK TABLES

7、執行unlock tables，釋放來源表的test1.cfg文件，然後導入ibd檔：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

并用alter table的方法为目标表test2导入这个ibd文件：

mysql> alter table test2 import tablespace; 
Query OK, 0 rows affected (0.03 sec)
1 row in set (0.00 sec)

8、再執行select，發現資料已經導入：

mysql> select * from test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

物理複製方法介紹

上述單表物理複製的方法，核心在於cp指令，因為是透過物理拷貝，所以如果複製的表非常大，那麼透過物理拷貝，就會比邏輯上的SQL寫入快很多，例如insert into select語句。

简单总结一下上述物理复制过程：

• 1、create table like語法建立一個相同表格結構的空的目標表
• 2、目標表執行alter table discard，丟棄ibd檔案
• # 3.來源表執行alter table for export語法，產生.cfg文件，並鎖定表
• #4、使用cp指令複製來源表cfg檔和ibd檔為目標表
• 5、unlock tables 釋放來源表的cfg檔案和鎖定
• 6、alter table import指令導入目標表的ibd資料檔。

alter table for export語法介紹：

• 1、這個指令是為了將記憶體中關於這個表的資料刷新到磁碟上，確保資料都能被binlog所記錄；
• 2、這個操作需要flush table或reload權限；
• 3、這個操作會持有目前表的共用MDL鎖，並阻止其他會話修改表結構，在FOR EXPORT操作完成時不會釋放先前取得的MDL鎖定，需要手動釋放
• 4、InnoDB會在與該表相同的資料庫目錄中產生一個名為table_name.cfg的檔案
• 5 、處理完表複製後，需要使用UNLOCK tables釋放來源表的MDL鎖定或斷開連線。

注意：

因為alter table for export鎖定表，所以這個方法比較適合在從函式庫上停掉複製關係，然後執行這個表複製的動作。如果有業務操作目前的來源表，需要謹慎考慮。

推薦學習：《MySQL影片教學》

以上是教你透過物理方式使MySQL恢復單表的詳細內容。更多資訊請關注PHP中文網其他相關文章！