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

發布時間：2020-08-13 03:34:12 來源：ITPUB博客閱讀：284 作者：神諭丶欄目：MySQL數據庫

將大的InnoDB表從一個實例，移動或者復制到另一個實例，有很多的方法，在5.6之前常用的是通過物理或者邏輯備份來實現。
在5.6.6+的版本中，用到了一種基于表空間遷移的快速方法，即類似Oracle TTS。
因為用到，故整理記錄至此。

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

〇過程：

① 先在mysql1上創建測試數據：

mysql> \R mysql1>
PROMPT set to 'mysql1> '
mysql1> USE test;
Database changed
mysql1> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(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 name FROM tts;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql1> INSERT INTO tts(name) SELECT name FROM tts;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
………………………………
mysql1> INSERT INTO tts(name) SELECT name FROM tts;
Query OK, 131072 rows affected (0.79 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql1> INSERT INTO tts(name) SELECT name FROM 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上有相同的庫表結構，此處為新建，并將mysql2上新建的test.tts表discard掉ibd文件：

mysql> \R mysql2>

PROMPT set to 'mysql2> '

mysql2> USE test;

Database changed

mysql2> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(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操作會記錄binlog并影響復制結構，慎用，或set sql_log_bin=0;

mysql2> ALTER TABLE tts DISCARD TABLESPACE;

Query OK, 0 rows affected (0.01 sec)

mysql2> \! du -sh /data/mysql/test/tts*

12K /data/mysql/test/tts.frm

③ 對mysql1的test.tts表做FLUSH TABLES操作，此時會多了一個cfg文件：

mysql1> FLUSH TABLE tts FOR 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

④ 開多一個終端，在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操作（此時可發現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上將傳過來的ibd和cfg文件修改權限：

vm2> chown mysql:mysql /data/mysql/test/tts.{ibd,cfg}

⑦ 將上述ibd文件IMPORT到tts表中：

mysql2> ALTER TABLE tts IMPORT TABLESPACE;

Query OK, 0 rows affected (0.93 sec)

mysql2> SELECT count(*) FROM tts;

+----------+

| count(*) |

+----------+

| 524288 |

+----------+

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)

作者微信公眾號（持續更新）
MySQL Transportable Tablespace(傳輸表空間) 使用詳解

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

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

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

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

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

猜你喜歡

最新資訊

相關推薦

相關標簽