教你实现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.cnhttp://www.bianceng.cn/database/MySQL/

(Figure1:转移前状态)

以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索mysql
, 数据
, 存储过程
, 数据迁移
, mysql定时器
, 一个
, 批量迁移
, 转移
, 创建定时器
, mysql防止重复插入
转移表
mysql 数据库迁移、oracle迁移到mysql、mysql data目录迁移、mysql数据迁移工具、mysql数据迁移,以便于您获取更多的相关知识。

时间: 2016-06-30