pt-online-schema change and row based replication

The way online schema changes have historically worked with statement based replication is to create an empty table on a slave, setup triggers to capture changes to a log table, copy the old table to the new table, apply the changes from the log table and atomic rename. This process breaks when using statement based replication because triggers don’t fire on events replicated to the slave. Triggers will fire on the master and the row events for any modified tables just replicate to the slave. This makes traditional online schema change break with statement based replication.

The workaround I’ve seen a few times at the conference is to run schema changes on the master when using row based replication. This is means either significantly reducing replication capacity during a schema change or having the change run really slowly so replication can keep up. Neither of these are optimal. I would like to submit an alternate method. Rather than rely on triggers on the slave simply run another binlog tailer and capture only events related to the table being changed. Triggers and their DDL mess are out of the picture. Then you can apply changes the same way statement based online schema change would and rename the tables.

None of this is required with online ddl and innodb online index defragmentation.

Enjoy.

One Comment

  1. Eric this is great piece on schema change and row based replication. I wonder how data matching tools might make this process simpler?

Leave a Reply