How to prevent Replication Break in MySQL while adding INDEX/COLUMN using PT-OSC (pt-online-schema-change)?

When adding indexes or columns in MySQL, it is important to be aware of the potential for replication to break. One way to prevent this is to use a tool like pt-online-schema-change from the Percona Toolkit.

pt-online-schema-change works by creating a new table with the desired schema changes and copying the data from the original table to the new table. During this process, any changes made to the original table are captured and applied to the new table as well. Once the data has been fully copied, pt-online-schema-change will swap the new and original tables, and then drop the old table.

To use pt-online-schema-change to prevent replication breaks, follow these steps:

  1. Install the Percona Toolkit on the MySQL server(s) where you will be making schema changes.
  2. Use the pt-online-schema-change tool to generate a SQL statement that will make the desired schema changes:

This will create a new table with the same schema as mytable, but with a new column named new_column. The –execute option tells pt-online-schema-change to actually perform the schema change.

  1. Monitor the progress of the schema change:

This will display a progress report every 30 seconds, showing how many rows have been copied and how much time is remaining.

  1. Monitor the replication status to make sure replication is keeping up with the changes:

This will create a heartbeat table that will allow you to monitor replication lag in real time. Make sure that replication is keeping up with the changes made by pt-online-schema-change.

By using pt-online-schema-change and monitoring replication status, you can safely make schema changes in MySQL without causing replication breaks.

About Shiv Iyer 444 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.