Thesql replication missed quite a lot SQL statement

binlogMySQLreplication

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?

Best Answer

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 ...).