一种基于binlog的大表变更及清理方法技术

本发明专利技术公开了一种基于binlog的大表变更及清理方法。该方法主要包括：接收输入参数(数据库连接信息、表名、DDL命令和where条件)；创建日志记录表和幽灵表，如果为DDL操作，则先将DDL命令应用到幽灵表；通过insert into的方式从原表拷贝数据至幽灵表,如果为数据清理操作，则在insert int语句中拼接where条件，只拷贝符合where条件的数据，同时监听binlog日志批量解析dml事件；监测到数据拷贝完成且没有新的binlog事件，进行原表和幽灵表的切换。本发明专利技术灵活性、稳定性较好，既可以用作大表变更，也可以用作数据清理，在实际生产环境使用过程中，对数据库性能影响较小。对数据库性能影响较小。对数据库性能影响较小。

【技术实现步骤摘要】
一种基于binlog的大表变更及清理方法


[0001]本申请涉及涉及计算机
，特别是涉及一种大表变更及清理方法。

技术介绍

[0002]和传统企业级开发不同，互联网迭代变更比较频繁，数据库表数据量比较庞大，基本每周甚至每天都有上线、一月一次大的上线是很常见的事，对应着表结构变更和数据清理也是经常发生的，比如给表添加字段、加索引等。当数据量小的情况下，直接修改表结构都很快，然而，在数据量大的场景下，特别是千万级、亿级的大表，如果处理不当，这些操作往往会引发锁表的巨大隐患，特别是在生产环境中，一旦在变更表结构过程中，出现了长时间锁表，会导致用户产生的数据长时间无法正常变更到表中，进而导致服务功能异常，结果将是灾难性的。
[0003]Mysql执行表过程为根据原表创建临时表，对原表加排它锁，将变更应用到临时表然后复制数据到临时表，最后释放写锁并rename表。后面衍生出了一种基于触发器的变更方案pt
‑
osc，利用触发器处理变更期间的dml操作，只需在数据拷贝完后才会对表加锁。
[0004]通过加锁的方式实现表结构的变更，虽然能保证数据的一致性，但也有被其它锁阻塞的风险，而且这种方式如果应用的大表，容易造成大的主从时延。而利用触发器机制的pt
‑
osc，虽然能减少加锁的时间，但由于触发器本身的缺陷，对主库的性能影响比较大。

技术实现思路

[0005]本专利技术提供一种基于binlog的大表变更及清理方法，旨在解决线上大表DDL变更锁表时间长、数据负载高等问题，有效保证业务连续性。
[0006]第一方面，一种基于binlog的大表变更及清理方法，包括以下步骤：
[0007](1)接收输入参数，所述输入参数包括数据库连接信息、表名、DDL命令和where条件；
[0008](2)校验所述输入参数；若参数校验不通过，则退出并打印相关日志；若校验通过，则根据DDL命令内容判断具体是在线DDL操作还是数据清理操作；
[0009](3)创建日志记录表和幽灵表；
[0010](4)如果步骤(2)中判断为在线DDL操作，则先将在线DDL操作语句应用到幽灵表，然后通过insert into的方式从原表拷贝数据至幽灵表；如果步骤(2)中判断为数据清理操作，则将where条件拼接到insert into的语句中，只拷贝符合where条件的数据，同时监听binlog日志批量解析dml事件；
[0011](5)监测到数据拷贝完成且没有新的binlog事件，进行原表和幽灵表的切换。
[0012]进一步地，步骤(2)中校验所述输入参数，具体包括：对数据库联通性、账号权限和DDL命令语法的检查。
[0013]进一步地，步骤(4)中所述从原表拷贝数据至幽灵表，其中根据输入的所述where条件，控制表数据复制的起点。
[0014]进一步地，步骤(1)中所述输入参数还包括运行时参数，步骤(5)中所述进行原表和幽灵表的切换的过程中，使用socket监听请求，实现根据负载情况动态调整所述运行时参数。
[0015]进一步地，所述运行时参数包括单次拷贝的行数。
[0016]进一步地，步骤(2)中所述打印相关日志，是通过命令行输出报错。
[0017]进一步地，在步骤(4)执行过程中，还定期打印进度日志，通过命令行输出进度信息。
[0018]第二方面，一种计算机设备，包括存储器和处理器，所述存储器存储有计算机程序，所述处理器执行所述计算机程序时实现上述的基于binlog的大表变更及清理方法。
[0019]第三方面，一种计算机可读存储介质，其上存储有计算机程序，处理器执行所述计算机程序时实现上述的基于binlog的大表变更及清理方法。
[0020]本专利技术至少具有以下有益效果：
[0021]本专利技术基于MySQL内部机制，实现了对线上生产环境数据库大表(千万级记录以上)的快速DDL操作，将原表的变更应用到一个表结构完全相同的新表上，通过复制的方式将原表的数据拷贝至新表，复制数据时可指定where条件，控制表数据复制的起点，因而兼具了数据清理功能，同时监听binlog事件将原表的更新应用到新表，迁移过程中使用socket监听请求，可根据负载情况动态调整运行时参数，保证数据库的稳定性，最后进行原子性的新旧表切换，整个过程安全、可控，对原表排他锁持有时长通常低于3S，对数据库的影响较小。该方案解决了线上大表DDL变更锁表时间长、数据负载高等问题，有效的保证了业务连续性。
附图说明
[0022]图1为本专利技术一个实施例的基本流程示意图。
具体实施方式
[0023]为了使本申请的目的、技术方案及优点更加清楚明白，以下结合附图及实施例，对本申请进行进一步详细说明。应当理解，此处描述的具体实施例仅仅用以解释本申请，并不用于限定本申请。
[0024]在一个实施例中，如图1所示，一种基于binlog的大表变更方法包括以下步骤：
[0025](1)接收初始化输入参数，所述输入参数包括数据库连接信息、表名、DDL命令和where条件；如果不涉及数据清理(没有删除需求)，则无需输入where条件；另外，还可输入单次拷贝的行数；
[0026](2)校验输入参数，主要是校验数据库连通性、账号权限、DDL命令语法等；若参数校验不通过，则退出并打印相关日志(通过命令行输出报错)；若校验通过，则根据DDL命令内容判断具体是在线DDL操作(alter操作)还是数据清理操作；
[0027](3)创建日志记录表和幽灵表；
[0028](4)如果步骤(2)中判断为在线DDL操作，则先将DDL命令应用到幽灵表，通过insert into的方式从原表(线上大表)拷贝数据至幽灵表，其中可根据输入的所述where条件，控制表数据复制的起点；如果步骤(2)中判断为数据清理操作，则将where条件拼接到
insert into的语句中，只拷贝符合where条件的数据(从而不符合where条件的数据就被清理掉了)，同时监听binlog日志批量解析dml事件；期间定期打印进度日志(通过命令行输出进度信息)；
[0029](5)监测到数据拷贝完成且没有新的binlog事件，进行原表和幽灵表的切换。若输入的参数还包括单次拷贝的行数，则进行原表和幽灵表的切换的过程中，还可使用socket监听请求，实现根据负载情况动态调整所述运行时参数。
[0030]本实施例实现的表的切换是原子性切换，通过两个会话的操作来完成，原理是基于MySQL内部机制：被lock table阻塞之后，执行rename的优先级高于dml，也即先执行rename table，然后执行dml。假设程序操作的会话是c10和c20，其他业务的dml请求的会话是c1
‑
c9,c11
‑
c19,c21
‑
c29。
[0031]具体内部运行步骤包括：
[0032本文档来自技高网...

【技术保护点】

【技术特征摘要】
1.一种基于binlog的大表变更及清理方法，其特征在于，包括以下步骤：(1)接收输入参数，所述输入参数包括数据库连接信息、表名、DDL命令和where条件；(2)校验所述输入参数；若参数校验不通过，则退出并打印相关日志；若校验通过，则根据DDL命令内容判断具体是在线DDL操作还是数据清理操作；(3)创建日志记录表和幽灵表；(4)如果步骤(2)中判断为在线DDL操作，则先将在线DDL操作语句应用到幽灵表，然后通过insert into的方式从原表拷贝数据至幽灵表；如果步骤(2)中判断为数据清理操作，则将where条件拼接到insert into的语句中，只拷贝符合where条件的数据，同时监听binlog日志批量解析dml事件；(5)监测到数据拷贝完成且没有新的binlog事件，进行原表和幽灵表的切换。2.根据权利要求1所述的基于binlog的大表变更及清理方法，其特征在于，步骤(2)中校验所述输入参数，具体包括：对数据库联通性、账号权限和DDL命令语法的检查。3.根据权利要求1所述的基于binlog的大表变更及清理方法，其特征在于，步骤(4)中所述从原表拷贝数据至幽灵表，其中根据输入的...

【专利技术属性】
技术研发人员：刘隆剑，陈翔，欧志伟，吴坚，
申请(专利权)人：上海甄云信息科技有限公司，
类型：发明
国别省市：