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.
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:
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
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:
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.
Download the Percona Toolkit or install using your favourite OS package manager. The Percona Toolkit is written in Perl.
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.
There are some other tools which do a similar job: