一、背景
丁奇大佬推荐的一个在线DDL工具,使用Go语言编写。gh-ost是一款无需添加触发器、可测试、可暂停、动态控制(再配置)、可审计的MySQL结构在线迁移工具。gh-ost github地址
二、原理详解
2.1 架构模式
a 连接从库,在主库做迁移(默认)
- 在主库上读写,创建_xxx_gho(目标表)、_xxx_ghc(记录 gh-ost 执行过程的表)。
- 从库上读取二进制日志事件,将变更应用到主库上的_xxx_gho表;
- 在主库上读源表的数据写入_xxx_gho表中:insert into igore…select;
- 在主库上完成表切换;
b 在主库修改
- 在主库上读写,创建_xxx_gho、_xxx_ghc。
- 主库上读取二进制日志事件,将变更应用到主库上的_xxx_gho表;
- 在主库上读源表的数据写入_xxx_gho表中:insert into igore…select;
- 在主库上完成表切换;
c 在从库做迁移
- 这种模式会在从库上做修改。gh-ost仍然会连上主库,但所有操作都是在从库上做的,不会对主库产生任何影响。
2.2 执行过程
图片来自于茁壮的小草
2.2 CentOS上安装
# 下载Github上的最新版本二进制包
wget https://github.com/github/gh-ost/releases/download/v1.1.2/gh-ost-binary-linux-20210617134741.tar.gz
# 解压
tar -zxvf gh-ost-binary-linux-20210617134741.tar.gz
# 为方便后续操作,将其拷贝到系统的可执行目录
cp gh-ost /usr/local/bin/
# 校验安装成果
gh-ost -h
2.3选项说明
选项 | 说明 |
---|---|
-user string | MySQL用户 |
-password string | MySQL密码 |
-host string | MySQL连接地址 |
-database string | 指定数据库名(强制) |
-port int | MySQL端口 |
-table string | 指定表名(强制) |
-aliyun-rds | true; 是否再阿里云的RDS上执行 |
-allow-master-master | 是否在双主架构上使用,一般与-assume-master-host参数一起使用 |
-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 rename语义是否正确 。默认不会执行,加上此选项才可以 |
-ask-pass | 提示输入MySQL密码 |
-assume-master-host string | 为gh-ost指定一个主库,格式为”ip:port”或者”hostname:port”。 |
-assume-rbr | 确认gh-ost连接的数据库实例的binlog_format=ROW的情况下,可以指定-assume-rbr,这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。 |
-check-flag | 检验其他标志 |
-chunk-size int | 1000;在每次迭代中处理的行数量(允许范围:100-100000) |
-concurrent-rowcount | 该参数如果为True(默认值),则进行row-copy之后,估算统计行数(使用explain select count(*)方式),并调整ETA时间,否则,gh-ost首先预估统计行数,然后开始row-copy |
-conf string | gh-ost支持将操作指令存储于文件,并通过此选项加载。类似于awk -f |
-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类型:atomic/two-step,atomic(默认)类型的cut-over是github的算法,two-step采用的是facebook-OSC的算法。 |
-cut-over-exponential-backoff | 在cut-over的失败尝试之间等待的时间间隔呈指数级增长,最大等待exponential-backoff-max-interval的值 |
-exponential-backoff-max-interval int | 64;进行指数回退尝试的时候,最大的秒数 |
-cut-over-lock-timeout-seconds int | 3;尝试cut-over过程中持有表锁的最大时间(s) |
-debug | 开启debug模式 |
-default-retries int | 60;异常之后默认的重试次数 |
-discard-foreign-keys | 丢弃表的外键(危险) |
-dml-batch-size int | 10;单个事务批处理事件数(1-100) |
-exact-rowcount | 真实计算表行数,会使得估算结果更加精准 |
-execute | gh-ost默认仅仅时做一些测试就退出了,可以用此选项执行真正的DDL操作 |
-force-named-cut-over | 如果为true,则’unpostpone |
-force-named-panic | 如果为true,‘panic’ 交互式命令必须命名迁移的表 |
-force-table-names string | 临时表的前缀 |
-heartbeat-interval-millis int | 100; gh-ost心跳频率值,默认为500 |
-hooks-hint string | 任意消息通过 GH_OST_HOOKS_HINT 注入到钩子 |
-hooks-hint-owner string | 任意用户通过 GH_OST_HOOKS_HINT_OWNER 注入到钩子 |
-hooks-hint-token string | 任意token通过 GH_OST_HOOKS_HINT_TOKEN 注入到钩子 |
-hooks-path string | hook文件存放目录(默认为empty,即禁用hook) |
-ignore-http-errors | 限流检测的时候忽略http错误 |
-initially-drop-ghost-table | gh-ost操作之前,检查并删除已经存在的ghost表。该参数不建议使用,请手动处理原来存在的ghost表。默认不启用该参数,gh-ost直接退出操作。 |
-initially-drop-old-table | :gh-ost操作之前,检查并删除已经存在的旧表。该参数不建议使用,请手动处理原来存在的ghost表。默认不启用该参数,gh-ost直接退出操作。 |
-initially-drop-socket-file | gh-ost强制删除已经存在的socket文件。该参数不建议使用,可能会删除一个正在运行的gh-ost程序,导致DDL失败。 |
-master-password string | MySQL主库 密码 |
-master-user string | MySQL主库 用户 |
-max-lag-millis int | 1500s;主从复制最大延迟时间,当主从复制延迟时间超过该值后,gh-ost将采取节流(throttle)措施。 |
-max-load string | 逗号分隔状态名称=阈值,如:‘Threads_running=100,Threads_connected=500’ |
-migrate-on-replica | gh-ost的数据迁移(migrate)运行在从库上,而不是主库上。 |
-mysql-timeout float | 连接、读写的超时时间 |
-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操作结束后,删除旧表,默认状态是不删除旧表,会存在_tablename_del表。 |
-panic-flag-file string | 当文件被创建,gh-ost将会立即退出。 |
-postpone-cut-over-flag-file string | 当这个文件存在的时候,gh-ost的cut-over阶段将会被无限推迟,数据仍然在复制,直到该文件被删除。用来控制是否进行rename操作 |
–quiet | 静默模式 |
-replica-server-id uint | gh-ost的server_id |
-serve-socket-file string | gh-ost的socket文件绝对路径 |
-serve-tcp-port int | 默认disabled; 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跳该列的数据迁移,让gh-ost把重命名列作为无关紧要的列。该操作很危险,你会损失该列的所有值。 |
-stack | 添加错误堆栈追踪。 |
-skip-strict-mode | 忽略SQL严格模式 |
-ssl | 开启SSL连接MySQL |
-ssl-allow-insecure | 忽略MySQL 证书链验证,要开启–ssl |
-ssl-ca string | CA 证书 ,要开启–ssl |
-ssl-cert | 证书,要开启–ssl |
-ssl-key string | 证书密钥,要开启–ssl |
-switch-to-rbr | 让gh-ost自动将从库的binlog_format转换为ROW格式。 |
-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 | 默认"/tmp/gh-ost.throttle";当该文件被创建后,gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候,创建一个文件,让所有的gh-ost操作停止,或者删除这个文件,让所有的gh-ost操作恢复。 |
-throttle-control-replicas string | 列出所有需要被检查主从复制延迟的从库,如:myhost1.com:3306,myhost2.com,myhost3.com:3307 |
-throttle-flag-file string | 当该文件被创建后,gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。 |
-throttle-http string | 当给定URL之后,gh-ost会对其发出HEAD请求,响应码为200则会触发限流。确保该地址低延时 |
-throttle-query string | 给定查询语句之后会发送给数据库,再根据其结果,若返回0侧不限流,返回值大于0则触发限流。须确保语句足够轻巧,一面造成数据库压力 |
三、实践
3.1 数据模型
源表
CREATE TABLE `cms_test_ghost` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT "ID",
`createtime` int(10) NOT NULL DEFAULT 0 COMMENT "createtime",
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='gh-ost test';
存储过程生成数据
# 生成模拟数据,入参n是多少就插入多少条数据
DELIMITER $$
CREATE PROCEDURE `idata`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n) DO
INSERT INTO cms_test_ghost(createtime) VALUES (unix_timestamp(now()));
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
# 调用存储过程
CALL idata(500000);
查看数据
mysql> desc cms_test_ghost;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| createtime | int(10) | NO | | 0 | |
+------------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from cms_test_ghost limit 10;
+----+------------+
| id | createtime |
+----+------------+
| 1 | 1634559148 |
| 2 | 1634559148 |
| 3 | 1634559148 |
| 4 | 1634559148 |
| 5 | 1634559148 |
| 6 | 1634559148 |
| 7 | 1634559148 |
| 8 | 1634559148 |
| 9 | 1634559148 |
| 10 | 1634559148 |
+----+------------+
10 rows in set (0.00 sec)
3.2 工具运用
gh-ost \
--allow-on-master \
--max-load=Threads_running=20 \
--critical-load=Threads_running=50 \
--critical-load-interval-millis=5000 \
--chunk-size=1000 \
--user="root" \
--password="Lfc123654" \
--host='127.0.0.1' \
--port=3306 \
--database="mall" \
--table="cms_test_ghost" \
--verbose \
--alter="ALTER TABLE cms_test_ghost MODIFY COLUMN id bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'ID';" \
--assume-rbr \
--cut-over=default \
--cut-over-lock-timeout-seconds=1 \
--dml-batch-size=10 \
--allow-on-master \
--concurrent-rowcount \
--heartbeat-interval-millis=2000 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute
执行过程
2021-10-24 23:09:38 INFO starting gh-ost 1.1.2
2021-10-24 23:09:38 INFO Migrating `mall`.`cms_test_ghost`
2021-10-24 23:09:38 INFO inspector connection validated on 127.0.0.1:3306
2021-10-24 23:09:38 INFO User has ALL privileges
2021-10-24 23:09:38 INFO binary logs validated on 127.0.0.1:3306
2021-10-24 23:09:38 INFO Inspector initiated on Reseach:3306, version 5.7.35-log
2021-10-24 23:09:39 INFO Table found. Engine=InnoDB
2021-10-24 23:09:39 INFO Estimated number of rows via EXPLAIN: 499592
2021-10-24 23:09:39 INFO Recursively searching for replication master
2021-10-24 23:09:39 INFO Master found to be Reseach:3306
2021-10-24 23:09:39 WARNING log_slave_updates not found on 127.0.0.1:3306, but executing directly on master, so I'm proceeding
2021-10-24 23:09:39 INFO streamer connection validated on 127.0.0.1:3306
2021-10-24 23:09:39 INFO Connecting binlog streamer at mysql-bin.000002:171451370
[2021/10/24 23:09:39] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 127.0.0.1 3306 root false false <nil> false UTC true 0 0s 0s 0 false}
[2021/10/24 23:09:39] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000002, 171451370)
[2021/10/24 23:09:39] [info] binlogsyncer.go:203 register slave for master server 127.0.0.1:3306
2021-10-24 23:09:39 INFO rotate to next log from mysql-bin.000002:0 to mysql-bin.000002
[2021/10/24 23:09:39] [info] binlogsyncer.go:723 rotate to (mysql-bin.000002, 171451370)
2021-10-24 23:09:39 INFO applier connection validated on 127.0.0.1:3306
2021-10-24 23:09:39 INFO applier connection validated on 127.0.0.1:3306
2021-10-24 23:09:39 INFO will use time_zone='SYSTEM' on applier
2021-10-24 23:09:39 INFO Examining table structure on applier
2021-10-24 23:09:39 INFO Applier initiated on Reseach:3306, version 5.7.35-log
2021-10-24 23:09:39 INFO Dropping table `mall`.`_cms_test_ghost_ghc`
2021-10-24 23:09:39 INFO Table dropped
# 创建_ghc表,记录执行过程
2021-10-24 23:09:39 INFO Creating changelog table `mall`.`_cms_test_ghost_ghc`
2021-10-24 23:09:39 INFO Changelog table created
# 创建_gho表,作为目标表(改了表结构后的表)
2021-10-24 23:09:39 INFO Creating ghost table `mall`.`_cms_test_ghost_gho`
2021-10-24 23:09:39 INFO Ghost table created
2021-10-24 23:09:39 INFO Altering ghost table `mall`.`_cms_test_ghost_gho`
2021-10-24 23:09:39 INFO Ghost table altered
2021-10-24 23:09:39 INFO Altering ghost table AUTO_INCREMENT value `mall`.`_cms_test_ghost_gho`
2021-10-24 23:09:39 INFO Ghost table AUTO_INCREMENT altered
2021-10-24 23:09:39 INFO Intercepted changelog state GhostTableMigrated
2021-10-24 23:09:39 INFO Created postpone-cut-over-flag-file: /tmp/ghost.postpone.flag
2021-10-24 23:09:39 INFO Waiting for ghost table to be migrated. Current lag is 0s
2021-10-24 23:09:39 INFO Handled changelog state GhostTableMigrated
2021-10-24 23:09:39 INFO Chosen shared unique key is PRIMARY
2021-10-24 23:09:39 INFO Shared columns are id,createtime
2021-10-24 23:09:39 INFO Listening on unix socket file: /tmp/gh-ost.mall.cms_test_ghost.sock
2021-10-24 23:09:39 INFO Migration min values: [1]
2021-10-24 23:09:39 INFO Migration max values: [500000]
2021-10-24 23:09:39 INFO Waiting for first throttle metrics to be collected
2021-10-24 23:09:39 INFO First throttle metrics collected
# Migrating `mall`.`cms_test_ghost`; Ghost table is `mall`.`_cms_test_ghost_gho`
# Migrating Reseach:3306; inspecting Reseach:3306; executing on Reseach
# Migration started at Sun Oct 24 23:09:38 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=50; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# postpone-cut-over-flag-file: /tmp/ghost.postpone.flag [set]
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.mall.cms_test_ghost.sock
Copy: 0/499592 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000002:171453750; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/499592 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000002:171455034; Lag: 1.01s, HeartbeatLag: 0.00s, State: migrating; ETA: N/A
Copy: 134000/499592 26.8%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000002:173237838; Lag: 1.00s, HeartbeatLag: 1.00s, State: migrating; ETA: 5s
Copy: 279000/499592 55.8%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: mysql-bin.000002:175167826; Lag: 1.00s, HeartbeatLag: 0.00s, State: migrating; ETA: 2s
Copy: 417000/499592 83.5%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 4s(copy); streamer: mysql-bin.000002:176990571; Lag: 1.00s, HeartbeatLag: 1.00s, State: migrating; ETA: due
# 行数据已经复制完成,接下来时监听binlog的变化并应用,如果有binlog写操作,则"Applied"后面的值不为0。
2021-10-24 23:09:43 INFO Row copy complete
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 4s(copy); streamer: mysql-bin.000002:178103874; Lag: 1.00s, HeartbeatLag: 0.58s, State: migrating; ETA: due
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5s(total), 4s(copy); streamer: mysql-bin.000002:178109672; Lag: 1.00s, HeartbeatLag: 0.00s, State: postponing cut-over; ETA: due
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 0/1000; Time: 6s(total), 4s(copy); streamer: mysql-bin.000002:178110594; Lag: 1.00s, HeartbeatLag: 0.00s, State: postponing cut-over; ETA: due
以上输出部分字段 | 说明 |
---|---|
Copy | 拷贝源表中的数据的完成行数(百分比) |
Applied | 在这个处理的时间段内,通过binlog重放到影子表的事件数。 |
Backlog | 用来衡量,处理的队列里边是否有事件堆积。0/100运行良好,100/100过载。 |
streamer | gh-ost正在处理的binlog文件 |
要进行cut-over,则需要删除 rm /tmp/ghost.postpone.flag
......
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 0/1000; Time: 43s(total), 4s(copy); streamer: mysql-bin.000002:178145114; Lag: 1.00s, HeartbeatLag: 0.00s, State: postponing cut-over; ETA: due
2021-10-24 23:10:22 INFO Grabbing voluntary lock: gh-ost.6072.lock
2021-10-24 23:10:22 INFO Setting LOCK timeout as 2 seconds
2021-10-24 23:10:22 INFO Looking for magic cut-over table
2021-10-24 23:10:22 INFO Creating magic cut-over table `mall`.`_cms_test_ghost_del`
2021-10-24 23:10:22 INFO Magic cut-over table created
2021-10-24 23:10:22 INFO Locking `mall`.`cms_test_ghost`, `mall`.`_cms_test_ghost_del`
2021-10-24 23:10:22 INFO Tables locked
2021-10-24 23:10:22 INFO Session locking original & magic tables is 6072
2021-10-24 23:10:22 INFO Writing changelog state: AllEventsUpToLockProcessed:1635088222708249439
2021-10-24 23:10:22 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-10-24 23:10:22 INFO Handled changelog state AllEventsUpToLockProcessed
2021-10-24 23:10:22 INFO Waiting for events up to lock
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 1/1000; Time: 44s(total), 4s(copy); streamer: mysql-bin.000002:178146676; Lag: 1.00s, HeartbeatLag: 1.00s, State: migrating; ETA: due
2021-10-24 23:10:23 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1635088222708249439
2021-10-24 23:10:23 INFO Done waiting for events up to lock; duration=982.377851ms
# Migrating `mall`.`cms_test_ghost`; Ghost table is `mall`.`_cms_test_ghost_gho`
# Migrating Reseach:3306; inspecting Reseach:3306; executing on Reseach
# Migration started at Sun Oct 24 23:09:38 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=20; critical-load: Threads_running=50; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# postpone-cut-over-flag-file: /tmp/ghost.postpone.flag
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.mall.cms_test_ghost.sock
Copy: 500000/500000 100.0%; Applied: 0; Backlog: 0/1000; Time: 44s(total), 4s(copy); streamer: mysql-bin.000002:178147589; Lag: 1.00s, HeartbeatLag: 0.61s, State: migrating; ETA: due
2021-10-24 23:10:23 INFO Setting RENAME timeout as 1 seconds
2021-10-24 23:10:23 INFO Session renaming tables is 6077
2021-10-24 23:10:23 INFO Issuing and expecting this to block: rename /* gh-ost */ table `mall`.`cms_test_ghost` to `mall`.`_cms_test_ghost_del`, `mall`.`_cms_test_ghost_gho` to `mall`.`cms_test_ghost`
2021-10-24 23:10:23 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-10-24 23:10:23 INFO Checking session lock: gh-ost.6072.lock
2021-10-24 23:10:23 INFO Connection holding lock on original table still exists
2021-10-24 23:10:23 INFO Will now proceed to drop magic table and unlock tables
2021-10-24 23:10:23 INFO Dropping magic cut-over table
2021-10-24 23:10:23 INFO Releasing lock from `mall`.`cms_test_ghost`, `mall`.`_cms_test_ghost_del`
2021-10-24 23:10:23 INFO Tables unlocked
2021-10-24 23:10:23 INFO Tables renamed
2021-10-24 23:10:23 INFO Lock & rename duration: 1.008945264s. During this time, queries on `cms_test_ghost` were blocked
[2021/10/24 23:10:23] [info] binlogsyncer.go:164 syncer is closing...
[2021/10/24 23:10:23] [error] binlogsyncer.go:631 connection was bad
[2021/10/24 23:10:23] [error] binlogstreamer.go:77 close sync with err: Sync was closed
2021-10-24 23:10:23 INFO Closed streamer connection. err=<nil>
2021-10-24 23:10:23 INFO Dropping table `mall`.`_cms_test_ghost_ghc`
[2021/10/24 23:10:23] [info] binlogsyncer.go:179 syncer is closed
2021-10-24 23:10:23 INFO Table dropped
2021-10-24 23:10:23 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:
2021-10-24 23:10:23 INFO -- drop table `mall`.`_cms_test_ghost_del`
2021-10-24 23:10:23 INFO Done migrating `mall`.`cms_test_ghost`
2021-10-24 23:10:23 INFO Removing socket file: /tmp/gh-ost.mall.cms_test_ghost.sock
2021-10-24 23:10:23 INFO Tearing down inspector
2021-10-24 23:10:23 INFO Tearing down applier
2021-10-24 23:10:23 INFO Tearing down streamer
2021-10-24 23:10:23 INFO Tearing down throttler
# Done
暂停
#暂停
echo throttle | socat - gh-ost.mall.cms_test_ghost.sock
#恢复
echo no-throttle | socat - gh-ost.mall.cms_test_ghost.sock
修改限速
echo chunk-size=100 | socat - gh-ost.mall.cms_test_ghost.sock
echo max-lag-millis=200 | socat - gh-ost.mall.cms_test_ghost.sock
echo max-load=Thread_running=3 | socat - gh-ost.mall.cms_test_ghost.sock
四、总结
在日常的业务开发过程中,难免需要针对大数据量的表做字段的更改操作。为避免在源表上DDL导致的锁表,暂停业务。我们可以通过新建影子表并拷贝数据,最终切换表名的思路,做到平滑过渡,而gh-ost正是非常符合此场景的工具。
参考文章: