Comparing Data Between Master and Slave on Mysql

In a replicated environment it is quite common that during the initial stages of setup, frequent replication breaks happen. The break happens because the Slave resulted differently from what was observed in the Master. This usually happens with statement-based replication which is due to many reasons like duplicate keys, partial success of a bulk update that was not encapsulated in a transactional clause, deadlocks and many more.


Often the solution is to skip the error and restart replication to the next position in the log. This will potentially catch off guard the unwary database administrator or developer until it has become too late - the master tables are no longer in sync with the slaves. This event is particularly a huge problem if the system,

1) can't be put offline,
2) have huge data set,
3) have slaves that are located remotely

So, the best thing to do is make sure that critical tables are always in Sync. The solution - comparing the slave proactively and regularly to detect anomalies even before they get too big to handle. And, in order to get an efficient comparison without having to expensively iterate over your records, you would only need two things:


Get the Number of Records per Table

Get the Checksum or Digest of the Table Derived from the Individual Checksums of Each Row Within the Table

Related Links:

• Related Links Widget for Blogspot