In a MySQL Master/Slave replication, what would happen if I write to the Slave?

Question

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?

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.

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)

• mk-table-checksum
• mk-table-sync

or

• pt-table-checksum
• pt-table-sync

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.