Zero downtime on MySQL schema change

Have you ever deployed a feature which interrupted your application because the schema change on the MySQL database has locked the table? Then I have good news for you! There are some tools like the Percona Toolkit which can apply the operation without a downtime.

The problem

When you have a MySQL database and want to apply a scheme change on a table (e.g. an alter table), the table needs to be rebuild on some DDL (Data Definition Language) operations. This depends on the MySQL version and DDL operation.

The rebuild looks like this:

1. Set write lock on the table
2. Create a new temporary table with the changed schema
3. Copy row by row to the temporary table
4. Set additional read lock on the table
5. Set the temporary table as new table
6. Release locks

When you have a bigger table, your application won't be able to write during the process. Also, the copy process will take some time. Your service will be interrupted.

We are offline due to maintenance

Percona Toolkit to the rescue

The tool pt-online-schema-change of the Percona Toolkit will take another strategy when altering a table. It will create a new table and add triggers to the existing table so that changes will be copied to the new table. Then the existing data will be copied. After this, the tables will swapped, and the triggers will be dropped.

The whole process looks like this:

1. Create the new table with the existing schema
2. Alter the new table with the changes
3. Create triggers
4. Copy data
5. Swap tables
6. Drop old table
7. Drop triggers

Disclaimer

pt-online-schema-change is well tested, and I have used it many times. But you should always test your schema change on a non-production database, and you should also have an up to date backup. Also read the documentation before using the tool. Under no circumstances will I be held responsible or liable in any way for any claims, damages, losses, expenses or costs.

Installation

Download the Percona Toolkit or install using your favourite OS package manager. The Percona Toolkit is written in Perl.

Usage

Example for dropping a column

pt-online-schema-change --alter "drop column my_column" D=my_database,t=my_table --alter-foreign-keys-method auto --execute

Example for adding a column with type INT UNSIGNED

pt-online-schema-change --alter "add column my_column INT UNSIGNED" D=my_database,t=my_table --alter-foreign-keys-method auto --execute

Like you can see, there are many options like —alter-foreign-keys-method or —execute. You can also use the option -dry-run which creates and alters the new table, but does not create triggers, copy data, or replace the original table.

If necessary, the user and password can be set by --user=hopefullynotroot and --password=hopefullynotpassword.

pt-online-schema-change --alter "add column my_column INT UNSIGNED" D=my_database,t=my_table --alter-foreign-keys-method auto --execute --user=hopefullynotroot --password=hopefullynotpassword

Please read the documentation for more information.

Other tools

There are some other tools which do a similar job:

• gh-ost - GitHub's online schema migration for MySQL
• Facebook OSC - Facebook's OnlineSchemaChange