mysql replication - master to slave

Question

I have successfully set up a master to slave environment and it is definitely working fine.

The only problem I have is that selecting count from a table, they are not the same BUT selecting after 5 mins from master, 50 rows are created while on the slave, also 50 rows are created (that's why I said i'm sure that is working fine)

Master:

+----------+
| COUNT(*) |
+----------+
|    77634 |
+----------+
1 row in set (0.00 sec)

Slave:

+----------+
| COUNT(*) |
+----------+
|    76932 |
+----------+
1 row in set (0.00 sec)

Any idea why this happened? is it possible that when I changed the slave to point to the master using the 'CHANGE MASTER TO' command, the position of binary log file @ the Master moved already?

Answer 1

MySQL replication is not "reliable", nor is it able to automatically re-sync if it goes wrong. There are lots of ways it can go wrong even without unplanned reboots etc.

You need to ACTIVELY monitor it, to stand any chance of it working for any length of time.

You need, at the very least, to do two things:

1. Check the output of SHOW SLAVE STATUS (on each slave) to ensure that the threads are running, no errors reported and seconds_behind_master is not too much.
2. Periodically run some kind of consistency check on each slave / master - I recommend mk-table-checksum with the --replication option

And hook the output of those checks up to your monitoring system so that your operations staff get alerted.

Your Ops staff also need to know how to fix it (dump / restore, or some other fix). You will definitely need to write some kind of knowledge-base article for Ops.

I did this before - it is not trivial and you can get it wrong easily.

Answer 2

Try 'SHOW SLAVE STATUS' on the slave to see if any errors have occured.

You can also try load data from master in order to re-establish the synchronization.