文章目录
  1. 1. GitHub’s online schema migration tool for MySQL
  2. 2. gh-ost
  3. 3. 使用方式
  4. 4. example
  5. 5. 原理:
    1. 5.1. 行数据拷贝(row copy)
    2. 5.2. 日志解析和应用
    3. 5.3. 可行性分析
  6. 6. 总结

GitHub’s online schema migration tool for MySQL

gh-ost has been developed at GitHub in recent months to answer a problem we faced with ongoing, continuous production changes requiring modifications to MySQL tables. gh-ost changes the existing online table migration paradigm by providing a low impact, controllable, auditable, operations friendly solution.

gh-ost

gh-ost : stands for GitHub’s Online Schema

– Triggerless
– Lightweight
– Pauseable
– Dynamically controllable
– Auditable
– Testable
– Trustable
– Triggerless

Transmogrifier/Transfigurator/Transformer/Thingy

使用方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
# 执行命令
./gh-ost --help

#输出
-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 si correct
-chunk-size int
amount of rows to handle in each iteration (allowed range: 100-100,000) (default 1000)
-conf string
Config file
-critical-load --max-load
Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits
-cut-over string
choose cut-over type (default|atomic, two-step) (default "atomic")
-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)
-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
-help
Display usage
-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!
-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.5: for every ms spend in a rowcopy chunk, spend 1.5ms 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
-replication-lag-query string
Query that detects replication lag in seconds. Result can be a floating point (by default gh-ost issues SHOW SLAVE STATUS and reads Seconds_behind_master). If you're using pt-heartbeat, query would be something like: SELECT ROUND(UNIX_TIMESTAMP() - MAX(UNIX_TIMESTAMP(ts))) AS delay FROM my_schema.heartbeat
-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-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
-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
-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-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
-user string
MySQL user
-verbose
verbose
-version
Print version & exit

example

\
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="replip" \
--max-lag-millis=1500 \
--user="xxxxx" \
--password="xxxxxx" \
--host="xxx.xxx..xxx.13" \
--port=3306 \
--database="test" \
--table="big_data_check_hdfs" \
--verbose \
--alter="change file_path file_path varchar(128) " \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--default-retries=120 \
--postpone-cut-over-flag-file=/data/dbbak/gh-ost/ghost.postpone.flag \
--execute

原理:

gh-ost最核心的两个模块: 行数据拷贝 ; 日志解析和应用 . 两个操作是同时并发执行的, 只要有日志就会立即在影子表执行.

行数据拷贝(row copy)

Row Copy的逻辑如下:

  • 计算Chunk
  • 对每个 Chunk 数据进行copy, copy的过程中 , 对原表进行加锁, 保证copy过程中没有对正在copy的数据进行修改操作.(如果是给表添加unique key,重复的数据会被ignore, 正常现象)

    1
    2
    3
    insert /* gh-ost %s.%s */ ignore into %s.%s (%s)
    (select %s from %s.%s force index (%s)
    where (%s and %s) %s lock in share mode
  • 将所有的 chunck都copy完成,整个copy过程完成

日志解析和应用

通过伪装为slave的方式从master获取binglog日志, 并解析拼装成为sql ,在目标库或者源库执行(这取决于在哪个库做操作, 大部分是在主库)

可行性分析

由于RowCopy的过程中是加锁的, 所以copy过程中是不存在对这部分数据的任何修改操作的, 即数据在copy过程中不可能有对应的binlog产生. 所以我们对不同类型的操作只需要分析 log before copy (b-log) 和 log after copy(a-log) , 我们假设copy过程为t0区间(可以看做一个时间点)

代码生成模板

  • INSTER

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    result = fmt.Sprintf(`
    replace /* gh-ost %s.%s */ into
    %s.%s
    (%s)
    values
    (%s)
    `, databaseName, tableName,
    databaseName, tableName,
    strings.Join(mappedSharedColumnNames, ", "),
    strings.Join(preparedValues, ", "),

日志提前到达, 直接insert进来, rowcopy的时候会被忽略. 日志延迟到

#如下逻辑
if b-log > t0: 
    日志延迟到达, 没有关系直接replace
else:  
    日志提前到达, 直接insert进来, rowcopy的时候会被忽略
  • UPDATE

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    fmt.Sprintf(`
    update /* gh-ost %s.%s */
    %s.%s
    set
    %s
    where
    %s
    `, databaseName, tableName,
    databaseName, tableName,
    setClause,
    equalsComparison

    #如下逻辑
    if b-log > t0:

    直接update目标表数据
    

    else:

    空update, rowcopy的时候会把new 值带过来
    

    if a-log > t0:

    空update
    

    else:

    直接update, 相当于update了两次
    
  • DELETE

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    result = fmt.Sprintf(`
    delete /* gh-ost %s.%s */
    from
    %s.%s
    where
    %s
    `, databaseName, tableName,
    databaseName, tableName,
    equalsComparison,
    )

    #如下逻辑
    if b-log > t0:

    空删除
    

    else:

    空删除
    

总结

gh-ost以一个新的思路来完成 online ddl 等操作, 避免了传统使用trigger方式来操作的很多弊端. 不过目前gh-ost尚未流行,需要进行充分的测试才可以上生产环境. 总之 , gh-ost是一个好工具

文章目录
  1. 1. GitHub’s online schema migration tool for MySQL
  2. 2. gh-ost
  3. 3. 使用方式
  4. 4. example
  5. 5. 原理:
    1. 5.1. 行数据拷贝(row copy)
    2. 5.2. 日志解析和应用
    3. 5.3. 可行性分析
  6. 6. 总结