专栏首页ShyNodesgh-ost工具Online-ddl简单操作记录
原创

gh-ost工具Online-ddl简单操作记录

主从架构

192.168.175.206

192.168.175.207

192.168.175.208

ghost配置文件(可以不加)

在207上添加

[root@localhost ~]# vi ghost.conf
[client]
user=mha
password=123

命令如下

[root@test-206 ~]#  gh-ost  --assume-master-host='192.168.175.206:3306' --master-user='mha' --master-password='123' --user='mha' --password='123' --host='192.168.175.207' --port=3306  --alter="ADD COLUMN c2 varchar(120)"   --database=sbtest --table="sbtest1" -execute --initially-drop-old-table --initially-drop-socket-file --initially-drop-ghost-table

执行过程日志输出如下:

[2021/07/28 16:02:21] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 192.168.175.207 3306 mha    false false <nil> false UTC true 0 0s 0s 0 false}
[2021/07/28 16:02:21] [info] binlogsyncer.go:354 begin to sync binlog from position (bin.000006, 7865)
[2021/07/28 16:02:21] [info] binlogsyncer.go:203 register slave for master server 192.168.175.207:3306
[2021/07/28 16:02:21] [info] binlogsyncer.go:723 rotate to (bin.000006, 7865)
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating test-206:3306; inspecting test-207:3306; executing on test-206
# Migration started at Wed Jul 28 16:02:20 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 0/1 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: bin.000006:10318; Lag: 0.01s, State: migrating; ETA: N/A
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: bin.000006:10318; Lag: 0.01s, State: migrating; ETA: due
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating test-206:3306; inspecting test-207:3306; executing on test-206
# Migration started at Wed Jul 28 16:02:20 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: bin.000006:16186; Lag: 0.01s, State: migrating; ETA: due
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: bin.000006:16186; Lag: 0.01s, State: migrating; ETA: due
[2021/07/28 16:02:22] [info] binlogsyncer.go:164 syncer is closing...
[2021/07/28 16:02:22] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/07/28 16:02:22] [info] binlogsyncer.go:179 syncer is closed
# Done

查看表DDL更改情况

206[sbtest]>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| _sbtest1_del     |
| sbtest1          |
+------------------+
2 rows in set (0.00 sec)

206[sbtest]>show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                                                 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '3',
  `b` int(11) DEFAULT '4',
  `d` varchar(44) NOT NULL DEFAULT 's',
  `c2` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看从库207上是否正常更改

207 [sbtest]>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| _sbtest1_del     |
| sbtest1          |
+------------------+
2 rows in set (0.00 sec)

207 [sbtest]>show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                                                 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '3',
  `b` int(11) DEFAULT '4',
  `d` varchar(44) NOT NULL DEFAULT 's',
  `c2` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### _sbtest1_del 在确认sbtest1已经正确更改以后无任何报错前提下,手动删除即可

附gh-ost命令参数:

[root@test-206 ~]# gh-ost --help
Usage of gh-ost:
  -aliyun-rds
        set to 'true' when you execute on Aliyun RDS.
  -allow-master-master
        explicitly allow running in a master-master setup
  -allow-nullable-unique-key
        allow gh-ost to migrate based on a unique key with nullable columns. As long as no NULL values exist, this should be OK. If NULL values exist in chosen key, data may be corrupted. Use at your own risk!
  -allow-on-master
        allow this migration to run directly on master. Preferably it would run on a replica
  -alter string
        alter statement (mandatory)
  -approve-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 approves that gh-ost's interpretation is correct
  -ask-pass
        prompt for MySQL password
  -assume-master-host string
        (optional) explicitly tell gh-ost the identity of the master. Format: some.host.com[:port] This is useful in master-master setups where you wish to pick an explicit master, or in a tungsten-replicator where gh-ost is unable to determine the master
  -assume-rbr
        set to 'true' when you know for certain your server uses 'ROW' binlog_format. gh-ost is unable to tell, event after reading binlog_format, whether the replication process does indeed use 'ROW', and restarts replication to be certain RBR setting is applied. Such operation requires SUPER privileges which you might not have. Setting this flag avoids restarting replication and you can proceed to use gh-ost without SUPER privileges
  -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 int
        amount of rows to handle in each iteration (allowed range: 100-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
  -critical-load string
        Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits
  -critical-load-hibernate-seconds int
        When nonzero, critical-load does not panic and bail out; instead, gh-ost goes into hibernate for the specified duration. It will not read/write anything to from/to any server
  -critical-load-interval-millis int
        When 0, migration immediately bails out upon meeting critical-load. When non-zero, a second check is done after given interval, and migration only bails out if 2nd check still meets critical load
  -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) (default 3)
  -database string
        database name (mandatory)
  -debug
        debug mode (very verbose)
  -default-retries int
        Default number of retries for various operations before panicking (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
        batch size for DML events to apply in a single transaction (range 1-100) (default 10)
  -exact-rowcount
        actually count table rows as opposed to estimate them (results in more accurate progress estimation)
  -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
  -host string
        MySQL hostname (preferably a replica, not the master) (default "127.0.0.1")
  -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
        Should gh-ost forcibly delete an existing socket file. Be careful: this might drop the socket file of a running migration!
  -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
        replication lag at which to throttle operation (default 1500)
  -max-load string
        Comma delimited status-name=threshold. e.g: 'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes
  -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)
  -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
  -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-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
  -version
        Print version & exit

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • DM 源码阅读系列文章(八)Online Schema Change 同步支持

    本文为 DM 源码阅读系列文章的第八篇,上篇文章 对 DM 中的定制化数据同步功能进行详细的讲解,包括库表路由(Table routing)、黑白名单(Blac...

    PingCAP
  • MySQL 最佳实践:gh-ost 工具使用详解

    MySQL 的同步机制比较单纯,主库上执行过的 DML 和 DDL 会在从库上再执行一次,那么主库上需要 10min 才能执行完的 DDL 理论上在从库至少也要...

    王文安@DBA
  • MySQL DDL Online Schema Change—gh-ost介绍

    gh-ost是针对MySQL对主库影响很小,无trigger的online schema change解决方案。采用消费binlog的方式来代替trigger方...

    MySQL轻松学
  • GitHub开源的MySQL在线更改Schema工具

    Rainbond开源
  • MySQL 5.7 特性:Online DDL

    DDL 一向是业务的痛点,尤其是对大型表的 DDL 操作,具有操作时间久,对性能影响大,可能影响业务正常使用等问题。

    王文安@DBA
  • gh-ost 在线ddl变更工具​

    作为MySQL DBA,相信我们大家都会对大表变更(大于10G 以上的)比较头疼,尤其是某些DDL会锁表,影响业务可持续性。目前通用的方案使用Percona 公...

    田帅萌
  • gh-ost 在线ddl变更工具​

    作为MySQL DBA,相信我们大家都会对大表变更(大于10G 以上的)比较头疼,尤其是某些DDL会锁表,影响业务可持续性。目前通用的方案使用Percona 公...

    用户1278550
  • MySQL在线DDL修改表结构的简单经验分享

    摘 要 在线DDL修改生产环境的大表一直是运维、DBA一个很头痛的问题,本文分享一些相关经验,希望对还在头痛的同学能有所帮助,当然更希望路过的大神,如果有更靠...

    张戈
  • Online DDL 工具 gh-ost实战(一)

    2020年了,开始正文前,先说几句废话,2019年公众号开始正式更文,后期由于个人问题停更了,2020年开始重新开始更文,更文目的很简单,记录自己的学习,分享给...

    SEian.G
  • MySQL在线DDL工具 gh-ost

    gh-ost基于 golang 语言,是 github 开源的一个 DDL 工具,是 GitHub's Online Schema Transmogrifier...

    东山絮柳仔
  • 技术分享 | Online DDL 工具 pt-osc

    爱可生 DBA 团队成员,主要负责 MySQL 故障处理和公司自动化运维平台维护。对技术执着,为客户负责。

    爱可生开源社区
  • Vitess online DDL介绍

    Vitess 引入了一种运行模式迁移的新方法:非阻塞的、异步的、预定的online DDL。通过 online DDL,Vitess 简化了模式迁移过程,它获得...

    CNCF
  • Github推荐:MySQL DBA不可错过的五大开源管理工具!

    对于数据库管理员(DBA)来说,保持数据库运行在最佳状态需要具备敏捷,专注,快速反应的能力以及一颗冷静的头脑。数据库几乎是所有应用程序成功运行的核心,由于DBA...

    企鹅号小编
  • gh-ost:在线DDL修改MySQL表结构工具

    在之前,我分享过一次 pt-online-schema-change 在线 DDL 的工具实践记录,在实际使用过程中,发现部门的很多老系统大量使用了触发器,从而...

    张戈
  • MySQL 8.0.19亿级数据如何秒速增加字段?

    今天主要介绍一下MySQL 8.0.19 instant add column的新特性,基于亿级数据秒速增加字段,下面一起来看看吧~

    IT大咖说
  • MySQL add/drop字段时报主键冲突

    错误提示是主键冲突,但是当我们去查询 id= 7458421 时,并无此记录。是不是很奇怪?遇到这种情况,一般有如下场景:

    用户1278550
  • Online DDL 工具 gh-ost原理(二)

    接上一篇文章Online DDL 工具 gh-ost实战(一),介绍了gh-ost的实际使用案例,本文介绍一下gh-osh的相关原理;

    SEian.G
  • gh-ost 学习笔记

    https://m.aliyun.com/yunqi/articles/62928

    二狗不要跑
  • gh-ost 原理剖析

    上一篇文章 介绍 gh-ost 参数和具体的使用方法,以及核心特性-可动态调整 暂停,动态修改参数等等。本文分几部分从源码方面解释gh-ost的执行过程,数据迁...

    用户1278550

扫码关注云+社区

领取腾讯云代金券