一次mysql复制故障排除

最新推荐文章于 2021-02-03 15:03:18 发布

wang_quan_li

最新推荐文章于 2021-02-03 15:03:18 发布

阅读量1k

点赞数

分类专栏： Mysql 文章标签： mysql 故障

Mysql 专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一种解决MySQL主从复制中出现的1236错误的方法，该错误通常由于主备服务器上的二进制日志文件不一致导致。通过调整复制位置并重新同步，成功解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

环境是两台主机互为主从，一台服务器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=1273exec_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;

结果显示一切正常。搞定！