一次mysql复制故障排除

Eddy 发布于2013-5-5 23:48:28 分类: 技术心得 已浏览loading 网友评论0条 我要评论

环境是两台主机互为主从,一台服务器down了,结果在另外一台服务器上slave无法正常运行。错误信息为:

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236

查看slave错误日志xxx.err文件:

130505 15:17:01 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000013' at position 538467

130505 15:17:02 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
130505 15:17:02 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
在master上没有发现mysql-bin.000013,当前binlog文件名为mysql-bin.000014,因而出现了上面的错误。运行下面命令:
mysql> slave stop;
Query OK, 0 rows affected (0.03 sec)
 
mysql> change master to
    -> master_log_file="mysql-bin.000014",
    -> master_log_pos=538467;
Query OK, 0 rows affected (7.50 sec)
 
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
 
显示如下错误:
                Last_IO_Error: Got fatal error 1236 from master when reading dat
a from binary log: 'binlog truncated in the middle of event; consider out of dis
k space on master; the first event 'mysql-bin.000014' at 538467, the last event
read from '.\mysql-bin.000014' at 538467, the last byte read from '.\mysql-bin.0
00014' at 538486.'
 
说明master故障造成了位置点不对,我们要找到正确的位置。
在master上:
mysqlbinlog mysql-bin.000014 > 1.txt
得到二进制日志的文本格式文件,查找538486附近的事件记录,
# at 538262
#130428 23:02:56 server id 1  end_log_pos 538334 Query thread_id=1273 exec_time=0 error_code=0
SET TIMESTAMP=1367161376/*!*/;
BEGIN
/*!*/;
# at 538334
# at 538411
#130428 23:02:56 server id 1  end_log_pos 538411 Table_map: `meiboshi`.`ecs_sessions` mapped to number 52
#130428 23:02:56 server id 1  end_log_pos 538658 Update_rows: table id 52 flags: STMT_END_F
得到位置应该为538334。
 
slave上运行下面命令:
mysql> slave stop;
Query OK, 0 rows affected (0.03 sec)
 
mysql> change master to
    -> master_log_file="mysql-bin.000014",
    -> master_log_pos=538334;
Query OK, 0 rows affected (0.08 sec)
 
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
结果显示一切正常。搞定!

已经有(0)位网友发表了评论,你也评一评吧!
原创文章如转载,请注明:转载自Eddy Blog
原文地址:http://www.rrgod.com/technique/877.html     欢迎订阅Eddy Blog

记住我的信息,下次不用再输入 欢迎给Eddy Blog留言