教你实现MySQL表数据迁移自动化

教你实现MySQL表数据迁移自动化2014-06-30一、背景

之前我写过关于SQL Server的数据迁移自动化的文章：SQL Server 数据库迁移偏方，在上篇文章中设计了一张临时表，这个临时表记录搬迁的配置信息，用一个存储过程读取这张表进行数据的迁移，再由一个Job进行迭代调用这个存储过程。

在这次MySQL的实战中，我的数据库已经做了4个分片，分布在不同的4台机器上，每台机器上的数据量有1.7亿（1.7*4=6.8亿），占用空间260G（260*4=1040G），这次迁移的目的就是删除掉一些历史记录，减轻数据库压力，有人说这为什么不使用表分区呢？这跟我们的业务逻辑有关造成无法使用表分区，至于为什么，参考阅读：MySQL表分区实战，其中最重要就是唯一索引的问题，扩展阅读：MySQL当批量插入遇上唯一索引，这篇文章需要了解MySQL的定时器的一些知识：MySQL定时器Events

本文与SQL Server 数据库迁移偏方最大的不同就是MySQL的Events不是串行执行的，当作业调用的存储过程还没有执行完毕，但又到了调度的时间，MySQL不会等待上次作业完成之后再调度，所以会造成重复调用读取到相同的数据；而SQL Server并不存在上面的问题。

二、设计思路

1. 创建一个临时表TempBlog_Log，这个表用于保存每次转移数据的ID起始值和结束值，以及搬迁的开始时间和结束时间；（这个ID是我们要迁移表的主键，自增字段，唯一标识）

2. 创建一个存储过程InsertData（），这个存储过程用于在TempBlog_Log表中插入记录，创建这个存储过程是因为MySQL跟SQL Server有些不同，MySQL不支持匿名存储过程，SQL Server直接执行SQL就可以了，无需为这些SQL再创建一个存储过程，这就是匿名存储过程了；

3. 创建一个存储过程MoveBlogData（），这个存储过程用于在TempBlog_Log表中读取记录，再批量把BlogA数据转移到BlogB中；这个是核心逻辑，解决了定时器重复调度的问题，详情见代码的解释；

4. 创建一个定时器e_Blog, 这个定时器定时调用存储过程MoveBlogData（），但是这里存在重复调度的问题，只能通过存储过程MoveBlogData（）进行控制。

三、迁移自动化特点

1. 该设计适应于大数据的迁移；

2. 可以最小化宕机时间（在转移的过程中BlogA还是一直在进数据的，只是在最后一部分数据的时候需要短时间的停入库操作）；

3. 可以防止MySQL定时器重复执行所带来的问题；

4. 可以实时监控数据转移的进度；

5. 数据迁移可能需要持续好几天的时间，它能保证BlogB的数据会无限的接近BlogA的数据；

四、代码分析

（一）创建临时表TempBlog_Log

-- 创建表CREATE TABLE TempBlog_Log（BeginId INT NOT NULL,EndId INT NOT NULL,IsDone BIT DEFAULT b"0" NOT NULL,BeginTime DATETIME DEFAULT NULL,EndTime DATETIME DEFAULT NULL,PRIMARY KEY（BeginId） ）;

下面就对表结构进行字段解释：

1） BeginId、EndId都是ServerA迁移表的主键值，BeginId表示一次数据迁移的起始值，EndId表示一次数据迁移的结束值，两个值的差就是这次数据转移的数据量；

2） IsDone 表示是否已经成功转移数据；

3） BeginTime表示转移的开始时间，EndTime表示转移的结束时间，这两个字段设置缺省值为NULL很关键，是后面进行判断是否重复执行的依据；

（二）创建存储过程InsertData（）

-- 存储过程DELIMITER $$USE `DataBaseName`$$DROP PROCEDURE IF EXISTS `InsertData`$$CREATE DEFINER=`root`@`%` PROCEDURE `InsertData`（）BEGINDECLARE ids_begin,ids_end,ids_increment INT;SET ids_begin=130000000;-- 需要转移开始Id值SET ids_end=210000000;-- 需要转移结束Id值SET ids_increment=200000;-- 每次转移的Id量WHILE ids_begin < ids_end DO INSERT INTO TempBlog_Log（BeginId,EndId） VALUES（ids_begin,ids_begin+ids_increment）;SET ids_begin = ids_begin + ids_increment;END WHILE; END$$DELIMITER ;

MySQL中不支持匿名存储过程，所以为了在临时表TempBlog_Log插入记录，只能创建一个存储过程了，如果你还没写过MySQL的存储过程，那么这是一个很好的例子。

1）为了能在存储过程中使用MySQL的分隔符“;”，DELIMITER $$表示你以“$$”作为分隔符，你也可以使用“//”；

2）定义变量时，你需要把所有的变量定义完了，之后再进行赋值，不然会报错，这跟SQL Server是有区别的；

3） WHILE条件后面需要加DO，而且要以END WHILE;作为结束标记；

4）作为存储过程的结束，再次出现“$$”表示已经结束，跟上一个“$$”形成一个整体、过程，并重新设置“;”为分隔符；

5）执行CALL InsertData（）;调用上面的存储过程，插入数据，调用完毕的结果如下图Figure1所示：

（Figure1：转移前状态）