Percona has a great toolkit that allows you to perform schema changes on a MySQL or MariaDB database without any downtime, it works by creating a new table with the same schema, making the changes to it and applying triggers for insertion, deletion and all updates, all while performing your valuable schema updates for you!
How to Add an Index to a MySQL or MariaDB database without downtime
pt-online-schema-change --alter 'add index ix_<COLUMN_NAME> (<COLUMN_NAME>)' D=<DATABASE_NAME>,t=<TABLE_NAME> --dry-run
This will perform a dry-run for you, to let you know what will happen, once you are happy, change the --dry-run for an —execute, as follows:
pt-online-schema-change --alter 'add index ix_<COLUMN_NAME> (<COLUMN_NAME>)' D=<DATABASE_NAME>,t=<TABLE_NAME> --execute
As an example
Let’s say that we have a database called Database1 and a table called Table1 and we want to add an index to a column called Column1, then the command will be as follows:
pt-online-schema-change --alter 'add index ix_Column1 (Column1)' D=Database1,t=Table1 --execute
Download Percona Toolkit
If you’re new to Percona Toolkit, you can grab it over here: https://www.percona.com/doc/percona-toolkit/
