DBAtlas · Database operations atlas

Triggerless online schema change with gh-ost

MySQLOnline Operations

Command

# Connects to a replica and reads its binlog; postpones cut-over until the flag file is removed
gh-ost --host=db2 --port=3306 --user=ghost --password='Gh0st#2024' \
  --database=shop --table=orders \
  --alter="ADD COLUMN channel VARCHAR(20) NULL, ADD INDEX ix_channel (channel)" \
  --max-load=Threads_running=50 --critical-load=Threads_running=200 --max-lag-millis=1500 \
  --exact-rowcount --concurrent-rowcount --default-retries=120 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone --panic-flag-file=/tmp/ghost.panic \
  --serve-socket-file=/tmp/gh-ost.shop.orders.sock --ssl --ssl-allow-insecure --execute
# While running: status / throttle / cut-over
echo status | nc -U /tmp/gh-ost.shop.orders.sock
echo throttle | nc -U /tmp/gh-ost.shop.orders.sock
rm /tmp/ghost.postpone

What it does

Builds a ghost table and copies rows; applies concurrent changes by reading the binlog instead of triggers. Throttling, pausing and cut-over timing are controlled at runtime via the socket or flag files.

When to use it

On large, write-heavy tables where trigger overhead is unacceptable and you want to choose the cut-over moment (e.g. a low-traffic window) yourself.

Caution

Requires binlog_format=ROW and binlog_row_image=FULL; the replica it connects to needs log_replica_updates=ON. The user needs SUPER or REPLICATION CLIENT / REPLICATION SLAVE plus ALL on the table. Tables with foreign keys (parent or child) are not supported. After cut-over the old table remains as _<table>_del; drop it manually once verified (--ok-to-drop-table drops it automatically). gh-ost releases before 1.1.8 use SHOW SLAVE STATUS and stop with error 1064 on MySQL 8.4; 8.4 support arrived in 1.1.8. gh-ost creates the file given in --postpone-cut-over-flag-file itself; cut-over does not happen until the file is removed.

Open in DBAtlas →

Related