gh-ost (GitHub's Online Schema Transmogrifier) is an alternative to pt-online-schema-change that uses binlog streaming instead of triggers, making it safer and more controllable.
gh-ost vs pt-osc
text
Feature gh-ost pt-osc
---------------------|---------------------|------------------
Mechanism Binlog streaming Triggers
Works with triggers Yes No
Pause/resume Yes (via socket) No
Throttle dynamically Yes Limited
Risk of trigger bugs None Possible
Requires binlog Yes (ROW format) NoInstallation
bash
# Download binary
wget https://github.com/github/gh-ost/releases/download/v1.1.6/gh-ost-binary-linux-amd64-20231207134705.tar.gz
tar xzf gh-ost*.tar.gz
sudo mv gh-ost /usr/local/bin/Basic Migration
bash
gh-ost \
--user=root --password=secret \
--host=localhost \
--database=myapp \
--table=orders \
--alter='ADD COLUMN score INT NOT NULL DEFAULT 0' \
--allow-on-master \
--executeThrottle and Control via Unix Socket
bash
# While migration is running, pause it
echo throttle | nc -U /tmp/gh-ost.orders.sock
# Resume
echo no-throttle | nc -U /tmp/gh-ost.orders.sock
# Check status
echo status | nc -U /tmp/gh-ost.orders.sock
# Gracefully complete (skips remaining rows, cuts over)
echo unpostpone | nc -U /tmp/gh-ost.orders.sockSafe Throttle Configuration
bash
gh-ost \
--user=root --password=secret \
--host=localhost \
--database=myapp --table=orders \
--alter='ADD INDEX idx_orders_score (score)' \
--max-load='Threads_running=20' \
--critical-load='Threads_running=40' \
--chunk-size=500 \
--default-retries=120 \
--postpone-cut-over-flag-file=/tmp/gh-ost.postpone \
--allow-on-master \
--execute
# --postpone-cut-over-flag-file: create this file to delay the final cutover
# touch /tmp/gh-ost.postpone # delays cutover until file is removedTest Mode (No Actual Migration)
bash
# Validate migration plan without touching production
gh-ost \
--user=root --password=secret \
--host=localhost \
--database=myapp --table=orders \
--alter='DROP COLUMN legacy_col' \
--test-on-replicaKey Takeaways
- gh-ost uses binlog streaming — no triggers, so it works on tables that already have triggers
- Use the Unix socket to pause, throttle, or postpone cutover during peak traffic
- Use
--postpone-cut-over-flag-fileto control exactly when the final lock happens - Test with
--test-on-replicabefore running on production
JusDB Can Help
Large-table schema migrations are high-risk operations. JusDB can plan and execute your MySQL schema changes with zero downtime.