In a correctly normalised database this should be rare, confined to version upgrades.

If you have to do it, since 8.0 with InnoDB see https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/, otherwise follow a sequence like this ...

1 Record a starting time or replication log position

2 Disable transactions

3 Make a working copy of the table, add the desired column to it

4 Give the original table a backup name

5 Rename the new table to the original name

6 Replay the transaction log from the timestamp in #1.

7 Enable transactions.

Last updated 16 Aug 2024