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?

  • 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)? Jan 6 '14 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. Jan 6 '14 at 9:12
  • slave-skip-errors should be irrelevant, it the statements are missing in the binary logs. Jan 6 '14 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... Jan 6 '14 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 Jan 7 '14 at 1:29

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 ...).
  • 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 Jan 7 '14 at 1:33
  • @welkinwalker, note that unless you set log_slave_updates, you won't find the replication stream in the slave binlogs ... Jan 7 '14 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. Jan 7 '14 at 14:40
  • The other possibility is you have some Replication filter. I would use replicate-wild-ignore-table = mysql.% on Slave only. Can I ask why you're preventing replication of performance_schema? Sep 24 '15 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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