gh-ost ddl在线变更

gh-ost DDL在线变更方案详解

原创于 2025-04-22 10:11:57 发布 · 3.2k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #gh-ost ddl在线变更

开发同时被 2 个专栏收录

67 篇文章

订阅专栏

预研

57 篇文章

订阅专栏

1. 数据库ddl方案：

1.1 Gh-ost

1, gh-ost根据alter内容，新建一张”_源表名_gho”的幽灵表。

2, 将原表内容分批次insert到ghost表（row-copy）。单批次1000条，由参数chunk-size控制。

insert into ghost select * from tab where id >= batchminid and id< batchmin_id+1000;

1, 另外一个线程读取binlog，捕捉该表的dml(binlog-apply)。并应用到ghost表。

2, 最后进行cut-over,锁定原表，待binlog完全追上，进行rename操作，操作完成后，原表将被rename成_tab_del表，该表需要手工删除。

gh-host介绍：https://zhuanlan.zhihu.com/p/83770402

1.2 Pt-osc:

1. 创建一个和要执行 alter 操作的表一样的新的空表，后缀默认是new。

2. 在新表执行alter table 语句，因为是空表，执行速度很快。

3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作。

4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，注意这里是Replace操作。

5. 表明替换将原表名table修改为 tableold, 将tablenew 表明修改为原表名tabl

6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

7. 默认最后将旧原表删除。

1.3 方案的优点：

传统的在线对大表做ddl操作很容易造成数据库服务器的瞬间压力，在表数据量达到千万级时候做增删改非常的耗时。如果瞬间业务操作并发容易出问题。采用目前流程方案可以规避上面的问题。

超过6000W表的结构修改，发现几乎不影响性能，很稳定很流畅的就修改了表结构，所以，对以上常规参数的使用基本能满足业务，一定要在业务低峰期做，这样才能确保万无一失。

2. 项目表结构设计：

2.1, 数据库操作日志表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for uni_table_opt
-- ----------------------------
DROP TABLE IF EXISTS `uni_table_opt`;
CREATE TABLE `uni_table_opt`  (
`id` int(11) NOT NULL,
`class_id` int(11) NULL DEFAULT NULL COMMENT '表名称',
`opt_string` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作脚本',
`opt_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作类型：0 建表 1 添加字段 2 修改字段 3 删除字段 4  添加唯一约束',
`opt_time` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.2，动态建立数据库表

2.3，数据字段表

2.4，资源分类表

3. 主要的流程：

4. 前后端交互：

4.1，前端第一次建表：

前端提供表所有字段，数据类型，数据规范，主键等属性，通过json推送到后端。服务器后端提供一个mysql 的ddl操作的工具类，生成建表语句，执行。

4.2，修改表结构：

修改指定的字段，涉及到字段的名称和字段的类型，通过json推送到后端。服务端基于gh-ost，提供一个修改字段的标准语句。提供给gh-ost，执行。

4.3，删除操作：

删除指定的字段，选择指定字段，通过json推送到后端。服务端基于gh-ost，提供一个删除字段的标准语句。提供给gh-ost，执行。

4.4，添加字段操作：

添加指定的字段，字段名称，类型，描述等，通过json推送到后端。服务端基于gh-ost，提供一个添加字段的标准语句。提供给gh-ost，执行。

4.5，添加唯一约束：

指定添加唯一约束的一个字段还是做多字段的唯一约束，通过json推送到后端。服务端基于gh-ost，提供一个添加唯一约束字段的标准语句。提供给gh-ost，执行。

5. 附录

5.1 主要脚本

#gh-ost

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="student"  --alter="add column adderss varchar(256)"  --ok-to-drop-table  --execute

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="student"  --alter="change adderssss11 adderssss varchar(256)"  --approve-renamed-columns --ok-to-drop-table --execute

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="student"  --alter="drop adderssss11" --ok-to-drop-table   --execute

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="student"  --alter="add unique(name)" --ok-to-drop-table   --execute

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="student"  --alter="add unique index fk_uniq(name, birthday)" --ok-to-drop-table   --execute


#pt-online-schema-change


pt-online-schema-change --charset=utf8 --no-version-check --user=root --password=123456 --host=localhost  P=3306,D=demo,t=user  --alter  "ADD COLUMN adderss varchar(256) DEFAULT NULL"   --execute

time pt-online-schema-change --charset=utf8 --no-version-check --user=root --password=123456 --host=localhost  P=3306,D=demo,t=user  --alter  "change adderss11 address varchar(1024)" --no-check-alter   --execute

5.2 运行日志

Pt-osc：

[root@sc-block-laj ~]# time pt-online-schema-change --charset=utf8 --no-version-check --user=root --password=123456 --host=localhost  P=3306,D=demo,t=user  --alter  "ADD COLUMN adderss11 varchar(256) DEFAULT NULL"   --execute
No slaves found.  See --recursion-method if host sc-block-laj has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `demo`.`user`...
Creating new table...
Created new table demo._user_new OK.
Altering new table...
Altered `demo`.`_user_new` OK.
2019-12-31T16:34:26 Creating triggers...
2019-12-31T16:34:26 Created triggers OK.
2019-12-31T16:34:26 Copying approximately 9712336 rows...
Copying `demo`.`user`:  36% 00:52 remain
Copying `demo`.`user`:  73% 00:21 remain
2019-12-31T16:35:51 Copied rows OK.
2019-12-31T16:35:51 Analyzing new table...
2019-12-31T16:35:51 Swapping tables...
2019-12-31T16:35:51 Swapped original and new tables OK.
2019-12-31T16:35:51 Dropping old table...
2019-12-31T16:35:51 Dropped old table `demo`.`_user_old` OK.
2019-12-31T16:35:51 Dropping triggers...
2019-12-31T16:35:51 Dropped triggers OK.
Successfully altered `demo`.`user`.

Gh-ost:

gh-ost  --user="root" --password="123456" --host=localhost --allow-on-master --database="demo" --table="user" --alter="CHANGE username username11 VARCHAR(256)"  --approve-renamed-columns --ok-to-drop-table --execute[2020/01/02 10:16:49] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 3306 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2020/01/02 10:16:49] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000005, 294011873)
[2020/01/02 10:16:49] [info] binlogsyncer.go:203 register slave for master server localhost:3306
[2020/01/02 10:16:49] [info] binlogsyncer.go:723 rotate to (mysql-bin.000005, 294011873)
# Migrating `demo`.`user`; Ghost table is `demo`.`_user_gho`
# Migrating sc-block-laj:3306; inspecting sc-block-laj:3306; executing on sc-block-laj
# Migration started at Thu Jan 02 10:16:49 +0800 2020
# 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.demo.user.sock
Copy: 98000/9979825 1.0%; Applied: 0; Backlog: 0/1000; Time: 6s(total), 6s(copy); streamer: mysql-bin.000005:297703596; State: migrating; ETA: N/A
Copy: 124000/9979825 1.2%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 7s(copy); streamer: 
C
Copy: 9830000/9979825 98.5%; Applied: 0; Backlog: 0/1000; Time: 5m42s(total), 5m42s(copy); streamer: mysql-bin.000006:138723037; State: migrating; ETA: 5s
Copy: 9867000/9979825 98.9%; Applied: 0; Backlog: 0/1000; Time: 5m43s(total), 5m43s(copy); streamer: mysql-bin.000006:140112180; State: migrating; ETA: 3s
Copy: 9888000/9979825 99.1%; Applied: 0; Backlog: 0/1000; Time: 5m44s(total), 5m44s(copy); streamer: mysql-bin.000006:140900342; State: migrating; ETA: 3s
Copy: 9906000/9979825 99.3%; Applied: 0; Backlog: 0/1000; Time: 5m45s(total), 5m45s(copy); streamer: mysql-bin.000006:141578026; State: migrating; ETA: 2s
Copy: 9939000/9979825 99.6%; Applied: 0; Backlog: 0/1000; Time: 5m46s(total), 5m46s(copy); streamer: mysql-bin.000006:142816410; State: migrating; ETA: 1s
Copy: 9961000/9979825 99.8%; Applied: 0; Backlog: 0/1000; Time: 5m47s(total), 5m47s(copy); streamer: mysql-bin.000006:143645258; State: migrating; ETA: 0s
Copy: 9972000/9979825 99.9%; Applied: 0; Backlog: 0/1000; Time: 5m48s(total), 5m48s(copy); streamer: mysql-bin.000006:144058516; State: migrating; ETA: 0s
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m49s(total), 5m48s(copy); streamer: mysql-bin.000006:145088121; State: migrating; ETA: due
# Migrating `demo`.`user`; Ghost table is `demo`.`_user_gho`
# Migrating sc-block-laj:3306; inspecting sc-block-laj:3306; executing on sc-block-laj
# Migration started at Thu Jan 02 10:16:49 +0800 2020
# 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.demo.user.sock
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m50s(total), 5m48s(copy); streamer: mysql-bin.000006:145114154; State: migrating; ETA: due
[2020/01/02 10:22:39] [info] binlogsyncer.go:164 syncer is closing...
[2020/01/02 10:22:39] [error] binlogsyncer.go:631 connection was bad
[2020/01/02 10:22:39] [error] binlogstreamer.go:77 close sync with err: Sync was closed
[2020/01/02 10:22:39] [info] binlogsyncer.go:179 syncer is closed
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m50s(total), 5m48s(copy); streamer: mysql-bin.000006:145115458; State: migrating; ETA: due
# Done
/root
==========获得值=============
spend time : 350.566s
/root

[2020/01/02 10:16:49] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 3306 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2020/01/02 10:16:49] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000005, 294011873)
[2020/01/02 10:16:49] [info] binlogsyncer.go:203 register slave for master server localhost:3306
[2020/01/02 10:16:49] [info] binlogsyncer.go:723 rotate to (mysql-bin.000005, 294011873)
# Migrating `demo`.`user`; Ghost table is `demo`.`_user_gho`
# Migrating sc-block-laj:3306; inspecting sc-block-laj:3306; executing on sc-block-laj
# Migration started at Thu Jan 02 10:16:49 +0800 2020
# 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.demo.user.sock
Copy: 0/9979825 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000005:294013599; State: migrating; ETA: N/A
Copy: 0/9979825 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000005:294016737; State: migrating; ETA: N/A
streamer: mysql-bin.000006:135831515; State: migrating; ETA: 7s
Copy: 9792000/9979825 98.1%; Applied: 0; Backlog: 0/1000; Time: 5m41s(total), 5m41s(copy); streamer: mysql-bin.000006:137297689; State: migrating; ETA: 6s
Copy: 9830000/9979825 98.5%; Applied: 0; Backlog: 0/1000; Time: 5m42s(total), 5m42s(copy); streamer: mysql-bin.000006:138723037; State: migrating; ETA: 5s
Copy: 9867000/9979825 98.9%; Applied: 0; Backlog: 0/1000; Time: 5m43s(total), 5m43s(copy); streamer: mysql-bin.000006:140112180; State: migrating; ETA: 3s
Copy: 9888000/9979825 99.1%; Applied: 0; Backlog: 0/1000; Time: 5m44s(total), 5m44s(copy); streamer: mysql-bin.000006:140900342; State: migrating; ETA: 3s
Copy: 9906000/9979825 99.3%; Applied: 0; Backlog: 0/1000; Time: 5m45s(total), 5m45s(copy); streamer: mysql-bin.000006:141578026; State: migrating; ETA: 2s
Copy: 9939000/9979825 99.6%; Applied: 0; Backlog: 0/1000; Time: 5m46s(total), 5m46s(copy); streamer: mysql-bin.000006:142816410; State: migrating; ETA: 1s
Copy: 9961000/9979825 99.8%; Applied: 0; Backlog: 0/1000; Time: 5m47s(total), 5m47s(copy); streamer: mysql-bin.000006:143645258; State: migrating; ETA: 0s
Copy: 9972000/9979825 99.9%; Applied: 0; Backlog: 0/1000; Time: 5m48s(total), 5m48s(copy); streamer: mysql-bin.000006:144058516; State: migrating; ETA: 0s
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m49s(total), 5m48s(copy); streamer: mysql-bin.000006:145088121; State: migrating; ETA: due
# Migrating `demo`.`user`; Ghost table is `demo`.`_user_gho`
# Migrating sc-block-laj:3306; inspecting sc-block-laj:3306; executing on sc-block-laj
# Migration started at Thu Jan 02 10:16:49 +0800 2020
# 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.demo.user.sock
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m50s(total), 5m48s(copy); streamer: mysql-bin.000006:145114154; State: migrating; ETA: due
[2020/01/02 10:22:39] [info] binlogsyncer.go:164 syncer is closing...
[2020/01/02 10:22:39] [error] binlogsyncer.go:631 connection was bad
[2020/01/02 10:22:39] [error] binlogstreamer.go:77 close sync with err: Sync was closed
[2020/01/02 10:22:39] [info] binlogsyncer.go:179 syncer is closed
Copy: 10000000/10000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 5m50s(total), 5m48s(copy); streamer: mysql-bin.000006:145115458; State: migrating; ETA: due
# Done

5.3 性能报告

user表	添加	修改
10w	3s	4.61s
100w	23.029s	21.916s
1000w	273.004s	253.515s

5.4 java脚本

package test;  
import java.io.InputStreamReader;  
import java.io.LineNumberReader;  

/** 
* java在linux环境下执行linux命令，然后返回命令返回值。 
* @author lee 
*/  
public class ExecLinuxCMD {  

public static Object exec(String cmd) {  
try {  
	
String[] cmdA = { "/bin/sh", "-c", cmd };  
Process process = Runtime.getRuntime().exec(cmdA);  
LineNumberReader br = new LineNumberReader(new InputStreamReader(  
process.getInputStream()));  
StringBuffer sb = new StringBuffer();  
String line;  
while ((line = br.readLine()) != null) {  
System.out.println(line);  
sb.append(line).append("\n");  
} 

return sb.toString();  
} catch (Exception e) {  
e.printStackTrace();  
}  
return null;  
}  

public static void main(String[] args) {  
	long start = System.currentTimeMillis();
String alter = "\"CHANGE username username11 VARCHAR(256)\"";
String cmd ="gh-ost  --user=\"root\" --password=\"123456\" --host=localhost --allow-on-master --database=\"demo\" --table=\"user\" --alter="+alter+"  --approve-renamed-columns --ok-to-drop-table --execute";
System.out.print(cmd);
String netsString = exec(cmd).toString();  
String pwdString = exec("pwd").toString();
System.out.println("==========获得值=============");  
long end = System.currentTimeMillis();
System.out.println("spend time : "+(end-start)/1000.0 +"s");
System.out.println(pwdString);  
System.out.println(netsString);  
}  

}  




6.应用场景和接口设计

6.1项目应用模块
发布中心，填报中心，这几个模块大致功能业务是相似的，下面介绍接口设计以互动中心里的问卷管理作为示例。

6.2 互动中心（调查问卷管理）原型图分析和接口设计