MySQL Transportable Tablespace 传输表空间 利用详解
发布时间：2022-03-26 12:45:33 所属栏目：MySql教程 来源：互联网
导读：将大的InnoDB表从一个实例，移动或者复制到另一个实例，有很多的方法，在5.6之前常用的是通过物理或者逻辑备份来实现。 在5.6.6+的版本中，用到了一种基于表空间迁移的快速方法，即类似Oracle TTS。 因为用到，故整理记录至此。 实验用到两台机器，单机单实
|
将大的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 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
