2013/10/23

mysql The LOST_EVENTS incident

masterのbin-log内容:
# at 32383921
#131022 15:52:41 server id 1  end_log_pos 32383978 CRC32 0xa2f68f09
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error

そのせいで、slaveのreplicationが止まった。
        Last_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 32383921

★結論から言えばマスターが悪い。。。。。
If you got a LOST_EVENTS incident on the Slave, consider yourself (kind of) lucky! Because then you know there could be something wrong, and not find out later that your Slave is inconsistent with the Master.


★例の一つ 5.6.10
As any other activity that produces changes, GRANT statements are replicated to MySQL slaves. Regardless of the binary log format setting, such events are always logged in STATEMENT format. It is likely because the command needs to handle more than just updating the contents of a few system tables, so such design allows each slave to fully execute the changes within their own environment.

This design, the fact that system tables are still non-transactional – they use MyISAM, as well as what I believe was an attempt to address another problem, causes an opportunity for a situation, when issuing an incorrect GRANT statement may break replication.

The following command attempts to grant FILE privilege on test database, which of course is not a valid operation, so MySQL refuses it and even gives an explanation of the problem:

master [localhost] {root} ((none)) > GRANT FILE ON test.* TO test@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

This is where it ended in previous MySQL versions. MySQL 5.6, however, makes one more step. It writes a binary log event:

#130409 16:49:02 server id 1  end_log_pos 432 CRC32 0xc2005658
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 432


★A simple FAILED SQL statement breaks MySQL 5.6 replication


解決:
★stop slave;set global Sql_slave_Skip_Counter =1;start slave;show slave status\G;
★Slaveを作り直し。