不停机修改线上 MySQL 主键字段 以及其带来的问题和总结思考

时间:2022-05-13 10:46:38

起因:

线上 user 数据库没有自增字段,数据量已经达到百万级。无论是给离线仓库还是数据分析同步数据,没有主键自增 id 都是杀手级的困难。所以在使用 create_time 痛苦了几次之后准备彻底来解决这个问题。

解决问题的思路:

当时理了两个思路,一个是直接找个夜生人静的夜晚,drop 掉目前的 primary key 。然后再 add 一个 id 字段做 primary key auto increament 。我觉得这个方法对于 100w 以下的数据表是一个不错的方法,首先简单,其次找个夜生人静的晚上也不会有非常长时间的锁表。当然最好使用 online ddl 进行操作就更好了。我们碰到的稍微现实一点问题是,目前线上表还会跟离线表进行同步。然后也想挑战一下不停机不锁表的切换。所以规划了一个思路是

1. 首先建一个变更 ddl 后的表,在这里表现为已经设置好自增 id 的情况。

2. 将线上数据导入该表。并且保持两张表同步。

  1. 方案1: 如果数据比较多 例如已经是1000w 插入了,而且插表频率高,那么建议使用类似于 maxwell 数据同步工具,读取 binlog 时时同步让两张表时刻保持一致。最后找一个表没那么热的时候,rename 表即可。 rename 操作可以在一个命令里面执行几乎是秒切,如果跟上线上没有延迟那么基本上不会有数据丢失,如果切换的时候表比较热,可能会有几条不一致可以手动修复即可。
  2. 方案2: 如果数据没有那么多 比如只是百万级别,而且也有表明显不热的时候比如说晚上 3 4 点的时候。那么你可以通过导入数据,然后根据 update_time 字段时时跟上线上表。然后最后再切换之前再执行一次 根据 update_time 的更新脚本,然后立即完成 rename 操作。这样的操作 在数据量不大表不热的时候甚至不会有数据的不一致。大家可以根据需求来选择具体的方案。

3. 找个夜深人静的时候 rename 表完成两张表的切换。

其实从流程上来看还是比较简单的,但是实际处理的时候还是遇到不少问题。

1. 我在线上进行两张表同步的时候发现 slave 明显跟不上 master,而且延迟的时间越来越大。后来查阅了一些资料发现是当 master 发生大量写入的时候, slave 是很有可能发生慢慢跟不上 master 的情况。引起这个情况有可能是 socket 网络通信上的跟不上,可能是 slave 机器的配置不行,更有可能是没有开启并行复制导致吞吐量跟不上。因为主库上可能是开启多进程在写,从库却是单进程在跟复制,慢慢落后是理所当然的,但是只要这种情况不长时间持续,可以发现离线库在主库完成大并发写入之后会慢慢恢复正常。

2. 切换方案跟太多别的业务交杂在一起,例如还去考虑同时兼容我们的离线分析库,把问题搞得十分复杂。下次在思考类似需求的解决方案的时候,一定要遵循最小优化原则。理论上来说离线分析库完全可以等切换完成之后全量重刷,由于已经有主键自增 id 了。刷新会变得比以前简单方便非常多,速度也会快很多。所以没有必要纠结同时兼容的问题。导致复制方案变得复杂最后导致出错。

3. 新键的表 ddl 一定不要随意改动上面的字段,理论上无论之前表字段有多不合理多恶心,如果没有在切换计划里面有比较全面的考量都不应该擅自删除字段。保持只增不减跟之前完全兼容。

最后这次切换虽然出了一些小岔子,但是总的来说比较顺利。目前已经正常跑了一周,应该稳定了。

Reference:

http://mysql.taobao.org/monthly/2016/04/08/  常见的 MySQL slave 延迟问题

https://www.cnblogs.com/kevingrace/p/6065088.html  MySQL binlog 总结

https://dev.mysql.com/doc/refman/5.6/en/rename-table.html 参考 MySQL 5.6 rename 文档