DBAtlas · Database operations atlas

Non-blocking ALTER on a large table with pt-online-schema-change

MySQLOnline Operations

Command

# First --dry-run, then the same command with --execute; throttles on replica lag
pt-online-schema-change --alter "ADD COLUMN region CHAR(2) NULL, ADD INDEX ix_region (region)" \
  D=shop,t=orders,h=db1,u=osc,p='Osc#2024',s=1 \
  --max-lag=5 --check-replica-lag h=db2,u=osc,p='Osc#2024',s=1 \
  --max-load Threads_running=50 --critical-load Threads_running=200 \
  --set-vars lock_wait_timeout=5 --noversion-check --dry-run

What it does

Creates an empty copy with the new structure, copies rows in chunks, mirrors changes during the copy with triggers and finally swaps the tables with an atomic RENAME. It slows down / pauses on its own when load or replica lag crosses the thresholds.

When to use it

For ALTERs that need COPY (column type, charset, PK change) or that would cause long replica lag.

Caution

The table needs a PK or unique key. Triggers add overhead to every write during the migration; on very write-heavy tables gh-ost has less impact. If child tables reference this table via foreign keys, --alter-foreign-keys-method=auto is required. For caching_sha2_password accounts add s=1 (TLS) to the DSN; otherwise the replica connection fails with 'Authentication requires secure connection'. The --check-replica-lag DSN does not inherit user / password from the main DSN; specify them. The swap takes a brief metadata lock; keep lock_wait_timeout low, it retries if the lock cannot be acquired.

Open in DBAtlas →

Related