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:
- Install the Percona Toolkit on the MySQL server(s) where you will be making schema changes.
- Use the pt-online-schema-change tool to generate a SQL statement that will make the desired schema changes:
|
1 2 3 4 |
pt-online-schema-change \ --alter "ADD COLUMN new_column INT" \ --execute \ D=mydatabase,t=mytable |
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.
- Monitor the progress of the schema change:
|
1 2 3 4 5 |
pt-online-schema-change \ --alter "ADD COLUMN new_column INT" \ --execute \ --progress time,30 \ D=mydatabase,t=mytable |
This will display a progress report every 30 seconds, showing how many rows have been copied and how much time is remaining.
- Monitor the replication status to make sure replication is keeping up with the changes:
|
1 2 3 4 5 6 7 |
pt-heartbeat \ -D mydatabase \ -u heartbeat \ -p mypassword \ --create-table \ --interval=1 \ --master-server-id=1 |
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.
Conclusion
Utilizing pt-online-schema-change from the Percona Toolkit provides a safe and efficient method to add indexes or columns in MySQL without disrupting replication. By following a structured approach, including monitoring the schema change progress and replication status, you can ensure smooth schema modifications.
