Several Questions

  • Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?

  • If not, how do I ensure that the Slave is in-sync with the Master going forward (either by deleting the Slave entry or copying that entry to the Master manually)?

  • Most importantly, how do I detect that the tables are out-of-sync?

  • please explain your question properly... Commented Dec 16, 2011 at 7:00
  • 1
    Isn't a master/slave relationship a one-way sync from master to slave, but not vice versa? I am asking what would happen if you write to the slave in this kind of set-up.
    – kfmfe04
    Commented Dec 16, 2011 at 9:51

1 Answer 1


As long as you do not perform any INSERT/UPDATE/DELETE statements directly on the Slave, your Slave should be just fine. Otherwise, MySQL Replication could break if you INSERT an new row in mydb.mytable on the Slave and, via Replication, the Slave later detects an INSERT of a row to mydb.mytable with the same PRIMARY KEY. This produces error 1062 (Duplicate Key).

The only way you could write to Slave without breaking MySQL Replication is this:

  • Master has databases db1,db2,db3
  • Slave is replicating databases db1,db2,db3 from the Master
  • You run CREATE DATABASE db4; on the Slave
  • You do INSERTs/UPDATEs/DELETEs only to db4 on the Slave
  • You do not do INSERTs/UPDATEs/DELETEs only to db1,db2,db3 on the Slave

To detect a Master and Slave being out of sync without downloading any tools, pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.


If you have a table mydb.mytable, run the command against it:

mysql> CHECKSUM TABLE mydb.mytable;

If the values do not come back the same, then something is out-of-sync.

If you want to examine a bunch of table in bulk, you can down Percona's MAATKIT. You will need two specific tools (Percona also has the Percona Toolkit that they themselves forked from MAATKIT which is now being promoted more)


pt-table-checksum will perform a CHECKSUM TABLE against all tables on Master and Slave. You can configure it to do all databases on just specific ones.

pt-table-sync can be run on a Slave against any table. Using the --print and --sync-to-master options, you can see what SQL statements need to be executed on the Slave to have it perfectly match the Master. This tool does not work with table that lacks a PRIMARY KEY or UNIQUE KEY.

I have used MAATKIT for years. I still do. I have not tried the Percona Toolkit yet, but I am sure it should be of the same quality as MAATKIT.

  • 1
    ty for the detailed answer. I thought I had more questions, but I think your answers have covered all the cases (pt-table-sync seems to be really useful).
    – kfmfe04
    Commented Dec 16, 2011 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.