手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-03-30] 来源:Linux社区  作者:Fantasy life [字体: ]

将大的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)
  7. mysql1> INSERT INTO tts(name) VALUES(REPEAT('a',128));
  8. Query OK, 1 row affected (0.00 sec)
  9. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  10. Query OK, 1 row affected (0.00 sec)
  11. Records: 1 Duplicates: 0 Warnings: 0
  12. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  13. Query OK, 2 rows affected (0.00 sec)
  14. Records: 2 Duplicates: 0 Warnings: 0
  15. ………………………………
  16. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  17. Query OK, 131072 rows affected (0.79 sec)
  18. Records: 131072 Duplicates: 0 Warnings: 0
  19. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  20. Query OK, 262144 rows affected (2.15 sec)
  21. Records: 262144 Duplicates: 0 Warnings: 0
  22. mysql1> \! du -sh /data/mysql/test/tts*
  23. 12K /data/mysql/test/tts.frm
  24. 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)
  7. mysql2> \! du -sh /data/mysql/test/tts*
  8. 12K /data/mysql/test/tts.frm
  9. 96K /data/mysql/test/tts.ibd
  10. mysql2> ALTER TABLE tts DISCARD TABLESPACE;
  11. Query OK, 0 rows affected (0.01 sec)
  12. mysql2> \! du -sh /data/mysql/test/tts*
  13. 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)
  3. mysql1> \! du -sh /data/mysql/test/tts*
  4. 4.0K /data/mysql/test/tts.cfg
  5. 12K /data/mysql/test/tts.frm
  6. 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)
  3. mysql1> \! du -sh /data/mysql/test/tts*
  4. 12K /data/mysql/test/tts.frm
  5. 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)
  3. mysql2> SELECT count(*) FROM tts;
  4. +----------+
  5. | count(*) |
  6. +----------+
  7. | 524288   |
  8. +----------+
  9. 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

linux
相关资讯       MySQL传输表空间 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款