MySQL Transportable Tablespace(傳輸表空間) 使用詳解

神諭丶發表於2017-02-21
原文網址 : http://blog.itpub.net/29773961/viewspace-2134065/
MySql
將大的InnoDB表從一個例項，移動或者複製到另一個例項，有很多的方法，在5.6之前常用的是通過物理或者邏輯備份來實現。
在5.6.6+的版本中，用到了一種基於表空間遷移的快速方法，即類似Oracle TTS。
因為用到，故整理記錄至此。

實驗用到兩臺機器，單機單例項，MySQL 5.6.30。
並將通過vm1> mysql1> vm2> mysql2> 區分兩臺shell環境和mysql client環境。



〇 過程：

① 先在mysql1上建立測試資料：
  1. mysql> \R mysql1>
  2. PROMPT set to 'mysql1> '
  3. mysql1> USE test;
  4. Database changed
  5. mysql1> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
  6. Query OK, 0 rows affected (0.01 sec)

  8. mysql1> INSERT INTO tts(name) VALUES(REPEAT('a',128));
  9. Query OK, 1 row affected (0.00 sec)

  11. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  12. Query OK, 1 row affected (0.00 sec)
  13. Records: 1 Duplicates: 0 Warnings: 0

  15. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  16. Query OK, 2 rows affected (0.00 sec)
  17. Records: 2 Duplicates: 0 Warnings: 0

  19. ………………………………

  21. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  22. Query OK, 131072 rows affected (0.79 sec)
  23. Records: 131072 Duplicates: 0 Warnings: 0

  25. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  26. Query OK, 262144 rows affected (2.15 sec)
  27. Records: 262144 Duplicates: 0 Warnings: 0

  29. mysql1> \! du -sh /data/mysql/test/tts*
  30. 12K /data/mysql/test/tts.frm
  31. 92M /data/mysql/test/tts.ibd

② 再保證mysql2上有相同的庫表結構，此處為新建，並將mysql2上新建的test.tts表discard掉ibd檔案：
  1. mysql> \R mysql2>
  2. PROMPT set to 'mysql2> '
  3. mysql2> USE test;
  4. Database changed
  5. mysql2> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
  6. Query OK, 0 rows affected (0.01 sec)

  8. mysql2> \! du -sh /data/mysql/test/tts*
  9. 12K /data/mysql/test/tts.frm
  10. 96K /data/mysql/test/tts.ibd
  11. 注意！該alter table ... discard tablespace操作會記錄binlog並影響複製結構，慎用，或set sql_log_bin=0;
  12. mysql2> ALTER TABLE tts DISCARD TABLESPACE;
  13. Query OK, 0 rows affected (0.01 sec)

  15. mysql2> \! du -sh /data/mysql/test/tts*
  16. 12K /data/mysql/test/tts.frm

③ 對mysql1的test.tts表做FLUSH TABLES操作，此時會多了一個cfg檔案：
  1. mysql1> FLUSH TABLE tts FOR EXPORT;
  2. Query OK, 0 rows affected (0.05 sec)

  4. mysql1> \! du -sh /data/mysql/test/tts*
  5. 4.0K /data/mysql/test/tts.cfg
  6. 12K /data/mysql/test/tts.frm
  7. 92M /data/mysql/test/tts.ibd

④ 開多一個終端，在vm1上將ibd和cfg檔案scp到vm2上：
  1. vm1> scp /data/mysql/test/tts.{ibd,cfg} user@vm2:/data/mysql/test
  2. user@vm2's password: 
  3. tts.ibd 100%   92MB  46.0MB/s   00:02    
  4. tts.cfg 100%  380     0.4KB/s   00:00  

⑤ 將mysql1的test.tts表做UNLOCK操作（此時可發現cfg檔案已被刪除）：
  1. mysql1> UNLOCK TABLES;
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql1> \! du -sh /data/mysql/test/tts*
  5. 12K /data/mysql/test/tts.frm
  6. 92M /data/mysql/test/tts.ibd

⑥ 在vm2上將傳過來的ibd和cfg檔案修改許可權：
  1. vm2> chown mysql:mysql /data/mysql/test/tts.{ibd,cfg}

⑦ 將上述ibd檔案IMPORT到tts表中：
  1. mysql2> ALTER TABLE tts IMPORT TABLESPACE;
  2. Query OK, 0 rows affected (0.93 sec)

  4. mysql2> SELECT count(*) FROM tts;
  5. +----------+
  6. | count(*) |
  7. +----------+
  8. | 524288   |
  9. +----------+
  10. 1 row in set (0.94 sec)

至此，已經將mysql1例項上的tts表中資料快速地遷移到mysql2例項上了。


〇 上述幾個步驟的解釋：

操作②中的discard tablespace會在表上加上MDL鎖，刪除change buffer所有相關的快取項，設定表後設資料資訊，標誌tablespace為刪除狀態，重新生成表的id，保證基於表id的操作後續均會失敗，再將idb檔案幹掉，在②中的兩次du可以看到.idb檔案已經被刪除了。這是一個十分危險的操作，慎重；此操作也會被記錄到binlog中，若在複製結構可能會有很大的影響，切記先臨時關閉binlog。

操作③中的flush table ... for export會給test.tts表加上共享鎖，並將purge coordinator thread（在並行複製中類似sql thread）停止，並且將髒頁強制同步到磁碟，建立並將test.tts表的後設資料寫入.cfg檔案；
FLUSH TABLES ... FOR EXPORT在error log中體現了這個過程：
[Note] InnoDB: Sync to disk of '"test"."tts"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/tts.cfg'
[Note] InnoDB: Table '"test"."tts"' flushed to disk

操作⑤執行unlock tables將③中的鎖解除，此時.cfg檔案被刪掉，purge coordinator thread也會重新啟動；（在做flush table ... for export時不能關閉session，避免鎖釋放造成.cfg檔案刪除）
UNLOCK TABLES在error log中記錄為：
[Note] InnoDB: Deleting the meta-data file './test/tts.cfg'
[Note] InnoDB: Resuming purge

操作⑦則是通過import tablespace操作，將從vm1上傳輸過來的.ibd檔案和匯入到tts表中，此時.cfg檔案也必須存在；
ALTER TABLE ... IMPORT TABLESPACE在error log中記錄為：
[Note] InnoDB: Importing tablespace for table 'test/tts' that was exported from host 'vm01'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete
[Note] InnoDB: "test"."tts" autoinc value set to 786406
過程為讀取cfg檔案：表定義，索引定義，索引RootPage，列定義等等。再讀取import檔案每一個page，檢查完整性，根據讀取到的cfg檔案，重新設定當前表的後設資料資訊。


總結一下整個過程就是：
create table $new_table ...
alter table $new_table discard tablespace;（刪除新表的tablespace檔案，保留frm檔案）
flush table $old_table for export;（關閉該表，並且生成cfg檔案）
拷貝ibd檔案，已經對應的cfg檔案。
unlock tables;
將ibd檔案和cfg檔案copy到新地址，修改好許可權
alter table $new_table import tablespace;


〇 限制：
兩個例項都必須開啟獨立表空間，innodb_file_per_table
遷移的兩個例項的innodb_page_size必須一致，並且mysql server版本建議一致
不支援在分割槽表上執行discard tablespace
不支援在有主外來鍵關係的表上執行discard tablespace，除非設定foregin_key_checks=0


〇 參考文件：
 MySQL 5.6 Reference Manual - 14.5.5 Copying Tablespaces to Another Server (Transportable Tablespaces)




作者微信公眾號（持續更新）



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2134065/，如需轉載，請註明出處，否則將追究法律責任。

相關文章