《mysql数据库 slave复制异常问题解决办法.docx》由会员分享，可在线阅读，更多相关《mysql数据库 slave复制异常问题解决办法.docx（6页珍藏版）》请在三一办公上搜索。
1、mysql数据库 slave复制异常问题解决办法mysql数据库 slave复制异常问题解决办法 slave是主从复制了,但小编在测试mysql slave主从复制出现了问题了,在此PHP粉丝网小编来为各位介绍mysql数据库 slave复制异常问题解决办法,希望文章对各位有用. 以下是两种slave复制异常的情况。导致的原因都是由于跨机房同步,slave的机房突然掉电导致的。 案例一:这个错误大原因是Read_Master_Log_Pos: 1028687822的pos号在主库上是没有的. 处理方法:获取这个pos号的前一个pos号,从新开启同步,这里注意如果是row模式的话就没有问题.如果
2、是mix的或者statement的话,就需要去分析binlog,确认具体执行到哪个pos号了,不然可能会导致数据不一致. (andy:db:)(none) 11:18:39 show slave statusG * 1. row * Slave_IO_State: Master_Host: 192.168.11.24 Master_User: slave Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000342 Read_Master_Log_Pos: 1028687822 Relay_Log_File: loc
3、alhost-relay-bin.000767 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000342 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter:
4、 0 Exec_Master_Log_Pos: 1028687822 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_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: No Last_IO_Errn
5、o: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Client requested master to start replication from position file size; the first event mysql-bin.000342 at 1028687822, the last event read from ./mysql-bin.000342 at 4, the last byte read from ./mysql-bin.00034
6、2 at 4. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1024 Master_UUID: e9143523-c116-11e2-a8a1-0022195d25da - Master_Info_File: /usr/local/mysql-6/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waitin
7、g for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 140725 06:55:26 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) 案例二:这个是由于slave的relay-log损坏了.可以考虑使用Exec
8、_Master_Log_Pos: 439512771pos号,从新开启同步.或者使用relay_log_recovery=ON参数启动. (root:bi:)(none) 10:56:01 show slave statusG * 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 172.20.100.16 Master_User: slave Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000076 Read_Maste
9、r_Log_Pos: 474667384 Relay_Log_File: localhost-relay-bin.000137 Relay_Log_Pos: 212337426 Relay_Master_Log_File: mysql-bin.000076 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignor
10、e_Table: Last_Errno: 1594 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinl
11、og on the relay log), a ork problem, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves relay log, you will be able to know their names by issuing SHOW SLAVE STATUS on this slave. Skip_Counter: 0 Exec_Master_Log_Pos: 439512771 Relay_Log_Space: 4746696
12、81 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1594 Last_SQL_Err
13、or: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinlog on the relay log), a work probl
14、em, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves relay log, you will be able to know their names by issuing SHOW SLAVE STATUS on this slave. Replicate_Ignore_Server_Ids: Master_Server_Id: 1016 Master_UUID: 7b6b6934-b981-11e3-bc82-c81f66de76ac Ma
15、ster_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 140725 10:54:50 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Posit
16、ion: 0 1 row in set (0.00 sec) rootlocalhost data# mysqlbinlog localhost-relay-bin.000137 a.txt ERROR: Error in Log_event:read_log_event: read error, data_len: 7736, event_type: 31 ERROR: Could not read entry at offset 215681738: Error in log format or read error. WARNING: The range of inted events
17、ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a -stop-position or -stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output. 总结:这两个错误的导致的原因是机房掉电,也就是没有crash safe.
