將大的InnoDB表從一個(gè)實(shí)例，移動(dòng)或者復(fù)制到另一個(gè)實(shí)例，有很多的方法，在5.6之前常用的是通過(guò)物理或者邏輯備份來(lái)實(shí)現(xiàn)。
在5.6.6+的版本中，用到了一種基于表空間遷移的快速方法，即類似OracleTTS。
因?yàn)橛玫?，故整理記錄至此?br />
實(shí)驗(yàn)用到兩臺(tái)機(jī)器，單機(jī)單實(shí)例，MySQL 5.6.30。
并將通過(guò)vm1> mysql1>vm2> mysql2> 區(qū)分兩臺(tái)shell環(huán)境和mysql client環(huán)境。
〇 過(guò)程：
① 先在mysql1上創(chuàng)建測(cè)試數(shù)據(jù)：
-
mysql> \R mysql1>
-
PROMPTset to 'mysql1> '
-
mysql1> USE test;
-
Database changed
-
mysql1> CREATE TABLE tts(idint PRIMARY KEYAUTO_INCREMENT, namechar(128));
-
Query OK, 0 rows affected(0.01 sec)
-
-
mysql1> INSERT INTO tts(name) VALUES(REPEAT('a',128));
-
Query OK, 1 row affected(0.00 sec)
-
-
mysql1> INSERT INTO tts(name) SELECT nameFROM tts;
-
Query OK, 1 row affected(0.00 sec)
-
Records: 1 Duplicates: 0 Warnings: 0
-
-
mysql1> INSERT INTO tts(name) SELECT nameFROM tts;
-
Query OK, 2 rows affected(0.00 sec)
-
Records: 2 Duplicates: 0 Warnings: 0
-
-
………………………………
-
-
mysql1> INSERT INTO tts(name) SELECT nameFROM tts;
-
Query OK, 131072 rows affected(0.79 sec)
-
Records: 131072 Duplicates: 0 Warnings: 0
-
-
mysql1> INSERT INTO tts(name) SELECT nameFROM tts;
-
Query OK, 262144 rows affected(2.15 sec)
-
Records: 262144 Duplicates: 0 Warnings: 0
-
-
mysql1> \! du -sh/data/mysql/test/tts*
-
12K/data/mysql/test/tts.frm
-
92M/data/mysql/test/tts.ibd
② 再保證mysql2上有相同的庫(kù)表結(jié)構(gòu)，此處為新建，并將mysql2上新建的test.tts表discard掉ibd文件：
-
mysql> \R mysql2>
-
PROMPTset to 'mysql2> '
-
mysql2> USE test;
-
Database changed
-
mysql2> CREATE TABLE tts(idint PRIMARY KEYAUTO_INCREMENT, namechar(128));
-
Query OK, 0 rows affected(0.01 sec)
-
-
mysql2> \! du -sh/data/mysql/test/tts*
-
12K/data/mysql/test/tts.frm
-
96K/data/mysql/test/tts.ibd
-
注意！該alter table ... discard tablespace操作會(huì)記錄binlog并影響復(fù)制結(jié)構(gòu)，慎用，或set sql_log_bin=0;
-
mysql2> ALTERTABLE tts DISCARD TABLESPACE;
-
Query OK, 0 rows affected(0.01 sec)
-
-
mysql2> \! du -sh/data/mysql/test/tts*
-
12K/data/mysql/test/tts.frm
③ 對(duì)mysql1的test.tts表做FLUSH TABLES操作，此時(shí)會(huì)多了一個(gè)cfg文件：
-
mysql1> FLUSH TABLE ttsFOR EXPORT;
-
Query OK, 0 rows affected(0.05 sec)
-
-
mysql1> \! du -sh/data/mysql/test/tts*
-
4.0K/data/mysql/test/tts.cfg
-
12K/data/mysql/test/tts.frm
-
92M/data/mysql/test/tts.ibd
④ 開多一個(gè)終端，在vm1上將ibd和cfg文件scp到vm2上：
-
vm1> scp /data/mysql/test/tts.{ibd,cfg} user@vm2:/data/mysql/test
-
user@vm2's password:
-
tts.ibd 100% 92MB 46.0MB/s 00:02
-
tts.cfg 100% 380 0.4KB/s 00:00
-
⑤ 將mysql1的test.tts表做UNLOCK操作（此時(shí)可發(fā)現(xiàn)cfg文件已被刪除）：
-
mysql1> UNLOCK TABLES;
-
Query OK, 0 rows affected(0.00 sec)
-
-
mysql1> \! du -sh/data/mysql/test/tts*
-
12K/data/mysql/test/tts.frm
-
92M/data/mysql/test/tts.ibd
⑥ 在vm2上將傳過(guò)來(lái)的ibd和cfg文件修改權(quán)限：
-
vm2> chown mysql:mysql/data/mysql/test/tts.{ibd,cfg}
⑦ 將上述ibd文件IMPORT到tts表中：
-
mysql2> ALTERTABLE tts IMPORT TABLESPACE;
-
Query OK, 0 rows affected(0.93 sec)
-
-
mysql2> SELECT count(*) FROM tts;
-
+----------+
-
| count(*) |
-
+----------+
-
| 524288 |
-
+----------+
-
1 rowin set (0.94 sec)
至此，已經(jīng)將mysql1實(shí)例上的tts表中數(shù)據(jù)快速地遷移到mysql2實(shí)例上了。
〇 上述幾個(gè)步驟的解釋：
操作②中的discard tablespace會(huì)在表上加上MDL鎖，刪除change buffer所有相關(guān)的緩存項(xiàng)，設(shè)置表元數(shù)據(jù)信息，標(biāo)志tablespace為刪除狀態(tài)，重新生成表的id，保證基于表id的操作后續(xù)均會(huì)失敗，再將idb文件干掉，在②中的兩次du可以看到.idb文件已經(jīng)被刪除了。這是一個(gè)十分危險(xiǎn)的操作，慎重；此操作也會(huì)被記錄到binlog中，若在復(fù)制結(jié)構(gòu)可能會(huì)有很大的影響，切記先臨時(shí)關(guān)閉binlog。
操作③中的flush table ... for export會(huì)給test.tts表加上共享鎖，并將purge coordinator thread（在并行復(fù)制中類似sql thread）停止，并且將臟頁(yè)強(qiáng)制同步到磁盤，創(chuàng)建并將test.tts表的元數(shù)據(jù)寫入.cfg文件；
FLUSH TABLES ... FOR EXPORT在error log中體現(xiàn)了這個(gè)過(guò)程：
[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
操作⑤執(zhí)行unlock tables將③中的鎖解除，此時(shí).cfg文件被刪掉，purge coordinator thread也會(huì)重新啟動(dòng)；（在做flush table ... for export時(shí)不能關(guān)閉session，避免鎖釋放造成.cfg文件刪除）
UNLOCK TABLES在error log中記錄為：
[Note] InnoDB: Deleting the meta-data file './test/tts.cfg'
[Note] InnoDB: Resuming purge
操作⑦則是通過(guò)import tablespace操作，將從vm1上傳輸過(guò)來(lái)的.ibd文件和導(dǎo)入到tts表中，此時(shí).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
過(guò)程為讀取cfg文件：表定義，索引定義，索引RootPage，列定義等等。再讀取import文件每一個(gè)page，檢查完整性，根據(jù)讀取到的cfg文件，重新設(shè)置當(dāng)前表的元數(shù)據(jù)信息。
總結(jié)一下整個(gè)過(guò)程就是：
create table $new_table ...
alter table $new_table discard tablespace;（刪除新表的tablespace文件，保留frm文件）
flush table $old_table for export;（關(guān)閉該表，并且生成cfg文件）
拷貝ibd文件，已經(jīng)對(duì)應(yīng)的cfg文件。
unlock tables;
將ibd文件和cfg文件copy到新地址，修改好權(quán)限
alter table $new_table import tablespace;
〇 限制：兩個(gè)實(shí)例都必須開啟獨(dú)立表空間，innodb_file_per_table
遷移的兩個(gè)實(shí)例的innodb_page_size必須一致，并且mysql server版本建議一致
不支持在分區(qū)表上執(zhí)行discard tablespace
不支持在有主外鍵關(guān)系的表上執(zhí)行discard tablespace，除非設(shè)置foregin_key_checks=0
〇 參考文檔： MySQL 5.6 Reference Manual - 14.5.5 Copying Tablespaces to Another Server (Transportable Tablespaces)
作者微信公眾號(hào)（持續(xù)更新）
文章標(biāo)題：MySQLTransportableTablespace(傳輸表空間)使用詳解
文章起源：
http://weahome.cn/article/gijejj.html
重慶分公司
重慶分公司
