ALTER TABLE in MySQL locks the table for the duration of the change. For large tables this means minutes or hours of downtime. pt-online-schema-change (pt-osc) performs the change online with minimal locking.
How pt-osc Works
text
1. Create new table with the desired schema
2. Add triggers on original table (INSERT/UPDATE/DELETE) to sync changes
3. Copy rows in small batches from original → new table
4. Rename new table → original (atomic, milliseconds lock)
5. Drop original table (now the old one)Installation
bash
apt-get install percona-toolkit
# or
yum install percona-toolkitBasic Usage
bash
# Add a column to a large table with zero downtime
pt-online-schema-change \
--alter 'ADD COLUMN last_login TIMESTAMP NULL' \
--host=localhost \
--user=root \
--password=secret \
--database=myapp \
--table=users \
--executeCommon ALTER Operations
bash
# Add index
pt-online-schema-change \
--alter 'ADD INDEX idx_users_email (email)' \
D=myapp,t=users --execute
# Change column type
pt-online-schema-change \
--alter 'MODIFY COLUMN status ENUM("active","inactive","pending") NOT NULL DEFAULT "active"' \
D=myapp,t=users --execute
# Drop column
pt-online-schema-change \
--alter 'DROP COLUMN legacy_field' \
D=myapp,t=users --executeThrottling to Protect Production
bash
pt-online-schema-change \
--alter 'ADD COLUMN score INT DEFAULT 0' \
--chunk-size=1000 \
--max-load='Threads_running=25' \
--critical-load='Threads_running=50' \
--sleep=0.5 \
D=myapp,t=orders --execute
# --max-load: pause copying if MySQL load exceeds this
# --critical-load: abort if load reaches critical threshold
# --sleep: seconds to sleep between chunksDry Run First
bash
# Always dry-run first (omit --execute)
pt-online-schema-change \
--alter 'ADD COLUMN score INT DEFAULT 0' \
--dry-run \
D=myapp,t=ordersWarning: pt-osc uses triggers. It does not work on tables that already have triggers. Consider
gh-ost (GitHub's online schema tool) for tables with existing triggers — it uses binlog-based replication instead.Key Takeaways
- Always dry-run first before using
--execute - Set
--max-loadand--critical-loadto protect production during the copy - pt-osc does not work on tables with existing triggers — use gh-ost instead
- The final rename step takes a brief lock (~milliseconds) — schedule during low traffic if possible
JusDB Can Help
Zero-downtime schema changes require careful planning and monitoring. JusDB can execute and validate your MySQL schema migrations safely.