MySQL Transportable Tablespace

野性酷女 2024-03-30 10:57 72阅读 0赞

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

④ 开多一个终端，在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
5.

⑤ 将mysql1的test.tts表做UNLOCK操作（此时可发现cfg文件已被删除）：

1.  mysql1> UNLOCK TABLES;
2.  Query OK, 0 rows affected (0.00 sec)
3.  
4.  mysql1> \\! du -sh /data/mysql/test/tts\*
5.  12K /data/mysql/test/tts.frm
6.  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.  
4.  mysql2> SELECT count(\*) FROM tts;
5.  \+----------+
6.  | count(\*) |
7.  \+----------+
8.  | 524288 |
9.  \+----------+
10. 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

[Oracle]: https://www.linuxidc.com/topicnews.aspx?tid=12
[MySQL]: https://www.linuxidc.com/topicnews.aspx?tid=21

发表评论取消回复

表情：

评论列表（有 0 条评论，72人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL Transportable Tablespace

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

野性酷女/ 2024年03月30日 10:57/ 0 赞/ 73 阅读

相关解决mysql Tablespace exists

目录解决MySQL Tablespace exists 什么是表空间？ Tablespace exists错误的原因解决Tablespace exists错误的方法

喜欢ヅ旅行/ 2024年02月17日 10:28/ 0 赞/ 92 阅读

相关 gRPC Transport

gRPC Transport Transport 分为 `ClientTransport` 和 `ServerTransport`，分别用于客户端和服务端 ![grpc

素颜马尾好姑娘i/ 2022年10月22日 04:27/ 0 赞/ 238 阅读

相关 UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces

![在这里插入图片描述][3df21d13f0104b21bf221fb41d245e33.png] dumpfile 和 transport\_tablespaces 不

梦里梦外;/ 2022年09月12日 04:57/ 0 赞/ 26 阅读

相关 tablespaces-datafiles示意图

![oracle_20tablespaces.gif][] [oracle_20tablespaces.gif]: /images/20220806/5444ddcabaf

た入场券/ 2022年08月06日 10:27/ 0 赞/ 210 阅读

相关 MySQL Study之--MySQL增加新的表空间（tablespace）

MySQL Study之--MySQL增加新的表空间（tablespace）系统环境：操作系统：RedHat EL64(64) MySQL：

Myth丶恋晨/ 2022年08月05日 08:47/ 0 赞/ 383 阅读

相关 Oracle表空间（tablespaces）

[http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html][http_www.cnblogs.com_fnng_

你的名字/ 2022年07月14日 14:12/ 0 赞/ 98 阅读

相关 MySQL数据库[ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace XXX

问题背景昨天老师告诉我，客户的网站打开不了，让我帮忙解决下，我上服务器看了下负载，发现数据库的服务器cpu都百分100%，大多数是mysqld 这个进程。于是，我打算把

逃离我推掉我的手/ 2022年05月27日 08:39/ 0 赞/ 147 阅读

相关 Oracle Transportable TableSpace(TTS) 传输表空间

详细参考： [https://www.yuhuashi.info/post/110.html][https_www.yuhuashi.info_post_110.html]

比眉伴天荒/ 2022年05月22日 13:28/ 0 赞/ 412 阅读

相关 Temporary Tablespaces

In this Document Purpose Scope and Application DB 11.1: Temporary Tablespace

以你之姓@/ 2022年03月31日 05:40/ 0 赞/ 238 阅读