Adding a column to a small table is instant.
On a table with tens of millions of rows and constant writes, a plain ALTER TABLE can lock the table or add large replication lag.
Some changes in MySQL 8 run as INSTANT or INPLACE, but many do not, and you find out in production.
gh-ost is GitHub’s online schema migration tool for MySQL. This note is the runbook I used: install it, run a dry run, test on a replica, verify the data, then run the real migration.
How gh-ost works
gh-ost does not change your table in place.
- It creates a ghost table with the same schema, for example
_orders_gho. - It applies your
ALTERto the empty ghost table. - It copies rows from the original table to the ghost table in small chunks.
- At the same time, it reads the binary log and applies ongoing writes to the ghost table.
- When the ghost table has caught up, it swaps the two tables in a short, atomic cut-over.
Other tools use triggers to capture ongoing writes. gh-ost reads the binary log instead, so it adds no trigger load to the production table. You can also pause it, throttle it, or stop it at any time.
Before you start
- Row-based binary logs. gh-ost needs
binlog_format=ROWandbinlog_row_image=FULLon the server it reads from. - A primary key or unique key on the table. gh-ost uses it to copy rows in chunks.
- Privileges. The gh-ost user needs
ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATEon the schema, andSUPERorREPLICATION CLIENT, REPLICATION SLAVEglobally. - No foreign keys on the table. gh-ost does not support them.
- Disk space for a full copy of the table.
On Amazon RDS or Aurora
You cannot change binlog_format with a SQL command on RDS.
Set it in the DB parameter group, then tell gh-ost not to try:
- Add
--assume-rbr. - Keep binary logs long enough for the migration:
CALL mysql.rds_set_configuration('binlog retention hours', 24); - If gh-ost connects to the writer, add
--allow-on-master.
Install on a Linux jump host
Run gh-ost from a host close to the database, not from your laptop.
A migration can run for hours, so start it inside tmux or screen.
# Pick the latest release from https://github.com/github/gh-ost/releases
VERSION=v1.1.11
curl -fsSLO "https://github.com/github/gh-ost/releases/download/${VERSION}/gh-ost-linux-amd64.tar.gz"
curl -fsSLO "https://github.com/github/gh-ost/releases/download/${VERSION}/SHA256SUMS"
sha256sum --ignore-missing -c SHA256SUMS
tar -xzf gh-ost-linux-amd64.tar.gz
./gh-ost --version
Use gh-ost-linux-arm64.tar.gz on a Graviton host.
Step 1: Dry run
Without --execute, gh-ost connects, checks the setup and validates the ALTER.
It does not copy data.
./gh-ost \
--host=db-replica.internal --user=ghost --password="$GHOST_PASSWORD" \
--database=shop --table=orders \
--alter="ADD COLUMN jira_id VARCHAR(30) NULL" \
--chunk-size=2000 \
--max-load=Threads_running=25 \
--critical-load=Threads_running=100 \
--max-lag-millis=1500 \
--verbose
Read the output. It tells you if the binary log format, the keys or the privileges are wrong.
A note on NOT NULL: adding a NOT NULL column without a default fails while gh-ost copies existing rows.
Add the column as NULL or give it a DEFAULT.
Step 2: Test on a replica
Add --test-on-replica and --execute.
gh-ost runs the full migration on a replica only.
At the end, it stops replication on that replica and keeps both tables, so you can compare them.
./gh-ost <same flags as above> --test-on-replica --execute
Verify the data
CHECKSUM TABLE compares whole rows.
Your ALTER changed the schema, so the checksums of the two tables never match.
Compare the columns the two tables share instead:
SELECT COUNT(*), SUM(CRC32(CONCAT_WS('#', id, customer_id, amount, status, created_at)))
FROM orders;
SELECT COUNT(*), SUM(CRC32(CONCAT_WS('#', id, customer_id, amount, status, created_at)))
FROM _orders_gho;
If the counts and sums match, the copy is correct.
Then start replication on the replica again with START REPLICA; (START SLAVE; before MySQL 8.0.22) and drop the test tables.
Step 3: Run the migration
Run the same command without --test-on-replica.
By default, gh-ost connects to a replica, finds the primary from it, and writes to the primary.
It reads the binary log from the replica, which keeps load off the primary.
./gh-ost <same flags as above> \
--postpone-cut-over-flag-file=/tmp/ghost.postpone \
--serve-socket-file=/tmp/gh-ost.orders.sock \
--execute
Two flags make this safe to run during the day:
--postpone-cut-over-flag-file: gh-ost copies everything, then waits while this file exists. You choose when the cut-over happens. Delete the file at a quiet time.--serve-socket-file: you can talk to the running migration.
echo status | nc -U /tmp/gh-ost.orders.sock # progress and ETA
echo throttle | nc -U /tmp/gh-ost.orders.sock # pause row copy
echo no-throttle | nc -U /tmp/gh-ost.orders.sock # resume
echo "chunk-size=1000" | nc -U /tmp/gh-ost.orders.sock
Throttling
gh-ost slows down by itself in three cases:
--max-load: a server metric is above the limit, for exampleThreads_running=25.--max-lag-millis: replication lag on the replicas is too high.--critical-load: a metric reaches a danger level. gh-ost stops the migration.
Start with conservative values. You can loosen them through the socket while the migration runs.
Step 4: Clean up
After the cut-over, gh-ost keeps the old table as _orders_del.
Keep it until you are sure the new schema works, then drop it.
On a large table, dropping it can also cause a stall, so do it at a quiet time.
Checklist
- Check the binary log format, keys, foreign keys, privileges and disk space.
- Run a dry run and read every warning.
- Test on a replica. Compare counts and checksums over the shared columns.
- Run the migration with a postponed cut-over and a socket.
- Cut over at a quiet time.
- Drop the old table later.
gh-ost turns a risky, all-or-nothing ALTER into a process you can test, watch, pause and time.
That is the part that matters: the change becomes routine instead of an event.