mysql 表空间迁移_使用MySQL传输表空间迁移数据

对于大表的迁移，如果使用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(*) fromtest01;+----------+

| count(*) |

+----------+

| 10000 |

+----------+

1 row in set (0.00sec)

mysql> show create tabletest01;+--------+--------------------------------------------------------+

| Table | Create Table |

+--------+-------------------------------------------------------+

| test01 | CREATE TABLE`test01` (

`id1`int(11) NOT NULLAUTO_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 NULLAUTO_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 tabletest01 discard tablespace;

Query OK,0 rows affected (0.01sec)[root@slavedb db2]# ls

db.opt test01.frm

此时，test01表还在db2中，但是已经无法访问

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| test01 |

+---------------+

1 row in set (0.00sec)

mysql> select * fromtest01;

ERROR1814 (HY000): Tablespace has been discarded for table 'test01'

(3)导出源库db1上的test01表

先对表db1.test01加上读锁，此时，db1.test01可读，但是不可写

mysql> flush table test01 forexport;

Query OK,0 rows affected (0.00 sec)

执行完export后，会生成一个test01.cfg文件

[root@masterdb db1]# pwd

/mysql/data/db1

[root@masterdb db1]#ls -l

total18472

-rw-r----- 1 mysql mysql 61 Feb 11 14:28db.opt-rw-r----- 1 mysql mysql 388 Feb 11 15:06test01.cfg-rw-r----- 1 mysql mysql 8588 Feb 11 14:29test01.frm-rw-r----- 1 mysql mysql 9437184 Feb 11 14:29test01.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:00test01.ibd100% 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]# chownmysql:mysql test01.ibd test01.cfg

[root@slavedb db2]#ls -l

total9236

-rw-r----- 1 mysql mysql 61 Feb 11 14:30db.opt-rw-r----- 1 mysql mysql 388 Feb 11 15:10test01.cfg-rw-r----- 1 mysql mysql 8588 Feb 11 14:58test01.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.02sec)

mysql> select count(*) from test01;+----------+

| count(*) |

+----------+

| 10000 |

+----------+

1 row in set (0.00 sec)

可以看到，test01表数据已经正常传输过来。

【完】