Call us Toll-Free:
1-800-218-1525
Live ChatEmail us

 Sponsors

How to check MySQL Replication databases are in Sync

Dawn Rossi, 07-20-2009
In a previous post, Kyle covered How to fix MySQL Replication, by using a script that looks up records in the master/slave, inserting any missing records as well as updating any ones that don't exactly match.

But how can you tell your replicated MySQL databases are no longer in sync?

Since some queries are never safe to run in a replicated environment (i.e. DELETE FROM table LIMIT 1), without having the proper experience, it's easy to develop replication inconsistencies that could lead to fatal errors.

The easiest way to monitor replicated databases are in-sync is by using MySQL built-in CHECKSUM function, that calculates a checksum for each table based on the values, number of rows and number of columns in the table.

Calculating a table checksum is a very quick operation!

If a table on the master generates a different checksum than the same table on the slave, it means the two are not in sync and you should look deeper.

The code below handles verifying two databases (master/slave or master/master), printing a list of all tables where the checksum doesn't match:


$dbname
="YOUR-DATABASE-NAME";

// Connect to master database
mysql_connect("MASTER-DB","USERNAME","PASSWORD");
mysql_select_db($dbname);

// Get all tables
$Result = @mysql_query("show tables");
$cnt = @mysql_num_rows($Result);
while (
$cnt)
{
 
$cnt--;
  if (!(
$Row = @mysql_fetch_array($Result))) continue;

 
$tablename = $Row[0];

 
$Result1 = @mysql_query("checksum table $tablename");
 
$Row1 = @mysql_fetch_row($Result1);

 
$arr_tables_db[$tablename] = $Row1[1];
}

mysql_connect("SLAVE-DB","USERNAME","PASSWORD");
mysql_select_db($dbname);

// Get all tables
$Result = @mysql_query("show tables");
$cnt = @mysql_num_rows($Result);
while (
$cnt)
{
 
$cnt--;
  if (!(
$Row = @mysql_fetch_array($Result))) continue;

 
$tablename = $Row[0];

 
$Result1 = @mysql_query("checksum table $tablename");
 
$Row1 = @mysql_fetch_row($Result1);

 
$arr_tables_db3[$tablename] = $Row1[1];
}

// Now display mismatches
foreach ($arr_tables_db as $name => $checksum)
{
  if (
$checksum != $arr_tables_db3[$name])
  {
    echo
"- $name\r\n";
  }
}

I strongly recommend setting this up on a cronjob, so that you identify any replication issues as quickly as possible.

Be sure to also read my post about a Better MySQL Replication monitor. Don't trust MySQL built-in "Seconds behind slave" reading, it's not reliable.

Steve, 06-26-2011
Thank you for this info. This was exactly what I was looking for!!!

Rakesh, 06-05-2012
This is a good script. Just one question, can I run this while database is open for transactions/queries. Means, does it lock table/database while generating checksum on master and slave tables.

myownstars, 04-11-2013
2 concerns:
1
if the database is too large, for example, more than 1T, is this method still avialable?
2
the script firstly check master then salve,
how about if some updates happen on slave table between it
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions