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.

I'm running traditional master/slave replication on MySQL 5.1.52, my master is set for statement based replication. A few tables are missing rows on the slave (differing row counts), these tables are insert only on the master using "load data infile ...", with auto-increment Pks. It is strange that the missing rows on the slave have gaps in the PK values where the rows should be. Slave is marked read-only so there are no accidental deletes.

share|improve this question
    
IMO, the second part of this question (regarding load data infile) should be a separate question. –  Derek Downey Mar 1 '12 at 20:20
    
I created a new question at dba.stackexchange.com/questions/14227/… –  tmcallaghan Mar 1 '12 at 20:39
    
The accepted answer is excellent at fixing the problem, but seems to miss the mark on diagnosing the problem. I have the same issue, and while I can now fix it easily I still have no idea what caused it or whether it may happen again. –  Mike S. Mar 16 '12 at 14:54

1 Answer 1

up vote 4 down vote accepted

The traditional way would be to use pt-table-checksum and pt-table-sync

I like doing things a little different. I immediately run pt-table-sync with the --sync-to-master --print options.

Here is the --sync-to-master option

--sync-to-master

Treat the DSN as a slave and sync it to its master.

Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS, connect to the server’s master, and treat the master as the source and the slave as the destination. Causes changes to be made on the master. Sets --wait to 60 by default, sets --lock to 1 by default, and disables --[no]transaction by default. See also --replicate, which changes this option’s behavior.

Here is the --print option

--print

Print queries that will resolve differences.

If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.

This, if you do something like this:

echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql

The file /root/SQLChanges.sql will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.

With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.

share|improve this answer
    
Do you have more information about why MySQL considers "load data infile" unsafe and switches to RBR if running in mixed mode, does that mean it is only unsafe when using SBR? –  tmcallaghan Mar 1 '12 at 20:08
    
Here is the MySQL Documentation on this : dev.mysql.com/doc/refman/5.5/en/… and dev.mysql.com/doc/refman/5.5/en/…. –  RolandoMySQLDBA Mar 1 '12 at 20:19
    
The documentation considers it unsafe and switches, if in mixed mode, to row based replication. There is no explanation what is meant by unsafe (assume SBR) or if RBR is completely safe. –  tmcallaghan Mar 1 '12 at 20:34

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.