> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bytebase.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Online Schema Migration for MySQL

[gh-ost](https://github.com/github/gh-ost) is a triggerless online schema migration tool for MySQL. Bytebase invokes gh-ost to migrate MySQL table schemas with minimal downtime.

## How it works

Online schema migration tools operate through a careful orchestration of steps:

1. **Ghost table creation**: Creates a new table with the desired schema structure.
2. **Data migration**: Incrementally copies data from the original table to the ghost table while capturing ongoing changes (`INSERT`, `UPDATE`, `DELETE`).
3. **Table swap**: Atomically replaces the original table with the fully synchronized ghost table.

The whole migration runs as a **single Bytebase task**. Once you start the rollout, ghost-table creation, data copy, replication catch-up, and cut-over all happen automatically in the background — there is no manual cut-over step.

Bytebase uses a dedicated **`bbdataarchive`** database, separate from your application schema, to stage gh-ost's working tables. During the migration two temporary tables exist there:

* **`<table>_<timestamp>_gho`** — the new table, being built with the desired schema and filled from the original.
* **`<table>_<timestamp>_ghc`** — the changelog table gh-ost uses to track DML against the original during the copy.

At cut-over, gh-ost performs an atomic swap with two `RENAME TABLE`s:

1. The original table moves **out of your schema into `bbdataarchive`** as **`~<table>_<timestamp>_del`** — preserved so you can verify the migration before dropping it (see Step 5).
2. The `_gho` table moves **out of `bbdataarchive` into your schema**, taking the original's name. It is now your new live table.

The `_ghc` changelog is then dropped — it has no role after cut-over. The `_gho` is **not** dropped: it became your new table via the rename. So after a successful migration your application schema contains the new table, and `bbdataarchive` contains only the renamed `_del` original.

## Requirements and limitations

### Requirements

* MySQL version 5.7 or higher
* Row-based logging enabled
* Primary key on the table
* Primary database for migration (replica migration not currently supported)

### Limitations

* Foreign key constraints are not supported
* Triggers are not supported
* Large tables may require extended migration time

For a comprehensive list of requirements and limitations, refer to the [gh-ost documentation](https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md).

## Performing an online schema migration

### Step 1 - Enable online migration

While creating a schema change plan, enable **Online migration** for the change. To customize gh-ost flags, click **Configure** next to the toggle.

Enabling the toggle prepends a `-- gh-ost = { ... }` comment to your statement — this is the **gh-ost directive** that Bytebase parses at execution time to route the migration through gh-ost. With the toggle on and no customization, the statement becomes:

```sql theme={null}
-- gh-ost = {} /* using default config */
ALTER TABLE employee ADD COLUMN nickname VARCHAR(255);
```

If you set flags via **Configure**, they're encoded as JSON, for example:

```sql theme={null}
-- gh-ost = {"chunk-size":"1000","max-load":"Threads_running=25"}
ALTER TABLE employee ADD COLUMN nickname VARCHAR(255);
```

<Note>
  Flags are read once when the issue is created and locked for the run. To change a flag, cancel the task and create a new issue.
</Note>

#### Writing the directive manually

For GitOps workflows, or any case where you author SQL outside the Bytebase UI, add the directive yourself on its own line — Bytebase recognizes the same syntax. Use an empty object for default flags:

```sql theme={null}
-- gh-ost = {}

ALTER TABLE employee ADD COLUMN nickname VARCHAR(255);
```

Or pass any subset of these flags as JSON: `max-load`, `chunk-size`, `dml-batch-size`, `default-retries`, `cut-over-lock-timeout-seconds`, `exponential-backoff-max-interval`, `max-lag-millis`, `allow-on-master`, `switch-to-rbr`, `assume-rbr`, `heartbeat-interval-millis`, `nice-ratio`, `throttle-control-replicas`, `attempt-instant-ddl`, `assume-master-host`. See the [gh-ost command-line reference](https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md) for what each flag does.

### Step 2 - Confirm gh-ost ran

Roll out the resulting issue like any other DDL. Bytebase does not show a separate "gh-ost" badge on the rollout, so confirm the directive was picked up by checking the task log:

* A **standard** MySQL DDL shows four sections — **Transaction**, **Command Execute**, **Transaction**, **Database Sync**.
* A **gh-ost** migration shows a single **Database Sync** section. **Command Execute** is absent.

If you see **Command Execute**, the directive was not parsed (most often because it is not on its own line). Fix the directive and re-create the issue.

### Step 3 - Watch progress in the backend logs

The Bytebase UI shows only the start and end of a gh-ost migration. The full picture — cut-over events, the lock-and-rename duration, temporary-table cleanup, and any errors — lands in the **Bytebase server log**. Tail it and filter for `source=ghost/`.

For example, a migration of `testdb.tmysql0` logs:

| What's happening                                            | Log line                                                                                                 |
| ----------------------------------------------------------- | -------------------------------------------------------------------------------------------------------- |
| Migration starts on `testdb`.`tmysql0`                      | <code>Migrating \`testdb\`.\`tmysql0\`</code>                                                            |
| Atomic cut-over — original and ghost tables are renamed     | `Tables renamed`                                                                                         |
| Writes against the original table were blocked for \~995 ms | <code>Lock & rename duration: 994.696418ms. During this time, queries on \`tmysql0\` were blocked</code> |
| Bytebase drops the temporary changelog table                | <code>Dropping table \`bbdataarchive\`.\`\~tmysql0\_1778162448\_ghc\`</code>                             |
| Migration is done                                           | <code>Done migrating \`testdb\`.\`tmysql0\`</code>                                                       |

The **Lock & rename duration** is the only window in which writes against the original table are blocked — typically sub-second.

<Note>
  In the raw log, gh-ost's printf-style messages pass through Bytebase's structured logger as-is, so you'll see `%s` placeholders alongside the values — for example, `msg="Lock & rename duration: %s..." !BADKEY=994.696418ms`. The values shown above are spliced in for readability.
</Note>

### Step 4 - Cancel if needed

A running gh-ost task can be canceled from the rollout page. Bytebase signals gh-ost to abort and drops the temporary `_gho` and `_ghc` tables from `bbdataarchive`. The original table in your application schema is left untouched.

### Step 5 - Post-migration cleanup

gh-ost leaves the renamed `_del` original in `bbdataarchive` so you can verify the new table is correct before discarding it, and so a heavy `DROP TABLE` doesn't compete with traffic on the freshly cut-over table for I/O. Bytebase doesn't expose gh-ost's `--ok-to-drop-table` flag, so the drop is manual:

1. Verify your data integrity and application functionality against the new table.
2. Open the `bbdataarchive` database from the instance's database list — Bytebase syncs it like any other schema, so the `~yourtablename_{timestamp}_del` table is browseable and queryable.
3. Once verified, drop `bbdataarchive.~yourtablename_{timestamp}_del`.