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. By Michi Lehr.
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 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.
# Example of dropping a column.
pt-online-schema-change --alter "drop column my_column" D=my_database,t=my_column --alter-foreign-keys-method auto --execute
Pretty straightforward. You will also get the links to similar tools. Nice one.
[Read More]