mysql库迁移_MySql数据库 指定库迁移

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之后原库不可再进行增删改操作，如果不想停服务，只能进行临时锁表，更改为只读操作，待恢复到另一台机器测试没问题，更改完关系之后，再进行解锁。