Senior LevelSQL & MySQL
How do gh-ost and pt-online-schema-change execute zero-downtime schema migrations on massive tables?
❓ Interview Prompt: Explain Online DDL limitations, `pt-online-schema-change` (trigger-based copying), `gh-ost` (binlog-streamed copying), and avoiding metadata lock stalls.
Executing 'ALTER TABLE' on a 500GB production table can block writes for hours. Tools like 'gh-ost' and 'pt-online-schema-change' perform zero-downtime schema migrations:
How 'gh-ost' Works:
1
Creates a shadow table with the new schema structure.
2
Copies historical rows in small, rate-limited background batches.
3
Intercepts live writes by streaming the MySQL Binary Log (binlog) and applying changes to the shadow table.
4
Atomically swaps the shadow table for the live table in milliseconds!
Code Implementation Example
bash
# Executing zero-downtime migration on massive table using gh-ost CLI:
gh-ost \
--user="migration_user" \
--password="secret" \
--host="db.internal" \
--database="production" \
--table="orders" \
--alter="ADD COLUMN tracking_code VARCHAR(64)" \
--execute💡Executing zero-downtime table migrations with gh-ost CLI.
🎯 Interview Key Takeaway
Use gh-ost or pt-online-schema-change for zero-downtime schema migrations on multi-gigabyte production tables.
#MySQL#gh-ost#Online DDL#Schema Migration#Senior
Back to all SQL & MySQL Questions→