The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.
SCENARIO #1
You may want to monitor Relay_Log_Space. It represents the some total of relay logs the Slave has to process. When should you worry about it?
Give this scenario
Slave_IO_Running is YesSlave_SQL_Running is No
This indicates that that the IO Thread is still downloading entries from its Master's binary logs. The longer you take to address the SQL error, the more the Relay_Log_Space will pile up. If the Master is in a heavy-write environment, relay logs will pile up quickly and puts the Slave at risk for disk space issues.
If you do not want to monitor Relay_Log_Space, you can always set the relay_log_space_limit. This force a cap on the amount of disk space allow for relay logs. For example, if you set this:
[mysqld]
relay_log_space_limit=8G
and restart mysql, this causes the IO Thread to stop collecting new entries from the Master when the sum of all relay log file sizes >= 8GB.. After the SQL Thread is done processing the oldest relay log, that relay log gets rotated out and the IO Thread starts getting entries from the Master where it left off. This provides a decent throttling mechanism for relay logs.
Another field you may want to look at is Last_SQL_Errno. Once Slave_IO_Running is Yes
and Slave_SQL_Running is No, the Last_SQL_Errno will be set to something nonzero. On very rare occasions, it is possible to have this:
Slave_IO_Running is YesSlave_SQL_Running is NoLast_SQL_Errno is 0
When you have this unique phenomenon, mysqld has encountered an undocumented error (Don't laugh, I have seen this).
SCENARIO #2
Something else to keep in mind (not necessarily for monitoring)
Give this scenario
Slave_IO_Running is NoSlave_SQL_Running is No
Under what three circumstances would this occur?
- Someone ran
STOP SLAVE; by hand or in a backup script
- The option skip-slave-start is in /etc/my.cnf and mysql was started
- Issuing
service mysql start --skip-slave-start or service mysql restart --skip-slave-start
Make sure not to monitor false positives should both replication threads are not running.
SCENARIO #3
Give this scenario
Slave_IO_Running is NoSlave_SQL_Running is Yes
This quickly indicates that there is a communication error because of one of the following
- The Master went down
- mysqld on the Master went down
- Network Intermittency
Best Answer
Once I have asked the similar question there was ultimate Answer. Please have a look at it.
Then you will be clear with the
The Seconds_Behind_Master is not a good Parameter for monitoring the replication.
Try to use
mk-heartbeatmonitoring script. I use that and it's really reliable.For documentation of mk-heartbeat, click here.
UPDATE
Try MONyog Web based application which has separate Replication tab and pretty easy to use, with lots of features
This will let you know Status of all slaves and Master on Replication tab for example: Replication topology, Master detail, Slave detail in a tabular format. Also, using Monitors page, you can get alert if seconds_behind_master lags behind certain threshold value.
You can also use the MySQL Community Replication Monitor
The community driven MySQL replication monitoring tools is a collection of scripts in SQL and other languages, to achieve the same result that others can get with expensive applications.