【MySQL运维】使用gh-ost工具实现大表在线DDL变更

一、gh-ost介绍

作为MySQL DBA都会面临这样一个问题，就是当对大表（10G以上）进行DDL变更时会有长时间锁表问题，影响业务可持续性。目前解决这个问题的方案一个较为通用的使用Percona公司开源的pt-osc工具，还有一个就是github基于go语言开发的gh-ost。gh-ost和pt-osc类似都能对大表进行在线DDL，在表格存在高并发写的情况下由于gh-ost是模拟从库应用binlog，所以性能不如pt-osc并发操作，但是gh-ost更灵活，不依赖触发器，并能根据实际情况动态调整。

安装gh-ost很简单，只需要访问https://github.com/github/gh-ost下载RPM包直接安装即可

二、gh-ost原理

gh-ost作为一个伪从库，从主库上拉取 binlog，经过过滤之后重新应用到主库，相当于在主库上的增量操作通过 binlog又应用回主库，不过是应用在一张虚拟表上。其大致的工作过程：

1、gh-ost首先连接到主库上，根据alter语句创建出虚拟表。

2、gh-ost作为从库根据参数设定连接到主库或其它从库上，在拷贝主库上的数据到虚拟表的同时，拉取增量数据产生的binlog，然后不断的把binlog应用回主库。

3、等待全部数据同步完成后将虚拟表和原表切换。

4、gh-ost在执行中会在原本的binlog event里面增加hint和心跳包用来控制整个流程的进度，检测状态等。gh-ost也会做很多前置的校验检查，比如binlog_format、表主键和唯一键、是否有外键等等。

5、gh-ost过程可停止，如果变更过程发现主库性能受影响，可以立刻停止拉取和应用 binlog，稳定之后根据binlog位置点继续应用。

三、gh-ost工作模式

a、连接到从库，但在主库做操作（默认方式，gh-ost将会检查从库状态，然后找到主库进行连接进行迁移）

1、行数据在主库上读写
2、读取从库的二进制日志，将变更应用到主库
3、在从库收集表格式、字段、索引、行数等信息
4、在从库上读取内部的变更事件（如心跳事件）
5、在主库切换表

b、连接到主库直接操作

直连主库进行copy数据和应用binlog，主库binlog格式须是row

c、在从库迁移（测试模式）

该模式会在从库执行迁移操作。gh-ost会连接到主库，所有的操作都在从库执行，不会对主库进行任何的改动。即使在复制运行阶段也可以进行表的切换操作，gh-ost将控制速度保证从库可以及时的进行数据同步

四、gh-ost重要参数说明

· 数据库连接配置

• --user：指定MYSQL用户
• --password：MySQL用户密码
• --port：MySQL端口，最好用从库
• --database：数据库名称
• --table：指定表名 

· 负载相关配置

• --max-load：状态名称=阈值，如Threads_running=100,Threads_connected=500
• --max-lag-millis：主从复制最大延迟时间，超过该值后gh-ost将采取节流(throttle)措施，默认1500s
• --chunk-size int：每次处理的行数量(100-100000)，默认1000
• --conf：gh-ost的配置文件路径
• --critical-load：当MySQL status中某状态的值超过阈值，则退出gh-ost，避免带来更高负载。如--critical-load Threads_connected=20,Connections=1500
• --critical-load-hibernate-seconds：负载达到critical-load时，gh-ost的休眠时间，休眠期间不会进行任何读/写
• --critical-load-interval-millis：如果值为0，当达到--critical-load，gh-ost立即退出；值非0，当达到--critical-load，gh-ost会在--critical-load-interval-millis秒数后再次进行检查，如果依旧达到--critical-load则退出
• --skip-foreign-key-checks：如确定表没有外键，可设置为true，跳过验证

· 主从相关配置

• --migrate-on-replica：在从库上进行迁移操作
• --allow-on-master：允许直连主库进行copy数据和应用binlog，主库binlog格式须是row
• --assume-master-host string：指定主库地址，格式为ip:port或hostname:port。通常用于主主架构或gh-ost发现不到主的时候
• --assume-rbr：原本的DDL过程中从库会执行一次stop slave、start slave操作，通过该选项可以不用重启，前提是binlog格式需要是ROW

· SQL相关配置

• --alter：指定需要操作的DDL语句，如engine=innodb代表alter table b engine=innodb
• --execute：需要真正执行操作，不加该选项的话默认仅做测试并退出
• --ok-to-drop-table：gh-ost操作结束后删除旧表，默认状态是不删除旧表，会存在_tablename_del表
• --timestamp-old-table：最终rename的时候表名会加上时间戳后缀，每次执行的时候都会生成一个新的表名
• --test-on-replica：仅测试，在切换之前复制会停止，然后进行切换操作，但是最终会切换回来，保证原始表不变（两个表都会保存下来，可以对两个表进行一致性检查等操作）
• --timestamp-old-table：在旧表名中使用时间戳，可以使旧表名称得到具有唯一且无冲突的交叉迁移
• --panic-flag-file：当这个文件被创建出来，gh-ost将会立即退出
• --postpone-cut-over-flag-file：当这个文件存在的时候，gh-ost数据正常复制但是不会完成切换，直到该文件被删除

 

五、gh-ost示例

· 示例1：对b表进行重建，即alter table b engine=innodb

gh-ost --user="root" --password="123456" --host=127.0.0.1 --port=3306 --database="test" --table="b" --allow-on-master --max-load=Threads_running=20 --critical-load=Threads_running=50 --critical-load-interval-millis=5000 --chunk-size=1000 --alter="engine=innodb" --execute --timestamp-old-table --assume-rbr --panic-flag-file=/tmp/ghost.panic.flag

· 示例2：在主库上进行DDL 

gh-ost --user="root" --password="123456" --host=127.0.0.1 --port=3306 --database="test" --table="t"  --alter="ADD COLUMN y1 varchar(10),add column y2 int not null default 0 comment 'test' "  --allow-on-master --execute

终止gh-ost值只需要创建--panic-flag-file所指定的文件即可。停止gh-ost后会有遗留表xxx_ghc、xxx_gho及socket 文件，如果需要再次执行需要清理掉这些文件和表