Performing real-time schema changes on MySQL (InnoDB) tables, using percona-toolkit

Category: Software and Systems Engineering
Author: Sean O'Donnell
Sat, Nov. 15th, 2014 @ 1:27:40 (MST)

If you need to perform real-time ALTER TABLE processes on MySQL (InnoDB) tables, a great tool for the job is the Percona Toolkit.

The Percona Toolkit includes a utility (pt-online-schema-change) to perform such a process, without write-locking tables, or having to manually create a temporary table and triggers to synchronize the data during the process.

It's also very strict in terms of syntax and prerequisites to even perform such a task. For instance, you must have a unique identifier or primary key already existing within the table you are trying to alter, or at the very least, a column that can be used as such (which is often rare with tables that didn't include one to begin with).

A common problem that I've encountered when dealing with projects that have database tables that need real-time "surgery" due to performance or "data-integrity" issues, is insufficient use of INDEX, Foreign Key Constraints, and at worst... unique identifiers.

While there is not much you can do for a table that has no usable column to assign as a unique identifier using this tool, you can use it to perform "surgery" on just about any other ailment your database schema may suffer from.

Convert an existing table ENGINE to InnoDB

Convert the user_avatars table from MyISAM, to InnoDB.

pt-online-schema-change --ask-pass --execute --alter "ENGINE=InnoDB" -D=myschema,-t=user_avatars

ADD COLUMN to existing table

For what ever reason, let's assume that this table did not include a Foreign Key relating to column. Let's create one.

pt-online-schema-change --ask-pass --execute --alter "ADD COLUMN uid INT(11) NOT NULL DEFAULT '0'" -D=myschema,-t=user_avatars

ADD INDEX to existing table column

We want to index the uid column to improve performance for SQL queries that include the uid column in the WHERE and/or GROUP BY clauses.

pt-online-schema-change --ask-pass --execute --alter "ADD INDEX uid (uid)" -D=myschema,-t=user_avatars

CONSTRAIN FOREIGN KEY to existing table column

We also want to constrain the user_avatars.uid column to the 'id' Foreign Key from the 'user' table (

pt-online-schema-change --ask-pass --execute --alter "CONSTRAINT fk_uid FOREIGN KEY (uid) REFERENCES user (id)" -D=myschema,-t=user_avatars 

This is just an example of using the pt-online-schema-change command to perform much needed surgery on a medium/large InnoDB table, in real-time. If you're using MySQL in a replication environment, then you should definitely read-up on the documentation for the replication-related commands.

Copyleft (<) 1998-2019