MySQL Transportable Tablespace(传输表空间) 使用详解

143次阅读

共计 3710 个字符，预计需要花费 10 分钟才能阅读完成。

将大的 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
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 会在表上加上排他锁，并将 idb 文件干掉，在②中的两次 du 可以看到.idb 文件已经被删除了。这是一个十分危险的操作，慎重；

操作③中的 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

〇限制：
两个实例都必须开启独立表空间，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)

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/142381.htm

正文完

星哥说事-微信公众号