刚刚接到报警短信,从库宕机,马上通知机房重启,在检查MySQL时,发现同步挂了,报主键冲突,询问开发是不是有往里面写数据,回答没有。
这就奇怪了,怎么会无缘无故报错呢?在检查了my.cnf配置文件,发现有个参数没有配置:
1
|
innodb_overwrite_relay_log_info = 1 |
当从库宕机后,重新开启主从复制同步,它可以重新执行已提交事务,这样就会造成同步失败,而这个参数就会避免这个问题的出现。
当开启了这个参数后
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
.... + InnoDB: Warning: innodb_overwrite_relay_log_info is enabled. Updates of other storage engines may have problem of consistency.
+ InnoDB: relay-log.info is detected.
+ InnoDB: relay log: position 429 , file name ./gauntlet3-relay-bin. 000111
+ InnoDB: master log: position 280 , file name gauntlet3-bin. 000015
.... InnoDB: Starting crash recovery.
.... InnoDB: Apply batch completed
+ InnoDB: In a MySQL replication slave the last master binlog file + InnoDB: position 0 468 , file name gauntlet3-bin. 000015
+ InnoDB: and relay log file + InnoDB: position 0 617 , file name ./gauntlet3-relay-bin. 000111
090205 17 : 41 : 31 InnoDB Plugin 1.0 . 2 - 3 started; log sequence number 57933
+ InnoDB: relay-log.info have been overwritten. .... 090205 17 : 41 : 31 [Note] Slave SQL thread initialized, starting replication in log ``gauntlet3-bin. 000015 `` at position 468 , relay log ``./gauntlet3-relay-bin. 000111 `` position: 617
|
已经执行完的Position点:
master log: position 280, file name gauntlet3-bin.000015
在恢复时它内部会检测到280这个点已经执行完毕,从下一个点468开始同步,并且重写relay.info文件,确保了主从同步正确。
建议在从库上添加,如果是官方MySQL,参数是relay_log_recovery=1
具体请参考:http://www.percona.com/doc/percona-server/5.5/reliability/crash_resistant_replication.html
本文转自hcymysql51CTO博客,原文链接: http://blog.51cto.com/hcymysql/1332012,如需转载请自行联系原作者