对于大表的迁移,如果使用mysqldump进行导出,然后重新导入到其它环境,速度是非常缓慢的。如果使用传输表空间,则可以解决这个问题。
测试使用传输表空间迁移表,基础环境如下:
源库 | 目标库 | |
IP地址 | 192.168.10.11 | 192.168.10.12 |
数据库版本 | 5.7.24 | 5.7.24 |
数据库名称 | db1 | db2 |
待迁移的表 | test01 |
(1)在目标库创建和源库相同的表test01
先在主库db1上查看表信息,并生成创建表的语句
mysql> select count(*) from test01; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec) mysql> show create table test01; +--------+--------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------+ | test01 | CREATE TABLE `test01` ( `id1` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id1`) ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 | +--------+-----------------------------------------------------+ 1 row in set (0.00 sec)
在目标数据库db2上创建表test01
mysql> CREATE TABLE `test01` ( -> `id1` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(30) DEFAULT NULL, -> PRIMARY KEY (`id1`) -> ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ; Query OK, 0 rows affected (0.00 sec)
(2)目标数据库db2上丢弃表空间
在丢弃表空间后,test01表的ibd文件会被删除
[root@slavedb db2]# pwd /mysql/data/db2 [root@slavedb db2]# ls db.opt test01.frm test01.ibd mysql> alter table test01 discard tablespace; Query OK, 0 rows affected (0.01 sec) [root@slavedb db2]# ls db.opt test01.frm
此时,test01表还在db2中,但是已经无法访问
mysql> show tables; +---------------+ | Tables_in_db2 | +---------------+ | test01 | +---------------+ 1 row in set (0.00 sec) mysql> select * from test01; ERROR 1814 (HY000): Tablespace has been discarded for table 'test01'
(3)导出源库db1上的test01表
先对表db1.test01加上读锁,此时,db1.test01可读,但是不可写
mysql> flush table test01 for export; Query OK, 0 rows affected (0.00 sec)
执行完export后,会生成一个test01.cfg文件
[root@masterdb db1]# pwd /mysql/data/db1 [root@masterdb db1]# ls -l total 18472 -rw-r----- 1 mysql mysql 61 Feb 11 14:28 db.opt -rw-r----- 1 mysql mysql 388 Feb 11 15:06 test01.cfg -rw-r----- 1 mysql mysql 8588 Feb 11 14:29 test01.frm -rw-r----- 1 mysql mysql 9437184 Feb 11 14:29 test01.ibd
将test01.ibd和test01.cfg文件拷贝到目标数据库db2的数据文件路径下
[root@masterdb db1]# scp test01.cfg test01.ibd root@192.168.10.12:/mysql/data/db2/ root@192.168.10.12's password: test01.cfg 100% 388 324.4KB/s 00:00 test01.ibd 100% 9216KB 59.6MB/s 00:00
传输结束后,释放test01表的读锁
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
(4)修改目标数据库db2的test01.ibd和test01.cfg文件权限
[root@slavedb db2]# chown mysql:mysql test01.ibd test01.cfg [root@slavedb db2]# ls -l total 9236 -rw-r----- 1 mysql mysql 61 Feb 11 14:30 db.opt -rw-r----- 1 mysql mysql 388 Feb 11 15:10 test01.cfg -rw-r----- 1 mysql mysql 8588 Feb 11 14:58 test01.frm -rw-r----- 1 mysql mysql 9437184 Feb 11 15:10 test01.ibd
(5)在db2上加载test01表
mysql> alter table test01 import tablespace; Query OK, 0 rows affected (0.02 sec) mysql> select count(*) from test01; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
可以看到,test01表数据已经正常传输过来。
【完】