pt-online-schema-change Add Index

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/

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments