mysql ddl工具之 gh-ost 介绍

一、概况

gh-ost（GitHub's Online Schema Transmogrifier/Transfigurator/Transformer/Thingy 的缩写，意思是 GitHub 的在线表定义转换器。）是mysql的一个大表ddl工具，类似于pt-osc工具。

二、下载安装

1、下载

下载地址：https://github.com/github/gh-ost/releases/

根据自己的操作系统（amd64或者是arm64）选择安装包下载，也可以下载rpm包进行安装

2、安装

tar xzvf gh-ost-binary-linux-amd64-20241219160321.tar.gz -C /usr/local/    # 解压后只有一个二进制文件gh-ost,就可以使用了

ln -s /usr/local/gh-ost /usr/bin/gh-ost     # 软链到/usr/bin下

# 验证是否安装成功
[root@iZuf6c0ayfwv04hf5r4wa8Z soft]# gh-ost --version
1.1.7 (git commit: d5ab048c1f046821f3c7384a386fc1c3ae399c92)

三、使用

3.1、参数介绍

Usage of gh-ost:
  --aliyun-rds=true
    	set to 'true' when you execute on Aliyun RDS.
  
  --allow-master-master
    	允许 gh-ost 在主主复制（双主互备）的 MySQL 环境中执行在线表结构迁移。默认情况下，gh-ost 会检测到主主复制拓扑并拒绝执行
  
  --allow-nullable-unique-key
    	在 MySQL 中，唯一键（Unique Key）允许包含多个 NULL 值（不同于 PRIMARY KEY 要求所有值非空且唯一）。而 gh-ost 默认会拒绝迁移包含可空唯一键的表，因为这类键可能导致 ghost 表在复制过程中出现数据冲突或不一致。--allow-nullable-unique-key 参数用于强制 gh-ost 允许迁移包含可空唯一键的表，但需要用户自行承担潜在风险。
  
  --allow-on-master
    	默认情况下，gh-ost 会选择在一个从库上执行ddl操作，--allow-on-master 参数用于强制 gh-ost 在主库上执行迁移操作，即使检测到当前实例是主库。这个参数一般要加上，因为如果在从库上执行ddl操作执行完成后还得进行主从切换操作，多此一举了。

  --allow-zero-in-date
    	在 MySQL 中，零日期值指的是 '0000-00-00' 或 '0000-00-00 00:00:00'。这类值在严格 SQL 模式下是非法的，但在非严格模式下可能存在于历史数据中。--allow-zero-in-date 参数用于允许 gh-ost 在迁移过程中处理包含零日期值的行，避免因这类值导致迁移失败。

  --alter='alter talbe ...' 
    	后面跟要执行的ddl语句，注意这里需要写完整的alter语句，和pt-osc不一样

  --approve-renamed-columns 
    	默认情况下，gh-ost 会拒绝执行包含列重命名的 DDL 操作，因为列重命名可能导致复制过程中的数据不一致或解析错误。--approve-renamed-columns 参数用于强制 gh-ost 允许列重命名操作

  --ask-pass
    	该参数控制不用在命令行中提供mysql密码

  --assume-master-host='ip:port'
    	默认情况下，gh-ost 会自动检测从库的主库地址（通过 SHOW SLAVE STATUS 中的 Master_Host）,但在主主复制或级联复制环境中，需要明确指定主库

  --assume-rbr
    	用于强制 gh-ost 假设主库使用基于行的复制（ROW-based replication，RBR）模式，即使实际环境可能配置为基于语句的复制（SBR）或混合模式（MIXED）。该参数主要用于兼容某些特殊场景，避免 gh-ost 因检测到非 RBR 模式而拒绝执行。

  --attempt-instant-ddl
    	用于尝试使用 MySQL 的 Instant DDL 特性来执行某些类型的 DDL 操作，无需重建表，若变更不支持 Instant DDL，gh-ost 会自动回退到传统方式

  --azure=true|false
    	set to 'true' when you execute on Azure Database on MySQL.

  --binlogsyncer-max-reconnect-attempts=0
    	在 gh-ost 的工作流程中，需要从 MySQL 主库持续读取 binlog以同步数据变更到影子表。若连接主库的过程中出现网络波动或临时故障，gh-ost 会尝试重新连接。
-binlogsyncer-max-reconnect-attempts 用于指定最大重试次数，超过该次数后 gh-ost 将终止迁移并报错。 0 是不限制

  --charset=utf8mb4
    	The default charset for the database connection is utf8mb4, utf8, latin1. (default "utf8mb4,utf8,latin1")

  --check-flag
    	Check if another flag exists/supported. This allows for cross-version scripting. Exits with 0 when all additional provided flags exist, nonzero otherwise. You must provide (dummy) values for flags that require a value. Example: gh-ost --check-flag --cut-over-lock-timeout-seconds --nice-ratio 0

  --chunk-size=1000
    	amount of rows to handle in each iteration (allowed range: 10-100,000) (default 1000)

  --concurrent-rowcount
    	(with --exact-rowcount), when true (default): count rows after row-copy begins, concurrently, and adjust row estimate later on; when false: first count rows, then start row copy (default true)

  --conf string
    	Config file

  --max-load string
    	Comma delimited status-name=threshold. e.g: 'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes

  --critical-load string
        格式：--max-load=指标名=阈值[,指标名=阈值...]
    	定义数据库负载阈值，当活跃线程数（threads_running）,qps等超过设置的阈值，gh-ost会退出

  

  -critical-load-hibernate-seconds int
    	When non-zero, critical-load does not panic and bail out; instead, gh-ost goes into hibernation for the specified duration. It will not read/write anything from/to any server

  --critical-load-interval-millis int
    	当为0时，迁移在达到临界负载后立即退出。当非零时，在给定的时间间隔后进行第二次检查，只有当第二次检测仍然满足临界负载时，迁移才会退出

  --cut-over string
      choose cut-over type (default|atomic, two-step) (default "atomic")

  -cut-over-exponential-backoff
    	Wait exponentially longer intervals between failed cut-over attempts. Wait intervals obey a maximum configurable with 'exponential-backoff-max-interval').
  --cut-over-lock-timeout-seconds int
    	Max number of seconds to hold locks on tables while attempting to cut-over (retry attempted when lock exceeds timeout) or attempting instant DDL (default 3)

  -database string
    	database name (mandatory)
  -debug
    	debug mode (very verbose)

  --default-retries int
    	该参数定义了 gh-ost 在执行各类操作（如数据复制、表切换）失败时的最大重试次数(default 60)

  -discard-foreign-keys
    	DANGER! This flag will migrate a table that has foreign keys and will NOT create foreign keys on the ghost table, thus your altered table will have NO foreign keys. This is useful for intentional dropping of foreign keys
  --dml-batch-size int
    	该参数决定了 gh-ost 在将数据从原表复制到 ghost 表时，每次批量插入的行数。(range 1-100) (default 10)

  --exact-rowcount
    	实际计算表的行数（select count 方式），而不是估算，数据量大时会非常耗时

  -execute
    	actually execute the alter & migrate the table. Default is noop: do some tests and exit
  -exponential-backoff-max-interval int
    	Maximum number of seconds to wait between attempts when performing various operations with exponential backoff. (default 64)
  -force-named-cut-over
    	When true, the 'unpostpone|cut-over' interactive command must name the migrated table
  -force-named-panic
    	When true, the 'panic' interactive command must name the migrated table
  -force-table-names string
    	table name prefix to be used on the temporary tables
  -gcp
    	set to 'true' when you execute on a 1st generation Google Cloud Platform (GCP).

  --heartbeat-interval-millis int
    	how frequently would gh-ost inject a heartbeat value (default 100)

  -help
    	Display usage
  -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
  -hooks-status-interval int
    	how many seconds to wait between calling onStatus hook (default 60)
  -host string
    	MySQL hostname (preferably a replica, not the master) (default "127.0.0.1")
  -ignore-http-errors
    	ignore HTTP connection errors during throttle check
  -initially-drop-ghost-table
    	Drop a possibly existing Ghost table (remains from a previous run?) before beginning operation. Default is to panic and abort if such table exists
  -initially-drop-old-table
    	Drop a possibly existing OLD table (remains from a previous run?) before beginning operation. Default is to panic and abort if such table exists

  --initially-drop-socket-file
    	gh-ost 通过 Unix Domain Socket（如 /tmp/gh-ost.sock）提供 HTTP 接口，用于监控和控制，若前一次运行异常退出，可能残留 socket 文件，导致新进程无法绑定。该参数主要作用是在启动时清理可能存在的历史残留文件

  -master-password string
    	MySQL password on master, if different from that on replica. Requires --assume-master-host
  -master-user string
    	MySQL user on master, if different from that on replica. Requires --assume-master-host

  --max-lag-millis int
    	该参数定义了 gh-ost 在检测到从库复制延迟超过指定阈值（毫秒）时的行为，当超过后gh-ost会暂停，等到延迟恢复小于该阈值后gh-ost自动恢复，无需人工干预(default 1500)

  -migrate-on-replica
    	Have the migration run on a replica, not on the master. This will do the full migration on the replica including cut-over (as opposed to --test-on-replica)
  -mysql-timeout float
    	Connect, read and write timeout for MySQL
  -nice-ratio float
    	force being 'nice', imply sleep time per chunk time; range: [0.0..100.0]. Example values: 0 is aggressive. 1: for every 1ms spent copying rows, sleep additional 1ms (effectively doubling runtime); 0.7: for every 10ms spend in a rowcopy chunk, spend 7ms sleeping immediately after
  -ok-to-drop-table
    	Shall the tool drop the old table at end of operation. DROPping tables can be a long locking operation, which is why I'm not doing it by default. I'm an online tool, yes?
  -panic-flag-file string
    	when this file is created, gh-ost will immediately terminate, without cleanup
  -password string
    	MySQL password
  -port int
    	MySQL port (preferably a replica, not the master) (default 3306)
  -postpone-cut-over-flag-file string
    	while this file exists, migration will postpone the final stage of swapping tables, and will keep on syncing the ghost table. Cut-over/swapping would be ready to perform the moment the file is deleted.
  -quiet
    	quiet
  -replica-server-id uint
    	server id used by gh-ost process. Default: 99999 (default 99999)
  -replication-lag-query string
    	Deprecated. gh-ost uses an internal, subsecond resolution query
  -serve-socket-file string
    	Unix socket file to serve on. Default: auto-determined and advertised upon startup
  -serve-tcp-port int
    	TCP port to serve on. Default: disabled
  -skip-foreign-key-checks
    	set to 'true' when you know for certain there are no foreign keys on your table, and wish to skip the time it takes for gh-ost to verify that
  -skip-renamed-columns ALTER
    	in case your ALTER statement renames columns, gh-ost will note that and offer its interpretation of the rename. By default gh-ost does not proceed to execute. This flag tells gh-ost to skip the renamed columns, i.e. to treat what gh-ost thinks are renamed columns as unrelated columns. NOTE: you may lose column data
  -skip-strict-mode
    	explicitly tell gh-ost binlog applier not to enforce 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
    	add stack trace upon error
  -storage-engine string
    	Specify table storage engine (default: 'innodb'). When 'rocksdb': the session transaction isolation level is changed from REPEATABLE_READ to READ_COMMITTED. (default "innodb")
  -switch-to-rbr
    	let this tool automatically switch binary log format to 'ROW' on the replica, if needed. The format will NOT be switched back. I'm too scared to do that, and wish to protect you if you happen to execute another migration while this one is running
  -table string
    	table name (mandatory)
  -test-on-replica
    	Have the migration run on a replica, not on the master. At the end of migration replication is stopped, and tables are swapped and immediately swap-revert. Replication remains stopped and you can compare the two tables for building trust
  -test-on-replica-skip-replica-stop
    	When --test-on-replica is enabled, do not issue commands stop replication (requires --test-on-replica)
  -throttle-additional-flag-file string
    	operation pauses when this file exists; hint: keep default, use for throttling multiple gh-ost operations (default "/tmp/gh-ost.throttle")
  -throttle-control-replicas string
    	List of replicas on which to check for lag; comma delimited. Example: myhost1.com:3306,myhost2.com,myhost3.com:3307
  -throttle-flag-file string
    	operation pauses when this file exists; hint: use a file that is specific to the table being altered
  -throttle-http string
    	when given, gh-ost checks given URL via HEAD request; any response code other than 200 (OK) causes throttling; make sure it has low latency response
  -throttle-http-interval-millis int
    	Number of milliseconds to wait before triggering another HTTP throttle check (default 100)
  -throttle-http-timeout-millis int
    	Number of milliseconds to use as an HTTP throttle check timeout (default 1000)
  -throttle-query string
    	when given, issued (every second) to check if operation should throttle. Expecting to return zero for no-throttle, >0 for throttle. Query is issued on the migrated server. Make sure this query is lightweight

  --timestamp-old-table
    	Use a timestamp in old table name. This makes old table names unique and non conflicting cross migrations

  -tungsten
    	explicitly let gh-ost know that you are running on a tungsten-replication based topology (you are likely to also provide --assume-master-host)
  -user string
    	MySQL user

  --verbose
    	--verbose 决定了 gh-ost 在执行过程中输出日志的详细程度，默认值：false（输出基础日志），启用方式：添加 --verbose 或 --verbose=true 参数，输出更详细的执行进度、状态信息和内部操作。

  -version
    	Print version & exit

3.2、两种使用方式

# 第一种，连接从库，在主库上做迁移，这是默认的方式，注意此时--host 等数据库连接参数填的是从库连接信息，这种方式解析的binlog是从库的，除了迁移外的其他操作也是在从库做的，会降低主库的压力，具体如下：
gh-ost --max-load=Threads_running=20 --critical-load=Threads_running=50 --critical-load-interval-millis=5000  --chunk-size=10000 --user=root --password='111111' --host='127.0.0.1' --port=3306 --database="endian" --table="aa" --verbose --alter="alter table aa add column school varchar(10), add column grade varchar(10);" --assume-rbr --cut-over=default --cut-over-lock-timeout-seconds=3 --dml-batch-size=10 --concurrent-rowcount --default-retries=10 --heartbeat-interval-millis=2000 --max-lag-millis=10000 --timestamp-old-table --initially-drop-socket-file  --execute 


# 第二种，连接主库，迁移过程所有操作都是在主库完成，主库压力会较大一些，这种方式适合没有从库，或者有从库但就是想直接用主库，注意此时--host等参数填的要是主库信息，并且要加上--allow-on-master，否则会报错，具体如下：
gh-ost --max-load=Threads_running=20 --critical-load=Threads_running=50 --critical-load-interval-millis=5000 --allow-on-master --chunk-size=10000 --user=root --password='111111' --host='127.0.0.1' --port=3306 --database="endian" --table="aa" --verbose --alter="alter table aa add column school varchar(10), add column grade varchar(10);" --assume-rbr --cut-over=default --cut-over-lock-timeout-seconds=3 --dml-batch-size=10 --concurrent-rowcount --default-retries=10 --heartbeat-interval-millis=2000 --max-lag-millis=10000 --timestamp-old-table --initially-drop-socket-file --execute