Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
please explain your question properly... –  Abdul Manaf Dec 16 '11 at 7:00
    
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 Dec 16 '11 at 9:51
add comment

1 Answer

up vote 2 down vote accepted

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.

EXAMPLE

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)

or

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.

share|improve this answer
    
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 Dec 16 '11 at 15:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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