Online DDL一直是MySQL数据库的一个痛点,好在官方和社区都有不错的解决方案。这些方案大致有以下几个:
本文主要给大家介绍一下由github开源的gh-ost。目前为止大表DDL最好的工具。
1 安装
# 本文安装1.1.5版本
wget https://github.com/github/gh-ost/releases/download/v1.1.5/gh-ost-1.1.5-1.x86_64.rpm
yum -y localinstall gh-ost-1.1.5-1.x86_64.rpm
2 使用
gh-ost有三种模式,分别是在连接从库DDL、单实例(相当于只在主库)DDL、在从库测试DDL三种模式。
本案例主库:192.168.1.30
本案例主库:192.168.1.31
前置条件:
gh-ost目前需要MySQL版本为5.7及更高版本。
gh-ost需要具有以下权限的账户:
在迁移表所在的数据库(模式)上具有ALTER、CREATE、DELETE、DROP、INDEX、INSERT、LOCK TABLES、SELECT、TRIGGER、UPDATE权限,或者当然也可以在*.*上具有这些权限。
要么:
在*.*上具有SUPER、REPLICATION SLAVE权限,或者:
在*.*上具有REPLICATION CLIENT、REPLICATION SLAVE权限。
需要SUPER权限来执行STOP SLAVE、START SLAVE操作。这些操作用于:
在binlog_format不是ROW且您明确指定了--switch-to-rbr的情况下,切换binlog_format为ROW。如果您的复制已经处于RBR状态(binlog_format=ROW),您可以指定--assume-rbr以避免STOP SLAVE/START SLAVE操作,因此不需要SUPER权限。gh-ost对所有MySQL连接使用REPEATABLE_READ事务隔离级别,而不管服务器默认设置如何。运行--test-on-replica:在切换阶段之前,gh-ost会停止复制,以便您可以比较两个表并确保迁移是正确的。
2.1 连接从库DDL
gh-ost --user="gh_user" --password="xxx" --host=192.168.1.31 --database="bbb" --table="student" --initially-drop-old-table --alter="ADD COLUMN y1 varchar(10),add column y2 int not null default 0 comment 'test' " --execute
2.2 单实例DDL
gh-ost --user="gh_user" ---password="xxx" --host=192.168.1.30 --database="bbb" --table="student" --alter="ADD COLUMN cc2 varchar(10),add column cc3 int not null default 0 comment 'test' " --allow-on-master --execute
2.3 从库测试DDL
gh-ost --user="gh_user" --password="xxx" --host=192.168.1.31 --database="bbb" --table="student" --alter="ADD COLUMN abc1 varchar(10),add column abc2 int not null default 0 comment 'test' " --test-on-replica --switch-to-rbr --execute
3 参数说明
gh-ost --help即可查看
4 限速
gh-ost --user="gh_user" --password="xxx" --host=192.168.1.31 --database="bbb" --table="student" --alter="ADD COLUMN o2 varchar(10),add column o1 int not null default 0 comment 'test' " --exact-rowcount --serve-socket-file=/tmp/gh-ost.t1.sock --panic-flag-file=/tmp/gh-ost.panic.t1.flag --postpone-cut-over-flag-file=/tmp/ghost.postpone.t1.flag --allow-on-master --execute
① 标示文件终止运行:--panic-flag-file
创建文件终止运行,例子中创建/tmp/gh-ost.panic.t1.flag文件,终止正在运行的gh-ost,临时文件清理需要手动进行。
② 表示文件禁止cut-over进行,即禁止表名切换,数据复制正常进行。--postpone-cut-over-flag-file
创建文件延迟cut-over进行,即推迟切换操作。例子中创建/tmp/ghost.postpone.t1.flag文件,gh-ost 会完成行复制,但并不会切换表,它会持续的将原表的数据更新操作同步到临时表中。
③ 使用socket监听请求,操作者可以在命令运行后更改相应的参数。--serve-socket-file,--serve-tcp-port(默认关闭)
创建socket文件进行监听,通过接口进行参数调整,当执行操作的过程中发现负载、延迟上升了,不得不终止操作,重新配置参数,如 chunk-size,然后重新执行操作命令,可以通过scoket接口进行动态调整。如:
暂停操作:
#暂停
echo throttle | socat - /tmp/gh-ost.test.t1.sock
#恢复
echo no-throttle | socat - /tmp/gh-ost.test.t1.sock
修改限速参数:
echo chunk-size=100 | socat - /tmp/gh-ost.t1.sock
echo max-lag-millis=200 | socat - /tmp/gh-ost.t1.sock
echo max-load=Thread_running=3 | socat - /tmp/gh-ost.t1.sock
5 DDL 过程
① 检查有没有外键和触发器。
② 检查表的主键信息。
③ 检查是否主库或从库,是否开启log_slave_updates,以及binlog信息
④ 检查gho和del结尾的临时表是否存在
⑤ 创建ghc结尾的表,存数据迁移的信息,以及binlog信息等
---以上校验阶段
⑥ 初始化stream的连接,添加binlog的监听
---以下迁移阶段
⑥ 创建gho结尾的临时表,执行DDL在gho结尾的临时表上
⑦ 开启事务,按照主键id把源表数据写入到gho结尾的表上,再提交,以及binlog apply。
---以下cut-over阶段
⑧ lock源表,rename 表:rename 源表 to 源_del表,gho表 to 源表。
⑨ 清理ghc表。
6 限制
外键约束不受支持。将来可能会在某种程度上支持。
触发器不受支持。将来可能会支持。
MySQL 5.7支持JSON列,但不作为主键的一部分。
前后两个表必须共享一个主键或其他唯一键。gh-ost将使用此键在复制时迭代表行。阅读更多
迁移键不得包含具有NULL值的列。这意味着:
列是NOT NULL,或
列是可空的,但不包含任何NULL值。
默认情况下,如果唯一键包括可空列,则gh-ost不会运行。
您可以通过--allow-nullable-unique-key覆盖此行为,但请确保这些列中没有实际的NULL值。现有的NULL值无法保证迁移表的数据完整性。
不允许迁移存在具有相同名称但大小写不同的另一个表的表。
例如,如果同一模式中存在名为MYtable的另一个表,则无法迁移MyTable。
Amazon RDS可用,但具有自己的限制。
Google Cloud SQL可用,需要--gcp标志。
Aliyun RDS可用,需要--aliyun-rds标志。
Azure Database for MySQL可用,需要--azure标志,并且有详细的文档(azure.md)。
当通过副本进行迁移时,不支持多源。如果直接连接到主服务器(--allow-on-master),则应该可以工作(但从未经过测试)。
仅支持活动-被动设置的主-主设置。不支持同时在两个主服务器上写入表的活动-活动设置。将来可能会支持。
如果枚举字段作为迁移键(通常是主键)的一部分,则迁移性能将降低并且可能很差。阅读更多
不支持迁移FEDERATED表,这与gh-ost解决的问题无关。
不支持加密的二进制日志。
不支持ALTER TABLE ... RENAME TO some_other_name(您不应该使用gh-ost进行这种微不足道的操作)。
6 参考资料:
1、https://www.cnblogs.com/zhoujinyi/p/9187421.html
2、《MySQL实战》 第七章 DDL
3、官方文档 https://github.com/github/gh-ost/tree/master/doc