一次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 'repl@xxx.xxx.xxx.xxx: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。