gh-ost原理及简单使用

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 , ghosttbl互换, 然后 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 都失败, 也没有问题: LOCKRENAME 都被释放. 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