As I’ve been more and more experienced as a MySQL DBA it seems as though I’m going from brick wall to brick wall. Fortunately I came across pt-online-schema-change after running into some issues with running large table alterations on a production environment.

The way pt-online-schema-change implements the non-blocking schema change is pretty clever. The utility creates a duplicate schema to the current table, then runs the alter command on the new schema, once the new schema is created triggers are added to the original table for update, delete, and inserts. The triggers add the newly altered information to the altered schema.

The utility chunks the current data and inserts the chunks into the newly altered table. Finally the tables are swapped, the triggers are dropped, and the newly demoted table is deleted. From what I noticed pt-online-schema-change pre-v2.1 sets SQL_BIN_LOG to 0 by default unless you specified –bin-log. This WILL break replication if the table schema is changed on the master but is not replicated to the slave; the break could happen much, much later too. It really depends on your environment and when you first specify the new alteration in a query. I totally didn’t have this happen to me…

How to use

The basics of how to use pt-online-schema-change are extremely quick pick up. It is pretty much like any other MySQL utility in that you specify your host, username, password, as well as the database and table. However it can be a bit annoying to remember the format for specifying the database and tablename. But I digress; let’s say that you want to change the table cookie.peanutbutter from MyISAM to InnoDB.

$ pt-online-schema-change -ucookie -pmonster -hcookiejar --alter="ENGINE=InnoDB" --execute D=cookie,t=peanutbutter

Essentially you can specify any table alteration (omitting the ALTER TABLE statement prefix) to pt-online-schema-change and it should work. In general I’ve found it is preferable to run online schema changes against any table using a auto-incrementing primary key. However you can always take a more manual approach to the chunking by using –chunk-index, –chunk-index-columns, and –chunk-size.