pt-online-schema-change: Zero-Downtime MySQL ALTER TABLE

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)

apt-get install percona-toolkit
# or
yum install percona-toolkit

# 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 \
  --execute

# 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 --execute

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 chunks

# Always dry-run first (omit --execute)
pt-online-schema-change \
  --alter 'ADD COLUMN score INT DEFAULT 0' \
  --dry-run \
  D=myapp,t=orders