当前位置 博文首页 > 魏小言的博客:OSC 在线更改表结构
随着业务的发展,更改表结构变的越来越常见。一般情况下,我们通过alter table 之类的DDL语句就可以完成。然而当执行alter table 语句时,数据库会对整个实例加锁,阻塞业务的所有操作,当线上数据表数据量非常庞大时,阻塞时间将会非常长,这对于我们是无法容忍的。因此我们不能简单粗暴地对原表执行alter table语句。
一、OSC操作
在线更改表结构有两种方法:
1、主从切换。即先逐步下线从库,在从库上执行alter table 语句,完成后替换线上从库,这样一台一台修改,最后做一下主从切换,再进行一次结构修改。整个过程耗时很长,并且做主从切换时,风险也很大。
2、Online Scheme Change,即OSC。
简单地来讲,OSC实际上是建立一张新表,在新表上执行DDL操作,然后将原表的数据同步到新表中,最后把新表rename为原表,并将原表删除或备份。在数据的同步中,除了要复制原始数据,也要将操作过程中原表数据产生的变化进行记录,以保证数据一致性,可以采用触发器记录更改。
整个OSC过程可以分为几个阶段:
1、创建新表。
2、创建触发器。
3、复制数据。
4、重放记录。
5、重命名(rename)。
6、清理。
下面分别介绍这几个阶段。
1、创建新表。在dba的实际操作中,往往需要在开发机上写出最终的表结构创建语句,然后通过ftp供dba下载操作。
2、创建触发器。可以设置mysql的insert、update和delete触发器,将更改保存到delta表中。delta表设计如下:
delta表保存了自增字段,更新类型(插入、更新、删除),是否已经被重放这三个字段以及原表的所有字段。
触发器触发时,可以通过old和new关键字将更新之后的数据存储到delta表中,供重放使用。
创建触发器的代码如下:
(1)插入触发器
delta表中存储插入的数据
(2)删除触发器
delta表中存储删除的数据
(3)更新触发器
delta表中存储的是更新之前的数据,更新之后的数据直接从原表获取即可(避免同一条记录被多次更新)
3、复制数据
将表数据从A导到B有好几种方法,最常用的一种是insert into B select * from A,在执行该语句时,数据库select的结果集加锁,影响业务,因此我们不能采用这种方案。
实际上,我们可以使用select...into outfile和load data infile的组合来实现,这样是不会对纪录进行锁定的。考虑到一个表的数据量可能很大,可以根据select limit 分批次导出数据,如下所示:
注意:导入导出数据需要有file权限,此外,路径设置还需要考虑是否设置@@secure_file_priv变量。
4、重放记录
从delta表中查询所有未被重放的,对于插入类型,执行插入操作;删除类型,执行删除操作;更新类型,更新的最终值从原表获取,条件从delta表中获取。
注意,重放记录至少需要执行两次,第一次,在复制完数据完成之后;第二次,在rename之前,以保证数据一致性。
5、重命名
操作完成后,通过mysql的rename命令,将新表重命名为原表,原表命名为备份表。注意,rename是一个原子操作,需要将两次重命名写到一条命令里。
6、清理
OSC操作完成后,将产生的备份表、触发器、delta表等删掉,以免影响下次使用。
二、总结
以上即为OSC的全部过程,可以看出,精华就在于触发器那一部分的使用,由于工具的复杂性,使用中还有一些注意事项:
1、此工具不是原子操作,如果某一点失败,处理不好,会留下很多中间过程的垃圾文件,这些可能会影响下次OSC操作;
2、执行OSC操作的原表和新表不能更改字段顺序,切记切记!
3、在执行之前,要对磁盘容量进行评估,因为OSC会使用表一倍以上的空间。
4、表不能有外键,否则可能会出现意想不到的问题。
源自小哥:司马迪