1 环境准备

1.两台数据库服务器,并安装好mysql服务，两台机器之间内网互相通信，最好可以进行免密传输
也可以一台服务器搭建多实例环境
10.0.0.101 10.0.0.102

2 迁移流程

2.1 导出要迁移的库与表结构,事件，触发器等（不导出数据）（102机器）

mysqldump -h 10.0.0.101 -uroot -p123 -S /data/3308/mysql.sock  -B world --default-character-set=utf8 -R --triggers -E --single-transaction --default-character-set=utf8 --no-data >/tmp/world.sql

2.2 恢复该库的的表与结构（102机器）

mysqldump -uroot -p123  -S /data/3307/mysql.sock >/tmp/world.sql

image.png

注意：目前只恢复了库与建表语句，数据并没有恢复

2.3 表空间删除（102机器）

1.利用select concat函数 将删除语句导出，再进行执行操作
（1）导出删除语句
select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema='world' into outfile '/tmp/discad.sql';
（2）执行删除操作
执行过程中会有外键检查 临时跳过外键检查 恢复后开启
set foreign_key_checks=0;跳过外键检查。
source /tmp/discad.sql;

2.4 可见ibd文件已经被删除

image.png

2.5 在101机器scp world库的ibd文件到102机器world库下（101机器）

[root@102 ~]# scp -rp /data/3307/data/world/*.ibd root@10.0.0.102:/data/3308/data/world/
city.ibd                                                                    100%  592KB  73.6MB/s   00:00    
country.ibd                                                                 100%  160KB  66.8MB/s   00:00    
countrylanguage.ibd                                                         100%  224KB  39.1MB/s   00:00    

2.6恢复ibd文件（102机器）

1.授权ibd文件
chown -R mysql.mysql /data/3308/data/world/*
2.导出恢复语句
select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='world' into outfile '/tmp/import.sql';
2.执行恢复操作
source /tmp/import.sql;
3.外键检查开启
set foreign_key_checks=1;

image.png

3 总结

优点
迁移速度快，该种方法迁库操作适用于稍大库，小库也是没问题的，因为没有导出与导入sql语句过程，唯一浪费时间就在于scp过程
缺点
在scp之后原库不可再进行增删改操作，如果不想停服务，只能进行临时锁表，更改为只读操作，待恢复到另一台机器测试没问题，更改完关系之后，再进行解锁。