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

之前我写过关于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`()
BEGIN
    DECLARE 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所示：

查看本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

（Figure1：转移前状态）

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!