mysql 5.6 使用傳輸資料表空間遷移表或恢複誤刪除的表

標籤：style   blog   http   使用   檔案   資料   for   2014   

mysql 5.6 使用傳輸資料表空間遷移表或恢複誤刪除的表

http://blog.csdn.net/lidan3959/article/details/25152623

以前在網上也看過一些對5.6傳輸資料表空間的介紹，不過都寫的不夠具體詳細，今天有空就具體操作了一把，並詳細記錄了一下算留個檔

 

一，簡單說明：
1），傳輸資料表空間的限制：   1，mysql 版本 5.6.6 及其以上，並且版本建議源和目標版本建議都是GA版並且大版本一樣   2，表引擎為innodb並且開啟獨立資料表空間  innodb_file_per_table   3，處於匯出狀態的表只允許讀也即是”read_only“狀態   4，DISCARD TABLESPACE不支援分區表[5.6.17才支援] 並且當表上具有外鍵關係的時候需要將 foreign_key_check設定為0   5，源和目標執行個體的page size 必須大小一致   6，ALTER TABLE ... IMPORT TABLESPACE  不強制進行外鍵約束檢查，所以需要將主表和子表都匯出，然後在目標執行個體匯入，      另外該操作不需要.cfg 中繼資料檔案，當然如果沒有改檔案在匯入的時候是不做中繼資料檢查的   7，在windows上，innodb引擎內部都是以小寫格式儲存資料庫，表名和資料表空間名，故為了避免匯入因為大小寫出現檔案建議在大小寫敏感， 如linux、unix的作業系統上都以小寫格式建立資料庫和表同時在[mysqld]項下 添加       [mysqld]       lower_case_table_names=1         2），傳輸資料表空間的優點：   1，不需要消耗太多的資源   2，很方便的將表 從一個執行個體遷移到另一個執行個體   3，相對於mysqldump來說你無須匯出然後匯入同時還的維護索引
二，正常的資料表空間遷移      1， 來源資料庫：           use test;           create table lidan(id int) engine=innodb;           insert into lidan values(10);      2，目標資料庫：           use test;           create table lidan(id int) engine=innodb;           insert into lidan values(10);       3，目標資料庫卸載資料表空間：          ALTER TABLE lidan DISCARD TABLESPACE;          卸載資料表空間後lidan.ibd 檔案和資料表空間分離並且會對錶加一個獨佔鎖定，此時如果執行dml則會直接報錯                  4，來源資料執行資料表空間匯出：          use test;          FLUSH TABLES lidan FOR EXPORT;         flush操作會觸發表lidan的dirty page重新整理到磁碟，並且stop purge 線程,同時將中繼資料資訊寫入lidan.cfg                   中繼資料檔案儲存體的資訊如下：                     主要就是一些資料庫和表名、row_id 、復原指標、事物id等          該會話會一直持有表lidan的metadata鎖，故此時其他會話只能進行SELECT二更新和插入操作會被阻塞 。                                 5，將匯出的表（來源資料庫）所在資料檔案目錄下對應的lidan.ibd file和lidan.cfg 拷貝到目標資料庫對應的檔案夾下         cp -p  lidan.cfg lidan.ibd /data/percona-data-3307/test/                    注意：檔案的拷貝必須在下一步釋放metadata lock 之前      6，釋放來源資料庫的metadata lock          use test;          unlock table;         unlock 這一步會刪除之前產生的lidan.cfg檔案，同時釋放metadata lock 並且重新啟動purge 線程      7，目標資料庫將表匯入到資料表空間         alter table lidan import tablespace;          然後查看匯入成功                   匯入的過程mysql內部會做如下事情：         a，檢查資料表空間每個page的一致性         b，更新每個page的space id和lsn的資訊         c，啟用頭部頁標記和更新LSN到頭部頁         d，page 狀態資訊設定為dirty ，這樣page將很快會被重新整理到磁碟        具體                   三，誤刪除情況下的恢複     這裡只討論備庫誤刪除的恢複，如果是主庫誤刪表那隻能通過備份來恢複了      1，首先備庫上執行drop操作，類比誤刪除        (user:root  time: 16:37 port: 3307)[db: test]drop table lidan;      2，備庫恢複必須要有.frm檔案，故這裡需要從源庫查看錶結構然後在目標庫建立         create table lidan(id int) engine=innodb;        接下來的步驟就按照（二）當中從3開始做即可，具體請自行測試。           參考： http://dev.mysql.com/doc/refman/5.6/en/flush.html                                                         http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html