不同情况下的MySQL 的迁移方案（推荐）

怪我咯

发布时间：2017-04-06 10:36:18

1402人浏览过

来源于php中文网

原创

一、为什么要迁移

MySQL 迁移是 DBA 日常维护中的一个工作。迁移，究其本义，无非是把实际存在的物体挪走，保证该物体的完整性以及延续性。就像柔软的沙滩上，两个天真无邪的小孩，把一堆沙子挪向其他地方，铸就内心神往的城堡。

生产环境中，有以下情况需要做迁移工作，如下：

磁盘空间不够。比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
业务出现瓶颈。比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案；
机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；
项目改造。某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。

一句话，迁移工作是不得已而为之。实施迁移工作，目的是让业务平稳持续地运行。

二、MySQL 迁移方案概览

MySQL 迁移无非是围绕着数据做工作，再继续延伸，无非就是在保证业务平稳持续地运行的前提下做备份恢复。那问题就在怎么快速安全地进行备份恢复。

一方面，备份。针对每个主节点的从节点或者备节点，都有备份。这个备份可能是全备，可能是增量备份。在线备份的方法，可能是使用 mysqldump，可能是 xtrabackup，还可能是 mydumper。针对小容量（10GB 以下）数据库的备份，我们可以使用 mysqldump。但针对大容量数据库（数百GB 或者 TB 级别），我们不能使用 mysqldump 备份，一方面，会产生锁；另一方面，耗时太长。这种情况，可以选择 xtrabackup 或者直接拷贝数据目录。直接拷贝数据目录方法，不同机器传输可以使用 rsync，耗时跟网络相关。使用 xtrabackup，耗时主要在备份和网络传输。如果有全备或者指定库的备份文件，这是获取备份的最好方法。如果备库可以容许停止服务，直接拷贝数据目录是最快的方法。如果备库不允许停止服务，我们可以使用 xtrabackup（不会锁定 InnoDB 表），这是完成备份的最佳折中办法。

另一方面，恢复。针对小容量（10GB 以下）数据库的备份文件，我们可以直接导入。针对大容量数据库（数百GB 或者 TB 级别）的恢复，拿到备份文件到本机以后，恢复不算困难。具体的恢复方法可以参考第四节。

三、MySQL 迁移实战

我们搞明白为什么要做迁移，以及迁移怎么做以后，接下来看看生产环境是怎样操作的。不同的应用场景，有不同的解决方案。

阅读具体的实战之前，假设和读者有如下约定：

为了保护隐私，本文中的服务器 IP 等信息经过处理；
如果服务器在同一机房，用服务器 IP 的 D 段代替服务器，具体的 IP 请参考架构图；
如果服务器在不同机房，用服务器 IP 的 C 段和 D 段代替服务器，具体的 IP 请参考架构图；
每个场景给出方法，但不会详细地给出每一步执行什么命令，因为一方面，这会导致文章过长；另一方面，我认为只要知道方法，具体的做法就会迎面扑来的，只取决于掌握知识的程度和获取信息的能力；
实战过程中的注意事项请参考第五节。

3.1 场景一一主一从结构迁移从库

遵循从易到难的思路，我们从简单的结构入手。A 项目，原本是一主一从结构。101 是主节点，102 是从节点。因业务需要，把 102 从节点迁移至 103，架构图如图一。102 从节点的数据容量过大，不能使用 mysqldump 的形式备份。和研发沟通后，形成一致的方案。

图一一主一从结构迁移从库架构图

具体做法是这样：

研发将 102 的读业务切到主库；
确认 102 MySQL 状态（主要看 PROCESS LIST），观察机器流量，确认无误后，停止 102 从节点的服务；
103 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份；
将 102 的整个 mysql 数据目录使用 rsync 拷贝到 103；
拷贝的同时，在 101 授权，使 103 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；
待拷贝完成，修改 103 配置文件中的 server_id，注意不要和 102 上的一致；
在 103 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；
进入 103 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；
Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 101 和 103 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；
和研发沟通，除了做数据一致性验证外，还需要验证账号权限，以防业务迁回后访问出错；
做完上述步骤，可以和研发协调，把 101 的部分读业务切到 103，观察业务状态；
如果业务没有问题，证明迁移成功。

3.2 场景二一主一从结构迁移指定库

我们知道一主一从只迁移从库怎么做之后，接下来看看怎样同时迁移主从节点。因不同业务同时访问同一服务器，导致单个库压力过大，还不便管理。于是，打算将主节点 101 和从节点 102 同时迁移至新的机器 103 和 104，103 充当主节点，104 充当从节点，架构图如图二。此次迁移只需要迁移指定库，这些库容量不是太大，并且可以保证数据不是实时的。

图二一主一从结构迁移指定库架构图

具体的做法如下：

103 和 104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；
102 导出数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；
102 收集指定库需要的账号以及权限；
102 导出数据完毕，使用 rsync 传输到 103，必要时做压缩操作；
103 导入数据，此时数据会自动同步到 104，监控服务器状态以及 MySQL 状态；
103 导入完成，104 同步完成，103 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；
上述完成后，可研发协作，将 101 和 102 的业务迁移到 103 和 104，观察业务状态；
如果业务没有问题，证明迁移成功。

3.3 场景三一主一从结构双边迁移指定库

接下来看看一主一从结构双边迁移指定库怎么做。同样是因为业务共用，导致服务器压力大，管理混乱。于是，打算将主节点 101 和从节点 102 同时迁移至新的机器 103、104、105、106，103 充当 104 的主节点，104 充当 103 的从节点，105 充当 106 的主节点，106 充当 105 的从节点，架构图如图三。此次迁移只需要迁移指定库，这些库容量不是太大，并且可以保证数据不是实时的。我们可以看到，此次迁移和场景二很类似，无非做了两次迁移。

图三一主一从结构双边迁移指定库架构图

具体的做法如下：

103 和 104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；
102 导出 103 需要的指定库数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；
102 收集 103 需要的指定库需要的账号以及权限；
102 导出103 需要的指定库数据完毕，使用 rsync 传输到 103，必要时做压缩操作；
103 导入数据，此时数据会自动同步到 104，监控服务器状态以及 MySQL 状态；
103 导入完成，104 同步完成，103 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；
上述完成后，和研发协作，将 101 和 102 的业务迁移到 103 和 104，观察业务状态；
105 和 106 新建实例，搭建主从关系，此时的主节点和从节点处于空载；
102 导出 105 需要的指定库数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；
102 收集 105 需要的指定库需要的账号以及权限；
102 导出 105 需要的指定库数据完毕，使用 rsync 传输到 105，必要时做压缩操作；
105 导入数据，此时数据会自动同步到 106，监控服务器状态以及 MySQL 状态；
105 导入完成，106 同步完成，105 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；
上述完成后，和研发协作，将 101 和 102 的业务迁移到 105 和 106，观察业务状态；
如果所有业务没有问题，证明迁移成功。

3.4 场景四一主一从结构完整迁移主从

接下来看看一主一从结构完整迁移主从怎么做。和场景二类似，不过此处是迁移所有库。因 101 主节点 IO 出现瓶颈，打算将主节点 101 和从节点 102 同时迁移至新的机器 103 和 104，103 充当主节点，104 充当从节点。迁移完成后，以前的主节点和从节点废弃，架构图如图四。此次迁移是全库迁移，容量大，并且需要保证实时。这次的迁移比较特殊，因为采取的策略是先替换新的从库，再替换新的主库。所以做法稍微复杂些。

图四一主一从结构完整迁移主从架构图

具体的做法是这样：

研发将 102 的读业务切到主库；
确认 102 MySQL 状态（主要看 PROCESS LIST，MASTER STATUS），观察机器流量，确认无误后，停止 102 从节点的服务；
104 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份，注意，此处操作的是 104，也就是未来的从库；
将 102 的整个 mysql 数据目录使用 rsync 拷贝到 104；
拷贝的同时，在 101 授权，使 104 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；
待拷贝完成，修改 104 配置文件中的 server_id，注意不要和 102 上的一致；
在 104 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；
进入 104 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；
Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 101 和 104 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；
除了做数据一致性验证外，还需要验证账号权限，以防业务迁走后访问出错；
和研发协作，将之前 102 从节点的读业务切到 104；
利用 102 的数据，将 103 变为 101 的从节点，方法同上；
接下来到了关键的地方了，我们需要把 104 变成 103 的从库；

104 STOP SLAVE；
103 STOP SLAVE IO_THREAD;
103 STOP SLAVE SQL_THREAD，记住 MASTER_LOG_FILE 和 MASTER_LOG_POS；
104 START SLAVE UNTIL 到上述 MASTER_LOG_FILE 和 MASTER_LOG_POS；
104 再次 STOP SLAVE；
104 RESET SLAVE ALL 清除从库配置信息；
103 SHOW MASTER STATUS，记住 MASTER_LOG_FILE 和 MASTER_LOG_POS；

云商商城系统
云商商城系统，即云商未来商城系统，云商商城系统提供完整的电子商务解决方案。云商商城系统集CMS、B2C、B2B2C、B2B、C2B、SNS用户社区于一体，包括网站商城、微信商城、手机商城等多种交易模式，支持实现不同模式的O2O电子商务平台。为企业树立企业品牌形象，实现独立网络推广，批发、零售，供应商加盟，并充分结合网站SEO、微博、APP，微信等移动客户端多渠道网络营销手段，实现线上线下统一管理，

下载
103 授权给 104 访问 binlog 的权限；
104 CHANGE MASTER TO 103；
104 重启 MySQL，因为 RESET SLAVE ALL 后，查看 SLAVE STATUS，Master_Server_Id 仍然为 101，而不是 103；
104 MySQL 重启后，SLAVE 回自动重启，此时查看 IO_THREAD 和 SQL_THREAD 是否为 YES；
103 START SLAVE；
此时查看 103 和 104 的状态，可以发现，以前 104 是 101 的从节点，如今变成 103 的从节点了。

业务迁移之前，断掉 103 和 101 的同步关系；
做完上述步骤，可以和研发协调，把 101 的读写业务切回 102，读业务切到 104。需要注意的是，此时 101 和 103 均可以写，需要保证 101 在没有写入的情况下切到 103，可以使用 FLUSH TABLES WITH READ LOCK 锁住 101，然后业务切到 103。注意，一定要业务低峰执行，切记；
切换完成后，观察业务状态；
如果业务没有问题，证明迁移成功。

3.5 场景五双主结构跨机房迁移

接下来看看双主结构跨机房迁移怎么做。某项目出于容灾考虑，使用了跨机房，采用了双主结构，双边均可以写。因为磁盘空间问题，需要对 A 地的机器进行替换。打算将主节点 1.101 和从节点 1.102 同时迁移至新的机器 1.103 和 1.104，1.103 充当主节点，1.104 充当从节点。B 地的 2.101 和 2.102 保持不变，但迁移完成后，1.103 和 2.101 互为双主。架构图如图五。因为是双主结构，两边同时写，如果要替换主节点，单方必须有节点停止服务。

图五双主结构跨机房迁移架构图

具体的做法如下：

1.103 和 1.104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；
确认 1.102 MySQL 状态（主要看 PROCESS LIST），注意观察 MASTER STATUS 不再变化。观察机器流量，确认无误后，停止 1.102 从节点的服务；
1.103 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份；
将 1.102 的整个 mysql 数据目录使用 rsync 拷贝到 1.103；
拷贝的同时，在 1.101 授权，使 1.103 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；
待拷贝完成，修改 1.103 配置文件中的 server_id，注意不要和 1.102 上的一致；
在 1.103 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；
进入 1.103 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；
Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 1.101 和 1.103 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；
我们使用相同的办法，使 1.104 变成 1.103 的从库；
和研发沟通，除了做数据一致性验证外，还需要验证账号权限，以防业务迁走后访问出错；
此时，我们要做的就是将 1.103 变成 2.101 的从库，具体的做法可以参考场景四；
需要注意的是，1.103 的单双号配置需要和 1.101 一致；
做完上述步骤，可以和研发协调，把 1.101 的读写业务切到 1.103，把 1.102 的读业务切到 1.104。观察业务状态；
如果业务没有问题，证明迁移成功。

3.6 场景六多实例跨机房迁移

接下来我们看看多实例跨机房迁移证明做。每台机器的实例关系，我们可以参考图六。此次迁移的目的是为了做数据修复。在 2.117 上建立 7938 和 7939 实例，替换之前数据异常的实例。因为业务的原因，某些库只在 A 地写，某些库只在 B 地写，所以存在同步过滤的情况。

图六多实例跨机房迁移架构图

具体的做法如下：

1.113 针对 7936 实例使用 innobackupex 做数据备份，注意需要指定数据库，并且加上 slave-info 参数；
备份完成后，将压缩文件拷贝到 2.117；
2.117 创建数据目录以及配置文件涉及的相关目录；
2.117 使用 innobackupex 恢复日志；
2.117 使用 innobackupex 拷贝数据；
2.117 修改配置文件，注意如下参数：replicate-ignore-db、innodb_file_per_table = 1、read_only = 1、 server_id；
2.117 更改数据目录权限；
1.112 授权，使 2.117 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；
2.117 CHANGE MASTE TO 1.112，LOG FILE 和 LOG POS 参考 xtrabackup_slave_info；
2.117 START SLAVE，查看从库状态；
2.117 上建立 7939 的方法类似，不过配置文件需要指定 replicate-wild-do-table；
和开发一起进行数据一致性的验证和验证账号权限，以防业务迁走后访问出错；
做完上述步骤，可以和研发协调，把相应业务迁移到 2.117 的 7938 实例和 7939 实例。观察业务状态；
如果业务没有问题，证明迁移成功。

四注意事项

介绍完不同场景的迁移方案，需要注意如下几点：

数据库迁移，如果涉及事件，记住主节点打开 event_scheduler 参数；
不管什么场景下的迁移，都要随时关注服务器状态，比如磁盘空间，网络抖动；另外，对业务的持续监控也是必不可少的；
CHANGE MASTER TO 的 LOG FILE 和 LOG POS 切记不要找错，如果指定错了，带来的后果就是数据不一致或者搭建主从关系失败；
执行脚本不要在 $HOME 目录，记住在数据目录；
迁移工作可以使用脚本做到自动化，但不要弄巧成拙，任何脚本都要经过测试；
每执行一条命令都要三思和后行，每个命令的参数含义都要搞明白；
多实例环境下，关闭 MySQL 采用 mysqladmin 的形式，不要把正在使用的实例关闭了；
从库记得把 read_only = 1 加上，这会避免很多问题；
每台机器的 server_id 必须保证不一致，否则会出现同步异常的情况；
正确配置 replicate-ignore-db 和 replicate-wild-do-table；
新建的实例记得把 innodb_file_per_table 设置为 1，上述中的部分场景，因为之前的实例此参数为 0，导致 ibdata1 过大，备份和传输都消耗了很多时间；
使用 gzip 压缩数据时，注意压缩完成后，gzip 会把源文件删除；
所有的操作务必在从节点或者备节点操作，如果在主节点操作，主节点很可能会宕机；
xtrabackup 备份不会锁定 InnoDB 表，但会锁定 MyISAM 表。所以，操作之前记得检查下当前数据库的表是否有使用 MyISAM 存储引擎的，如果有，要么单独处理，要么更改表的 Engine。

五技巧

在 MySQL 迁移实战中，有如下技巧可以使用：

任何迁移 LOG FILE 以 relay_master_log_file（正在同步 master 上的 binlog 日志名）为准，LOG POS 以 exec_master_log_pos（正在同步当前 binlog 日志的 POS 点）为准；
使用 rsync 拷贝数据，可以结合 expect、nohup 使用，绝对是绝妙组合；
在使用 innobackupex 备份数据的同时可以使用 gzip 进行压缩；
在使用 innobackupex 备份数据，可以加上 –slave-info 参数，方便做从库；
在使用 innobackupex 备份数据，可以加上 –throttle 参数，限制 IO，减少对业务的影响。还可以加上 –parallel=n 参数，加快备份，但需要注意的是，使用 tar 流压缩，–parallel 参数无效；
做数据的备份与恢复，可以把待办事项列个清单，画个流程，然后把需要执行的命令提前准备好；
本地快速拷贝文件夹，有个不错的方法，使用 rsync，加上如下参数：-avhW –no-compress –progress；
不同分区之间快速拷贝数据，可以使用 dd。或者用一个更靠谱的方法，备份到硬盘，然后放到服务器上。异地还有更绝的，直接快递硬盘。