MySQL主从失败 错误Got fatal error 1236解决方法

<p>由于主服务器异外重启, 导致从报错, 错误如下:</p><p>show slave status错误:</p><p>mysql&gt; show slave status\G</p><p>Master_Log_File: mysql-bin.000288</p><p>Read_Master_Log_Pos: 627806304</p><p>Relay_Log_File: mysql-relay-bin.000990</p><p>Relay_Log_Pos: 627806457</p><p>Relay_Master_Log_File: mysql-bin.000288</p><p>Slave_IO_Running: No</p><p>Slave_SQL_Running: Yes</p><p>Exec_Master_Log_Pos: 627806304</p><p>Relay_Log_Space: 627806663</p><p></p><p>......</p><p>Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:</p><p>&#39;Client requested master to start replication from impossible position&#39;</p><p>mysql错误日志:</p><p>tail /data/mysql/mysql-error.log</p><p>111010 17:35:49 [ERROR] Error reading packet from server: Client requested master</p><p>to start replication from impossible position ( server_errno=1236)</p><p>111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data</p><p>from binary log: &#39;Client requested master to start replication from impossible</p><p>position&#39;, Error_code: 1236</p><p>111010 17:35:49 [Note] Slave I/O thread exiting, read up to log &#39;mysql-bin.000288&#39;,</p><p>position 627806304</p><p>按照习惯, 先尝试必改position位置.</p><p>mysql&gt; stop slave;</p><p>mysql&gt; change master to master_log_file=&#39;mysql-bin.000288&#39;,master_log_pos=627625751;</p><p>mysql&gt; start slave;</p><p>错误依旧, 接下来登陆到主服务器查看binlog日志.</p><p>先按照错误点的标记去主服务器日志中查找:</p><p>[root@db1 ~]# mysqlbinlog --start-position=627655136 /data/mysql/binlog/mysql-bin.000288</p><p>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</p><p>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</p><p>DELIMITER /*!*/;</p><p># at 4</p><p>#111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log</p><p>created 111010 13:31:19</p><p># Warning: this binlog is either in use or was not closed properly.</p><p>BINLOG &#39;</p><p>F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</p><p>AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC</p><p>&#39;/*!*/;</p><p>DELIMITER ;</p><p># End of log file</p><p>ROLLBACK /* added by mysqlbinlog */;</p><p>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</p><p>没有看到这个位置.</p><p>[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 &gt; test.txt</p><p>less text.txt</p><p>看最后一部分</p><p># at 627625495</p><p>#111010 16:35:46 server id 1 end_log_pos 627625631 Query thread_id=45613333</p><p>exec_time=32758 error_code=0</p><p>SET TIMESTAMP=1318289746/*!*/;</p><p>delete from freeshipping_bef_update where part=&#39;AR-4006WLM&#39; and code=&#39;&#39;</p><p>/*!*/;</p><p># at 627625631</p><p>#111010 16:35:46 server id 1 end_log_pos 627625751 Query thread_id=45613333</p><p>exec_time=32758 error_code=0</p><p>SET TIMESTAMP=1318289746/*!*/;</p><p>delete from shippingFee_special where part=&#39;AR-4006WLM&#39;</p><p>/*!*/;</p><p>DELIMITER ;</p><p># End of log file</p><p>ROLLBACK /* added by mysqlbinlog */;</p><p>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</p><p>找到最接近错误标记627655136的一个position是627625631.</p><p>再回到slave机器上change master, 将postion指向这个位置.</p><p>mysql&gt; stop slave;</p><p>Query OK, 0 rows affected (0.00 sec)</p><p>mysql&gt; change master to master_log_file=&#39;mysql-bin.000288&#39;,master_log_pos=627625631;</p><p>Query OK, 0 rows affected (0.06 sec)</p><p>mysql&gt; start slave;</p><p>Query OK, 0 rows affected (0.00 sec)</p><p>再次查看</p><p>mysql&gt; show slave status\G</p><p>*************************** 1. row ***************************</p><p>Slave_IO_State: Queueing master event to the relay log</p><p>Master_Host: 192.168.21.105</p><p>Master_User: rep</p><p>Master_Port: 3306</p><p>Connect_Retry: 10</p><p>Master_Log_File: mysql-bin.000289</p><p>Read_Master_Log_Pos: 25433767</p><p>Relay_Log_File: mysql-relay-bin.000003</p><p>Relay_Log_Pos: 630</p><p>Relay_Master_Log_File: mysql-bin.000289</p><p>Slave_IO_Running: Yes</p><p>Slave_SQL_Running: Yes</p><p>主从同步正常了, 同样的方法修复其它slave机器.</p>
RangeTime:0.006073s
RangeMem:211.59 KB
返回顶部 留言