Applications and operations
Run MySQL migrations safely
Use compatible expand-and-contract changes while accounting for MySQL DDL implicit commits and interrupted migrations.
8 minute lesson
Apply migrations in order and record successful versions in the database.
For a risky rename, add the new column first, deploy code that supports both versions, backfill data, switch reads, then remove the old column in a later release.
Check how MySQL executes each ALTER TABLE on the table size and version you actually run.
MySQL data-definition statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE cause an implicit commit. You cannot make a multi-statement schema migration atomic by wrapping it in START TRANSACTION.
Back up first, make each step restartable, and test what happens when the migration stops halfway through.
Lesson completed