MySQL Transportable TableSpace(TTS) 使用详解

1月 9th, 2018

将大的InnoDB表从一个实例,移动或者复制到另一个实例,有很多的方法,在5.6之前常用的是通过物理或者逻辑备份来实现。
在5.6.6+的版本中,用到了一种基于表空间迁移的快速方法,即类似Oracle TTS。

 

使用起来十分简单,此处将实例1上的表数据通过TTS方式导入实例2

〇 在实例1上创建测试数据:

  1. — 创建待迁移的表
  2. mysql1> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
  3. Query OK, 0 rows affected (0.01 sec)
  4. mysql1> INSERT INTO tts(name) VALUES(REPEAT(‘a’,128));
  5. Query OK, 1 row affected (0.00 sec)
  6. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  7. Query OK, 1 row affected (0.00 sec)
  8. Records: 1 Duplicates: 0 Warnings: 0
  9. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  10. Query OK, 2 rows affected (0.00 sec)
  11. Records: 2 Duplicates: 0 Warnings: 0
  12. ………………………………
  13. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  14. Query OK, 262144 rows affected (2.15 sec)
  15. Records: 262144 Duplicates: 0 Warnings: 0
  16. — 已产生92M的ibd文件
  17. mysql1> \! du -sh /data/mysql/test/tts*
  18. 12K /data/mysql/test/tts.frm
  19. 92M /data/mysql/test/tts.ibd

 

〇 在实例2上创建和实例1相同表结构的表,并执行(该操作会记录binlog,可临时不记binlog):

  1. SET sql_log_bin=0;
  2. ALTER TABLE tts DISCARD TABLESPACE;
  3. SET sql_log_bin=1;

 

〇 对实例1的该表执行FLUSH TABLE $tb_name FOR EXPORT:

  1. mysql1> FLUSH TABLE tts FOR EXPORT;
  2. Query OK, 0 rows affected (0.05 sec)
  3. — 产生多了一个cfg文件
  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

 

〇 将实例1的/data/mysql/test/tts.{ibd,cfg}文件拷到实例2所在的datadir对应的库目录中,并为俩文件赋权。

 

〇 恢复实例1中test.tts表的可用性(此时cfg文件已回收):

  1. mysql1> UNLOCK TABLES;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql1> \! du -sh /data/mysql/test/tts*
  4. 12K /data/mysql/test/tts.frm
  5. 92M /data/mysql/test/tts.ibd

 

〇 将表空间ibd文件恢复至实例2的test.tts表:

  1. mysql2> ALTER TABLE tts IMPORT TABLESPACE;
  2. Query OK, 0 rows affected (0.93 sec)
  3. mysql2> SELECT count(*) FROM tts;
  4. +———-+
  5. | count(*) |
  6. +———-+
  7. | 524288   |
  8. +———-+
  9. 1 row in set (0.34 sec)

 

至此,已经将mysql1实例上的tts表中数据快速地迁移到mysql2实例上了。
discard tablespace
为表加MDL锁,删除change buffer所有相关的缓存项,设置表元数据信息,标志tablespace为删除状态,重新生成表的id,保证基于表id的操作后续均会失败,再将idb文件干掉,在②中的两次du可以看到.idb文件已经被删除了。这是一个十分危险的操作,慎重;此操作也会记录binlog,若在复制结构可能会有意想不到的灾难,切记先临时关闭binlog。
flush table … for export
为表加共享锁,并purge coordinator thread(在并行复制中的sql thread被称为coordinator)停止,并且将脏页强制同步到磁盘,创建并将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时不能断开会话,避免锁释放造成.cfg文件删除)
UNLOCK TABLES在error log中记录为:
[Note] InnoDB: Deleting the meta-data file ‘./test/tts.cfg’
[Note] InnoDB: Resuming purge
import tablespace
将从实例1上传输过来的.ibd文件和导入到tts表中,此时.cfg文件也必须存在;
ALTER TABLE … IMPORT TABLESPACE在error log中记录为:
[Note] InnoDB: Importing tablespace for table ‘test/tts’ that was exported from host ‘$host1’
[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文件,重新设置当前表的元数据信息。
〇 MySQL TTS的限制:
两个实例都必须开启独立表空间(innodb_file_per_table=1)
迁移的两个实例的innodb_page_size必须一致,并且mysql server版本建议一致
不支持在分区表上执行discard tablespace
不支持在有主外键关系的表上执行discard tablespace,除非设置foregin_key_checks=0
标签:
目前还没有任何评论.