How to Change a Schema on a Busy MySQL Table Without Locking It

The change you want is one column. The table has a few hundred million rows and takes writes all day. Run the ALTER directly and you find out how long your application tolerates a stalled table, which is a question better answered on purpose than at three in the morning.

There are two ways out. The first is to check whether MySQL will do the change instantly, because it very often will. The second is to copy the table in the background and swap it in at the end, which is what the online schema change tools do.

Check whether you need a tool at all

In MySQL 8.4, INSTANT is the default algorithm for adding and dropping columns, and it is a metadata-only operation. Renaming a column, adding or dropping a virtual generated column, setting or dropping a column default, and adding members to the end of an ENUM without changing its storage size are all supported instantly as well.

Say so explicitly, so that MySQL refuses rather than quietly falling back to a full table rebuild.

ALTER TABLE orders ADD COLUMN dispatch_note VARCHAR(255) NULL, ALGORITHM=INSTANT;

If that statement succeeds you are finished. There is one limit worth knowing about, which is that instant changes accumulate row versions in the table metadata. The maximum permitted is 64, raised to 255 as of MySQL 9.1.0, and once you hit it you get this:

ERROR 4092 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

Rebuilding the table with OPTIMIZE TABLE or a table-rebuilding ALTER resets the count to zero. Adding an index is not an instant operation, but it does support ALGORITHM=INPLACE, LOCK=NONE, which permits concurrent DML while it runs.

What copy and swap actually does

When the change genuinely needs the table rebuilt, the pattern is the same whichever tool you pick. An empty copy of the table is created with the new definition, rows are copied across in chunks, the copy is kept in step with ongoing writes, and at the end the two tables are swapped by rename.

The two mature tools differ in how they keep the copy in step. Percona's pt-online-schema-change creates triggers on the original table that mirror every write into the copy. GitHub's gh-ost avoids triggers entirely by connecting to MySQL as if it were a replica and reading the binary log, which means the write path on your table is untouched while the migration runs. The rest of this guide uses gh-ost, because that difference is what makes it predictable under load.

What gh-ost needs

The requirements are short and non-negotiable, and it is worth checking them before you schedule anything.

The table must have a primary key or another unique key, and the columns of that key must be NOT NULL or contain no actual NULL values. Foreign key constraints are not supported, and neither are existing triggers on the table. Encrypted binary logs are not supported. The server it reads binary logs from must be producing them in row format, and full row images are what it supports. Both of those are the MySQL 8.4 defaults, since binlog_format defaults to ROW and binlog_row_image defaults to full.

The migration user needs ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE on the database being changed, plus either SUPER, REPLICATION SLAVE or REPLICATION CLIENT, REPLICATION SLAVE globally. SUPER is only needed to switch a replica to row-based replication, which you can avoid with --assume-rbr if the topology already uses it.

Do a no-op run first

Without --execute, gh-ost validates the migration and changes nothing. Run it that way every single time, because it catches a missing unique key or a foreign key reference in seconds rather than an hour in.

gh-ost \
  --host=replica.internal \
  --user="gh-ost" \
  --password="$GHOST_PASSWORD" \
  --database="shop" \
  --table="orders" \
  --alter="ADD COLUMN dispatch_note VARCHAR(255) NULL" \
  --verbose

The real run

By default gh-ost expects to connect to a replica, work out the topology's master from there, and read binary log events off the replica while writing rows on the master. If you have no replicas, you connect to the master directly and approve that with --allow-on-master.

gh-ost \
  --host=master.internal \
  --allow-on-master \
  --user="gh-ost" \
  --password="$GHOST_PASSWORD" \
  --database="shop" \
  --table="orders" \
  --alter="ADD COLUMN dispatch_note VARCHAR(255) NULL" \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=1000 \
  --max-lag-millis=1500 \
  --serve-socket-file=/tmp/gh-ost.shop.orders.sock \
  --panic-flag-file=/tmp/gh-ost.panic.flag \
  --postpone-cut-over-flag-file=/tmp/gh-ost.postpone.flag \
  --verbose \
  --execute

--chunk-size controls how many rows are copied per iteration, defaulting to 1000 with an allowed range of 10 to 100000. --max-load is a list of status variables and thresholds that make the throttler kick in when any one is exceeded. --critical-load uses the same format but makes gh-ost panic and bail out instead of waiting.

Note what is missing from that command. --ok-to-drop-table is not there, so the old table is left behind at the end rather than dropped. That is the default, because dropping a large table is itself a long locking operation, and it means you can inspect the original before you get rid of it on your own schedule.

Stopping it safely

Three controls matter, and all three work while the migration is running.

Creating the file named by --panic-flag-file makes gh-ost terminate immediately without cleanup. That is the emergency stop. The ghost table is left behind for you to drop.

The file named by --postpone-cut-over-flag-file has to exist when gh-ost starts, and the final swap does not happen while it exists. The row copy runs to completion and then waits. Deleting the file, or sending the unpostpone interactive command, lets the cut-over proceed, which is how you move the only blocking moment of the whole operation into a window you chose.

The interactive commands go over the socket file.

echo status | nc -U /tmp/gh-ost.shop.orders.sock
echo "chunk-size=250" | nc -U /tmp/gh-ost.shop.orders.sock
echo throttle | nc -U /tmp/gh-ost.shop.orders.sock
echo no-throttle | nc -U /tmp/gh-ost.shop.orders.sock

throttle forces the migration to suspend and no-throttle cancels it. panic over the same socket aborts. The atomic cut-over holds a lock on one connection while another performs the rename, and if that connection dies or times out, the original table is left in place and accessible.

Take a backup you have restored before you run any of this, and check it. A schema change that goes wrong on a table this size is not something you fix by trying again.

Our server setup and optimization work covers database changes of this kind end to end, and infrastructure management keeps the replicas and binlog settings they depend on in a state where they will actually work.