Understanding gh-ost Online DDL Migration Process and Cut‑over Mechanics

The article provides a deep dive into the gh‑ost tool, an online DDL solution for MySQL that performs schema changes without downtime. It begins with a brief author bio and links to a previous introduction of gh‑ost parameters.

Execution Process

1. Check database instance information : verifies connectivity, permissions, binlog format, row image, and storage engine. Example commands include:

show grants for current_user();
select @@global.log_bin, @@global.binlog_format;
select @@global.binlog_row_image;

2. Simulate a slave and obtain binlog coordinates by creating a binlog streamer. Sample log entries:

2019-09-08T22:01:20.944172+08:00    17760 Query show /* gh-ost readCurrentBinlogCoordinates */ master status
2019-09-08T22:01:20.947238+08:00    17762 Connect   [email protected] using TCP/IP

3. Create migration tables for logging ( _b_ghc ) and the shadow table ( _b_gho ), then alter the shadow table to match the target schema.

create table `test`.`_b_ghc` (
  id bigint auto_increment,
  last_update timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  hint varchar(64) charset ascii not null,
  value varchar(4096) charset ascii not null,
  primary key(id),
  unique key hint_uidx(hint)
) auto_increment=256;

create table `test`.`_b_gho` like `test`.`b`;
alter table `test`.`_b_gho` engine=innodb;

4. Copy data in chunks by determining the minimum and maximum primary keys and iterating over ranges. Example chunk query:

select /* gh-ost `test`.`b` iteration:0 */ `id` from `test`.`b` where `id` > 1 and `id` < 21 order by `id` asc limit 1 offset 999;

5. Apply incremental binlog changes to the shadow table. The core conversion functions reside in gh-ost/go/sql/builder.go and translate INSERT, UPDATE, DELETE events into corresponding DML on the shadow table.

func BuildDMLDeleteQuery(databaseName, tableName string, ...) {
    result = fmt.Sprintf(`delete /* gh-ost %s.%s */ from %s.%s where %s`, databaseName, tableName, databaseName, tableName, equalsComparison)
    return result, uniqueKeyArgs, nil
}

func BuildDMLInsertQuery(databaseName, tableName string, ...) {
    result = fmt.Sprintf(`replace /* gh-ost %s.%s */ into %s.%s (%s) values (%s)`, databaseName, tableName, databaseName, tableName, strings.Join(mappedSharedColumnNames, ", "), strings.Join(preparedValues, ", "))
    return result, sharedArgs, nil
}

func BuildDMLUpdateQuery(databaseName, tableName string, ...) {
    result = fmt.Sprintf(`update /* gh-ost %s.%s */ %s.%s set %s where %s`, databaseName, tableName, databaseName, tableName, setClause, equalsComparison)
    return result, sharedArgs, uniqueKeyArgs, nil
}

Data Consistency Analysis

The tool performs three kinds of operations during migration: row copy from the original table (A), concurrent DML on the original table (B), and applying binlog events to the shadow table (C). Because binlog events are generated after the original DML, the apply step (C) always follows B, ensuring eventual consistency. A diagram (omitted) illustrates the possible operation orderings.

Cut‑over Process

Cut‑over is an atomic rename sequence that swaps the original and shadow tables while a sentinel table ( _b_del ) prevents premature renames. The steps involve locking both tables, setting a short lock‑wait timeout, renaming, and finally unlocking. Sample log sequence:

lock tables `test`.`b` write, `test`.`_b_del` write;
set session lock_wait_timeout:=1;
rename table `test`.`b` to `test`.`_b_del`, `test`.`_b_gho` to `test`.`b`;
unlock tables;

If any step fails, gh‑ost aborts safely, releases locks, and normal DML resumes on the original table.

Impact on Applications and Replication

During the lock, write operations are blocked until the rename succeeds or the process fails. Replication is unaffected because lock statements are not written to the binlog; only the rename is replicated, preserving atomicity on slaves.

Final Remarks

The article concludes that gh‑ost’s design, especially the cut‑over logic, provides a reliable, low‑impact method for online schema changes. Readers are encouraged to try the tool, share experiences, and consult the referenced blog posts for deeper details.