Check if MySQL Replication is in sync

Question

The MySQL documentation about CHECKSUM TABLE is very minimalistic.

My question: Is CHECKSUM TABLE a good way of checking if a MySQL Slave Table is in sync with the master? In other words: Can i use the checksums to verify that the slave table is equal to the master table?

I can't use Seconds_Behind_Master indicator because of changes only made in the slave table.

Why not use the documented [SHOW SLAVE STATUS](http://dev.mysql.com/doc/refman/5.1/en/replication-administration-status.html) SQL statement? — HBruijn, Apr 29 '14 at 15:18
for example i change the slave table - the table differs from the master table. i can't track this using show slave status — Informant, Apr 29 '14 at 15:20
You can take specific tables out of replication if they change in the slave server, then you can use "show slave status" — LinuxDevOps, Apr 29 '14 at 15:48

score 2 · Accepted Answer · answered Apr 29 '14 at 15:31

2

CHECKSUM TABLE is a good way. Instead of running it directly, take a look at pt-table-checksum.

Once you ensure it's in sync, You should make the slave read-only so you can't change its tables.

answered Apr 29 '14 at 15:31

sciurus

12,678
2
31
49

pt-table-checksum is the right way to check data consistency between master and slave: 1) you don't need to stop replication 2) it's not affecting MySQL performance. just one note - revoke SUPER from everybody, but root account, set slave read-only and only then run pt-table-checksum – akuzminsky Apr 29 '14 at 15:51
thx, here's my first python script that runs through some mysql servers (username, password, database name must be the same) and prints checksums and line count of the tables – Informant Apr 29 '14 at 21:50
http://pastebin.com/PENkCyRR – Informant Apr 29 '14 at 21:51

score 1 · Answer 2 · answered Apr 29 '14 at 15:44

It's generally considered good (best?) practice to make secondary servers read only to help prevet just what you're trying to detect.

set global read_only =1;

or

read_only=1

in the my.cnf file. This doesn't stop users with SUPER privilege from making changes though so you should also remove that from accounts that don't need it

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

Then only use unprivileged accounts to work with the databases and the root account for when you need SUPER Privilege.

Check if MySQL Replication is in sync

2 Answers2