月份：2020年5月

1. 前言

概要

本文档描述MySQL第三方改表工具gh-ost的使用。

适用条件

本文档中描述的所有功能均基于mysql安装完成以后的场景。

2.工作原理

gh-ost是github针对pt-osc用触发器带来的问题而重新设计的一款较新的表结构变更工具。其使用binlog+回放线程来替换掉触发器。

1、先连接到主库上，创建临时的ghost表，根据alter语句修改新表。

2、作为一个“备库”连接到主库（或者某一个备库上），一边在主库上拷贝已有的数据到新表，一边从主库（或者某一个备库）上拉取增量数据的binlog。

3、然后不断的把 binlog 应用回主库。

4、cut-over是最后一步，锁住主库的源表，等待binlog 应用完毕，然后替换gh-ost表为源表。

2.1数据拷贝中如何保证数据一致性

根据设置的chunk大小，分批使用

来拷贝数据

binlog解析及回放转化：

delete语句解析为delete语句 insert语句解析，转换为replace into语句 update语句解析为update语句

gh-ost 做ddl变更期间对原表和影子表的操作有三种:

1、对原表的数据拷贝到影子表

2、业务对原表的DML操作，产生binlog

3、对影子表的应用原表的binlog 共有如下几种顺序:

1-》2-》3：

数据拷贝	DML	应用binlog
insert ignore into b select id>0 and id<3;	insert into b values(2,’b’);	replace into b values(2,’b’);
insert ignore into b select id>0 and id<3;	update b set val=’b’ where id=1;	update b set val=’b’ where id=1;
insert ignore into b select id>0 and id<3;	delete from b where id=1;	delete from b where id=1;

2-》3-》1：

DML	应用binlog	数据拷贝
insert into b values(2,’b’);	replace into b values(2,’b’);	拷贝语句为insert ignore，忽略插入
update b set val=’b’ where id=1;	update b set val=’b’ where id=1;此时影子表没有该记录，update空记录	拷贝的数据为update之后的数据
delete from b where id=1;	delete from b where id=1;此时影子表没有该记录，delete空记录	拷贝时id=1的数据已被删除，所以不会拷贝

2-》1-》3：

DML	数据拷贝	应用binlog
insert into b values(2,’b’);	insert ignore 将(2,’b’)数据插入影子表	replace into b values(2,’b’);覆盖数据，但数据一致
update b set val=’b’ where id=1;	insert ignore 将(1,’b’)数据插入影子表	执行update b set val=’b’ where id=1;数据一致
delete from b where id=1;	拷贝时id=1的数据已被删除，所以不会拷贝	delete from b where id=1;此时影子表没有该记录，delete空记录

由于binlog是由DML产生，所以3一定在2之后，故只有以上3种情况。这3种情况均能保证数据拷贝过程中影子表数据与原表一致。

2.2cut-over如何保证数据一致性

• Connections C1..C9 对 tbl 执行正常的DML: INSERT, UPDATE, DELETE
• Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT=’magic-be-here’
• Connection C10: LOCK TABLES tbl WRITE, tbl_old WRITE
• Connections C11..C19, 新进的对 tbl的dml，由于C10的 LOCK被阻塞
• Connection C20: RENAME TABLE tbl TO tbl_old, ghost TO tbl 同样被C10的LOCK阻塞, 但是优先级高于 C11..C19 和 C1..C9 和任何企图在tbl上执行 DML的会话
• Connections C21..C29, 新进连接，在 tbl上执行dml，但是被C10的 LOCK 和C20的 RENAME阻塞
• Connection C10: 检查 C20’s RENAME 是否仍在等待 (在processlist中检查 RENAME )
• Connection 10: DROP TABLE tbl_old什么都不会发生; tbl 仍然是 locked. 其他会话也仍然被阻塞.
• Connection 10: UNLOCK TABLES
• unlock后首先执行C20的RENAME , ghost 与 tbl互换, 然后 C1..C9, C11..C19, C21..C29 都在新的 tbl表上执行

注：

• 创建 tbl_old 防止过早交换表
• 一个会话在拥有WRITE LOCK后仍可以执行 DROP TABLE
• 被阻塞的 RENAME 优先级永远高于被阻塞的 INSERT/UPDATE/DELETE，无论哪个会话先被执行
• 第3步中的LOCK TABLES tbl WRITE, tbl_old WRITE会获取表的元数据锁，一旦执行，即使在等待锁时也会阻塞后面会话的DML请求，等待超时时间取决于MySQL参数：lock_wait_timeout。gh-ost获取锁流程：
  • 首先设置会话级参数：set session lock_wait_timeout:=6（该参数为-cut-over-lock-timeout-seconds*2，默认值为3，即设置为6秒，可根据需要进行调整）
  • 如果表上有大事务，可能会导致获取元数据锁超时，gh-ost会重试至多-default-retries次（默认为60）
  • 如果重试次数达到上限仍未获取到元数据锁，则程序失败退出

2.3cut-over流程失败时会发生什么

• C10 在 CREATE 时失败，什么都不会发生
• C10 在 LOCK 时失败, 表不会被锁。业务正常执行其他语句
• C10在C20即将 RENAME时失败:
  • 释放锁， C1..C9, C11..C19 立即在 tbl上执行。
  • C20的 RENAME 立即失败，因为 tbl_old 存在。 影响仅为C1..C9, C11..C19的操作被锁了一段时间
• C10 在 C20的 RENAME操作被阻塞后失败: 与上面的情况类似. 释放锁, C20的 RENAME 立即失败，因为 tbl_old 存在。
• C20 在 C10 drops table之前失败, 程序捕获到失败由 C10 执行: DROP, UNLOCK.
• C20 在 C10 DROPs the table 之后 unlock之前失败, 与上面情况相同.
• 如果C10和C20 都失败, 也没有问题: LOCK ， RENAME 都被释放. C1..C9, C11..C19, C21..C29 可正常操作 tbl.

无论在cut-over时发生什么异常整个cut-over都是原子化的，不会导致数据丢失。

参考http://code.openark.org/blog/mysql/solving-the-non-atomic-table-swap-take-iii-making-it-atomic

3.工具安装

gh-ost安装非常简单

1、获取定制化安装包：gh-ost-1.0.49-sn.x86_64.rpm

2、执行安装命令：rpm -ivh gh-ost-1.0.49-sn.x86_64.rpm

4.使用说明

使用样例：

并发调用使用样例：

主要参数说明：

-user参数：MYSQL用户
-password参数：MySQL密码
-host参数：MySQL hostname
-database参数：数据库名
-table参数：表名
-alter参数：是alter table 语句中除alter table tablename部分的子句。如SQL语句为：alter table t1 engine=innodb。该参数即填engine=innodb
-heartbeat-interval-millis参数:gh-ost心跳频率值
-allow-on-master参数：允许gh-ost直接运行在主库上，默认gh-ost连接的从库。如在主库执行，则必须设置
-assume-rbr:确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。
-ok-to-drop-table参数：gh-ost操作结束后，是否删除旧表，默认不删除。建议设置
-initially-drop-ghost-table参数：gh-ost操作之前，检查并删除已经存在的ghost表。默认不启用该参数，gh-ost直接退出操作。建议设置
-replica-server-id uint : gh-ost的server_id，并行调用gh-ost时，必须指定该参数，且必须唯一，否则执行cut-over时会产生锁冲突ERROR Timeout while waiting for events up to lock。
-execute参数：实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定-execute

在执行过程中，可以看到gh-ost打印类似如下的进度信息：

Copy: 671195/821238 81.7%; Applied: 570000; Backlog: 0/1000; Time: 7m0s(total), 7m0s(copy); streamer: mysql-bin.000100:47929523; Lag: 0.01s, State: migrating; ETA: 1m33s
Copy: 726045/821238 88.4%; Applied: 570000; Backlog: 0/1000; Time: 7m5s(total), 7m5s(copy); streamer: mysql-bin.000100:180662515; Lag: 0.01s, State: migrating; ETA: 55s
Copy: 737038/821238 89.7%; Applied: 570000; Backlog: 0/1000; Time: 7m6s(total), 7m6s(copy); streamer: mysql-bin.000100:207265195; Lag: 0.01s, State: migrating; ETA: 48s
Copy: 748031/821238 91.1%; Applied: 570000; Backlog: 0/1000; Time: 7m7s(total), 7m7s(copy); streamer: mysql-bin.000100:233867486; Lag: 0.11s, State: migrating; ETA: 41s

进度信息解析：

Copy: 748031/821238 91.1%; 821238表示从行数（非精确，估算值），748031指已经迁移的行数，91.1%指迁移完成的百分比。
​
Applied: 570000;指在binlog日志中处理的event数量。
​
Backlog: 0/1000，表示在读取二进制日志方面表现良好，在二进制日志队列中没有任何积压（Backlog）事件。 如Backlog: 7/1000，当复制行时，在二进制日志中积压了一些事件，并且需要应用。 如Backlog: 1000/1000，表示缓冲区已满（固定1000个事件缓冲区），此时就表明binlog写入量非常大，gh-ost处理不过来event了，如长时间都保持1000/1000，则gh-ost任务就一直结束不了。
​
Time: 7m7s(total), 7m7s(copy);表示总共已执行了7m7s，copy表已执行7m7s。
​
streamer: mysql-bin.000100:233867486;表示当前已经应用到binlog文件位置
​
ETA: 41s表示估算的剩余时间，实际测试时发现该值经常不准确

5.附：全部参数

-aliyun-rds:如在 Aliyun RDS上执行需要设置为true。
-allow-master-master:是否允许gh-ost运行在双主复制架构中
-allow-nullable-unique-key:允许gh-ost在数据迁移依赖的唯一键可以为NULL，默认为不允许为NULL的唯一键。如果数据迁移(migrate)依赖的唯一键允许NULL值，则可能造成数据不正确。
-allow-on-master:允许gh-ost直接运行在主库上。默认gh-ost连接的从库。
-alter string:DDL语句
-approve-renamed-columns ALTER:如果你修改一个列的名字，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的，除非提供-approve-renamed-columns ALTER。
-ask-pass:MySQL密码
-assume-master-host string:为gh-ost指定一个主库，格式为”ip:port”或者”hostname:port”。在这主主架构里比较有用，或则在gh-ost发现不到主的时候有用。
-assume-rbr:确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。
-check-flag:检查是否存在另一个标志文件
-chunk-size int:在每次迭代中处理的行数量(允许范围：100-100000)，默认值为1000。
-concurrent-rowcount:该参数如果为True(默认值)，则进行row-copy之后，估算统计行数(使用explain select count(*)方式)，并调整ETA时间，否则，gh-ost首先预估统计行数，然后开始row-copy。
-conf string:gh-ost的配置文件。
-critical-load string:一系列逗号分隔的status-name=values组成，当MySQL中status超过对应的values，gh-ost将会退出。
-critical-load-hibernate-seconds int :负载达到critical-load时，gh-ost在指定的时间内进入休眠状态。 不会读/写任何来自任何服务器的任何内容。
-critical-load-interval-millis int:当值为0时，当达到-critical-load，gh-ost立即退出。当值不为0时，当达到-critical-load，gh-ost会在-critical-load-interval-millis秒数后，再次进行检查，再次检查依旧达到-critical-load，gh-ost将会退出。
-cut-over string:选择cut-over类型:(default|atomic, two-step) (默认 “atomic”)。
-cut-over-exponential-backoff
-cut-over-lock-timeout-seconds int:gh-ost在cut-over阶段最大的锁等待时间，当锁超时时，gh-ost的cut-over将重试。(默认值：3)
-database string:数据库名。
-debug:debug模式。
-default-retries int:各种操作在panick前重试次数。(默认为60)
-discard-foreign-keys:该参数针对一个有外键的表，在gh-ost创建ghost表时，并不会为ghost表创建外键。谨慎使用。
-dml-batch-size int:在单个事务中应用DML事件的批量大小(范围1-100)(默认值为10)
-exact-rowcount:准确统计表行数(使用select count(*)的方式)，得到更准确的预估时间。
-execute:实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定-execute
-exponential-backoff-max-interval int:执行指数后退的各种操作时，两次尝试之间等待的最大秒数。(默认值64)
-force-named-cut-over:如果为true，则’unpostpone | cut-over’交互式命令必须命名迁移的表
-force-named-panic:如果为true，则’panic’交互命令必须命名迁移的表
-force-table-names string:在临时表上使用的表名前缀
-gcp:在第一代Google Cloud Platform(GCP)上执行时设置为“true”。
-heartbeat-interval-millis int:gh-ost心跳频率值(默认值100)
-help
-hooks-hint string:arbitrary message to be injected to hooks via GH_OST_HOOKS_HINT, for your convenience
-hooks-hint-owner string:arbitrary name of owner to be injected to hooks via GH_OST_HOOKS_HINT_OWNER, for your convenience
-hooks-hint-token string:arbitrary token to be injected to hooks via GH_OST_HOOKS_HINT_TOKEN, for your convenience
-hooks-path string:directory where hook files are found (default: empty, ie. hooks disabled). Hook files found on this path, and conforming to hook naming conventions will be executed
-host string:MySQL hostname(默认值127.0.0.1)
-initially-drop-ghost-table:gh-ost操作之前，检查并删除已经存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
-initially-drop-old-table:gh-ost操作之前，检查并删除已经存在的旧表。默认不启用该参数，gh-ost直接退出操作。
-initially-drop-socket-file:gh-ost强制删除已经存在的socket文件。该参数不建议使用，可能会删除一个正在运行的gh-ost程序，导致DDL失败。
-master-password string :MySQL主库密码
-master-user string:MysQL主库账号
-max-lag-millis int:主从复制最大延迟时间，当主从复制延迟时间超过该值后，gh-ost将采取节流(throttle)措施。(默认值1500)
-max-load string:逗号分隔状态名称=阈值，如：’Threads_running=100,Threads_connected=500’。当状态超过阈值时，应用程序将限制写入。
-migrate-on-replica:gh-ost的数据迁移(migrate)运行在从库上，而不是主库上。
-nice-ratio float:每次chunk时间段的休眠时间，范围[0.0…100.0]。0：每个chunk时间段不休眠，即一个chunk接着一个chunk执行；1：每row-copy 1毫秒，则另外休眠1毫秒；0.7：每row-copy 10毫秒，则另外休眠7毫秒。
-ok-to-drop-table:gh-ost操作结束后，是否删除旧表，默认不删除。
-panic-flag-file string:当这个文件被创建，gh-ost将会立即退出。
-password string :MySQL密码
-port int ：MySQL端口
-postpone-cut-over-flag-file string：当这个文件存在的时候，gh-ost的cut-over阶段将会被推迟，数据仍然在复制，直到该文件被删除。
-quiet：静默模式。
-replica-server-id uint : gh-ost的server_id
-replication-lag-query string:弃用
-serve-socket-file string：socket文件。
-serve-tcp-port int:gh-ost使用端口，默认关闭。
-skip-foreign-key-checks:确定你的表上没有外键时，设置为’true’，并且希望跳过gh-ost验证的时间-skip-renamed-columns ALTER
-skip-renamed-columns ALTER：如果修改一个列名(如change column)，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的。此标志告诉gh ost跳过重命名的列，即将ghost认为重命名的列视为不相关的列。可能会丢失列数据。
-skip-strict-mode：显式指定gh-ost不要使用strict sql mode
-ssl：Enable SSL encrypted connections to MySQL hosts
-ssl-allow-insecure：Skips verification of MySQL hosts’ certificate chain and host name. Requires -ssl
-ssl-ca string：CA certificate in PEM format for TLS connections to MySQL hosts. Requires -ssl
-ssl-cert string：Certificate in PEM format for TLS connections to MySQL hosts. Requires -ssl
-ssl-key string：Key in PEM format for TLS connections to MySQL hosts. Requires -ssl
-stack:添加错误堆栈追踪。
-switch-to-rbr:让gh-ost自动将从库的binlog_format转换为ROW格式。
-table string:表名
-test-on-replica：在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，原表和ghost表立刻交换而后立刻交换回来。继续保持stop slave，使你可以对比两张表。
-test-on-replica-skip-replica-stop:当-test-on-replica执行时，该参数表示该过程中不用stop slave。
-throttle-additional-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候，创建一个文件，让所有的gh-ost操作停止，或者删除这个文件，让所有的gh-ost操作恢复。
-throttle-control-replicas string:列出所有需要被检查主从复制延迟的从库。
-throttle-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。
-throttle-http string：gh-ost检查指定的URL，如果返回值不为200，gh-ost操作立即停止。
-throttle-query string:节流查询。每秒钟执行一次。当返回值=0时不需要节流，当返回值>0时，需要执行节流操作。该查询会在数据迁移(migrated)服务器上操作，所以请确保该查询是轻量级的。
-timestamp-old-table:在旧表名中使用时间戳。 这会使旧表名称具有唯一且无冲突的交叉迁移
-tungsten：告诉gh-ost正在运行的是一个tungsten-replication拓扑结构。
-user string :MYSQL用户
-verbose
-version