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 have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status:

         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

And when I modified the content on the master manually, whether it was select or update or insert, or alter table, the modification will be synchronized to the slave instantly.

However, after running for several days, I found the slave missed a lot of insert statements, these insertion statements didn't violate any PRIMARY key rule. More ever, when I tried to redo the binlog on another slave with:

mysqlbinlog mysql-binlog.00000X | mysql

Those missed statement were missed again with no warning or error.

Have you ever met such a situation, what should I do to restore all the changes to the slave? (There are quite a lot of missed changes, so I could not restore them one by one).

I dug into this matter to find that, the relay log on the slave contains all the insertion statement, which means the binlog is transmitted to the slave correctly. However, the binlog on the slave missed some of the insertion statement, so this issue appeared to occurred during the redo process of the slave.

Any suggestions to diagnose into this issue or work around it?

share|improve this question

migrated from stackoverflow.com Jan 6 at 8:45

This question came from our site for professional and enthusiast programmers.

    
Are there any errors or warnings in the logs (in master and slave)? Do you have --slave-skip-errors option enabled? What engines do the tables use (and do all the tables have the same definition in master and slave)? –  ypercube Jan 6 at 8:58
    
No error or warning. I used slave-skip-errors=1062, so the duplicated requests were ignored, but the missed insertions are not duplicated. I used myisam engine, I copied the table files from the master to slave. BTW, for a specific talbe, not all the insertions were failed. –  welkinwalker Jan 6 at 9:12
    
slave-skip-errors should be irrelevant, it the statements are missing in the binary logs. –  Dimitre Radoulov Jan 6 at 12:55
    
Are you configured to replicate all databases/all tables (the default) or are you configured to replicate selectively with "replicate-xxx" or "binlog-xxx" configuration options? Those options have numerous side-effects... –  Michael - sqlbot Jan 6 at 13:39
1  
Selectively. On the master I configured binlog-ignore-db=mysql, on the slave I configured replicate-ignore-db = mysql,information_schema. The other 8 databases are all replicated. So do you mean the side effects might caused the issue? But for a specific table, not all the insertion statement is missed –  welkinwalker Jan 7 at 1:29

1 Answer 1

Two possibilities come to mind:

  1. *SQL_LOG_BIN* was temporary turned off.
  2. Your master server crashed without synchronizing the binary log
    (and most probably *SYNC_BINLOG* was set to a value different than 1 ...).
share|improve this answer
    
1. SQL_LOG_BIN is not turned off, I can see all the insertion statement in the binlog. 2. The master is working fine, and the relay log on the slave contains all the insertion statement too. However, the binlog on the slave missed some of the insertion statement which might denoted these statements were missed during the redo process –  welkinwalker Jan 7 at 1:33
    
@welkinwalker, note that unless you set log_slave_updates, you won't find the replication stream in the slave binlogs ... –  Dimitre Radoulov Jan 7 at 13:33
    
Yes, I configured that option in the [mysqld] section of /etc/my.cnf. So the SQL statement in bin-log of slave should be alike with its relay-log. But I could see its bin-log missed some inter statements. –  welkinwalker Jan 7 at 14:40

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.