故障场景&＃xff1a;昨天自建机房突然断电&＃xff0c;检查一下数据库状态以及主从复制状态(crmDB)root&＃64;localhost [(none)]> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Master_Host: 172.16.117.247Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000262Read_Master_Log_Pos: 342439502Relay_Log_File: relay-bin.000042Relay_Log_Pos: 342439665Relay_Master_Log_File: mysql-bin.000262Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 342439502Relay_Log_Space: 342439879Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1236Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: &＃39;Client requested master to start replication from position > file size; the first event &＃39;mysql-bin.000262&＃39; at 342439502, the last event read from &＃39;/data/data/mysql-bin.000262&＃39; at 4, the last byte read from &＃39;/data/data/mysql-bin.000262&＃39; at 4.&＃39;Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 247Master_UUID: db5c1497-9cf4-11e3-8259-77a6dd604747Master_Info_File: /data/3306/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 150717 09:59:39Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
1 row in set (0.00 sec)(crmDB)root&＃64;localhost [(none)]>
根据报错信息&＃xff0c;看看主库binlog发生了啥mysqlbinlog --start-position&＃61;342439502 /data/data/mysql-bin.000262 > /tmp/binlog.tx
[root&＃64;crmdbM data]# cat /tmp/binlog.txt
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/;
/*!40019 SET &＃64;&＃64;session.max_insert_delayed_threads&＃61;0*/;
/*!50003 SET &＃64;OLD_COMPLETION_TYPE&＃61;&＃64;&＃64;COMPLETION_TYPE,COMPLETION_TYPE&＃61;0*/;
DELIMITER /*!*/;
# at 4
#150716 4:10:02 server id 247 end_log_pos 120 CRC32 0x0b64a00d Start: binlog v 4, server v 5.6.14-log created 150716 4:10:02
BINLOG &＃39;
Gr6mVQ/3AAAAdAAAAHgAAAAAAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQ2g
ZAs&＃61;
&＃39;/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/;
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;################################
没有看到这个位置&＃xff0c;这是mysql-bin.000262最开头的位置&＃xff0c;[root&＃64;crmdbM data]# mysqlbinlog /data/data/mysql-bin.000262 | head -n 10
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/;
/*!40019 SET &＃64;&＃64;session.max_insert_delayed_threads&＃61;0*/;
/*!50003 SET &＃64;OLD_COMPLETION_TYPE&＃61;&＃64;&＃64;COMPLETION_TYPE,COMPLETION_TYPE&＃61;0*/;
DELIMITER /*!*/;
# at 4
#150716 4:10:02 server id 247 end_log_pos 120 CRC32 0x0b64a00d Start: binlog v 4, server v 5.6.14-log created 150716 4:10:02
BINLOG &＃39;
Gr6mVQ/3AAAAdAAAAHgAAAAAAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQ2g
ZAs&＃61;
&＃39;/*!*/;
既然没有&＃xff0c;那么看一下整个binlog最后部分pos点信息&＃xff0c;&＃xff08;报错是说slave IO接受到的pos点信息比主库的还大&＃xff09;
那么我们看一下解析出来的binlog最后几行信息mysqlbinlog /data/data/mysql-bin.000262 > /tmp/binlog1.txt
[root&＃64;crmdbM data]# tail -n 20 /tmp/binlog1.txt
#150717 1:21:06 server id 247 end_log_pos 342403057 CRC32 0xbac8e194 Table_map: &＃96;crm&＃96;.&＃96;sky_consultant_statis&＃96; mapped to number 7724
# at 342403057
#150717 1:21:06 server id 247 end_log_pos 342403240 CRC32 0x38327494 Write_rows: table id 7724 flags: STMT_END_FBINLOG &＃39;
AuinVRP3AAAAaQAAAPGnaBQAACweAAAAAAEAA2NybQAVc2t5X2NvbnN1bHRhbnRfc3RhdGlzACII
DwoRAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwP2AwMDBYcAAAsC8P///wOU4ci6
AuinVR73AAAAtwAAAKioaBQAACweAAAAAAEAAgAi//8AAAAM/Eg&＃43;AAAAAAAACmNoZW5qdW5o
dWnwvg9Vp9UAAAAAABoAAAAAAAAAoyMAAI8AAAAKAAAABAAAAAYAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIUAAAAEAAAAgQAAAAAAAAAAAAAAgAAAAAAAAAAA
AAAAAAAAAACUdDI4
&＃39;/*!*/;
# at 342403240
#150717 1:21:06 server id 247 end_log_pos 342403271 CRC32 0xd0b514e1 Xid &＃61; 2027427035
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/;
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;看到没有&＃xff0c;主库binlog最大才342403240 &＃xff0c;报错信息是342439502 相差36262&＃xff0c;既然从库说已经超过了主库的binlog,SQL线程已经执行到这个位置了
Exec_Master_Log_Pos: 342439502,而且主库断电重启&＃xff0c;重新生成了binlog&＃xff0c;那么我们重新指定下一个binlog文件开始
[root&＃64;crmdbM data]# mysqlbinlog /data/data/mysql-bin.000263 | head -n 20
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/;
/*!40019 SET &＃64;&＃64;session.max_insert_delayed_threads&＃61;0*/;
/*!50003 SET &＃64;OLD_COMPLETION_TYPE&＃61;&＃64;&＃64;COMPLETION_TYPE,COMPLETION_TYPE&＃61;0*/;
DELIMITER /*!*/;
# at 4
#150717 2:29:45 server id 247 end_log_pos 120 CRC32 0xa89426a5 Start: binlog v 4, server v 5.6.14-log created 150717 2:29:45 at startup
ROLLBACK/*!*/;
BINLOG &＃39;
GfinVQ/3AAAAdAAAAHgAAAAAAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAZ&＃43;KdVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaUm
lKg&＃61;
&＃39;/*!*/;
# at 120
#150717 2:40:45 server id 247 end_log_pos 199 CRC32 0xe654b6a6 Query thread_id&＃61;35 exec_time&＃61;0 error_code&＃61;0
SET TIMESTAMP&＃61;1437072045/*!*/;
SET &＃64;&＃64;session.pseudo_thread_id&＃61;35/*!*/;
SET &＃64;&＃64;session.foreign_key_checks&＃61;1, &＃64;&＃64;session.sql_auto_is_null&＃61;0, &＃64;&＃64;session.unique_checks&＃61;1, &＃64;&＃64;session.autocommit&＃61;1/*!*/;
SET &＃64;&＃64;session.sql_mode&＃61;1075838976/*!*/;
SET &＃64;&＃64;session.auto_increment_increment&＃61;1, &＃64;&＃64;session.auto_increment_offset&＃61;1/*!*/;
/*!\C utf8 *//*!*/;
[root&＃64;crmdbM data]#
binlog第一个pos点是4&＃xff0c;就可以指定pos点了(crmDB)root&＃64;localhost [(none)]> change master to master_host&＃61;&＃39;172.16.117.247&＃39;,master_user&＃61;&＃39;slave&＃39;,master_password&＃61;&＃39;slave&＃39;,master_log_file&＃61;&＃39;mysql-bin.000263&＃39;,master_log_pos&＃61;4;
Query OK, 0 rows affected, 2 warnings (0.05 sec)(crmDB)root&＃64;localhost [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)(crmDB)root&＃64;localhost [(none)]> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.117.247Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000265Read_Master_Log_Pos: 60234822Relay_Log_File: relay-bin.000005Relay_Log_Pos: 18238150Relay_Master_Log_File: mysql-bin.000265Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 18237987Relay_Log_Space: 60235199Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 2584
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 247Master_UUID: db5c1497-9cf4-11e3-8259-77a6dd604747Master_Info_File: /data/3306/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: System lockMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
1 row in set (0.00 sec)(crmDB)root&＃64;localhost [(none)]>
没有主键冲突&＃xff0c;然后写个脚本&＃xff0c;在凌晨&＃xff0c;用pt-table-checksum检测一下库是否有主从差异
########最终解决方法&＃xff0c;主库这两个参数
(crmDB247)root&＃64;localhost [(none)]> show global variables like &＃39;%sync_binlog%&＃39;;
&＃43;---------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------&＃43;-------&＃43;
| sync_binlog | 0 |
&＃43;---------------&＃43;-------&＃43;
1 row in set (0.00 sec)(crmDB247)root&＃64;localhost [(none)]> mysql> show global variables like &＃39;%at_trx%&＃39;;
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| innodb_flush_log_at_trx_commit | 2 |
&＃43;--------------------------------&＃43;-------&＃43;
1 row in set (0.00 sec)mysql>
主从复制必须为双1
sync_binlog &＃61; 1
innodb_flush_log_at_trx_commit &＃61; 1
从库必须配置为###
relay_log &＃61; /data/3306/logs/relay-bin
log_slave_updates &＃61; 1
skip_slave_start &＃61; 1
relay_log_recovery &＃61;1
####及时同步sync_master_info sync_relay_log_info
master_info_repository &＃61; TABLE
relay-log-info-repository &＃61; TABLE
京公网安备 11010802041100号