I recently encountered a situation in which after running Percona Toolkit’s pt-table-checksum on a customer system, 95% of the table on the MySQL master was different on the MySQL slave. Although this table was not a critical part of the infrastructure, from time to time, writes to the table from the master would break replication. Additionally, this table has about 6 million rows, and running pt-table-sync would take sometime. Rebuilding the slave from backup of the master would not be an easy option as well since the slave acts as an archive where it has a lot more data than the master.
So how did we solve it? With pt-online-schema-change and a NOOP ALTER.
pt-online-schema-change --alter 'ENGINE=INNODB' D=dbname,t=tblname
How is it …
[Read more]