问题描述：mysql在启动时报错"Failed to open log xxxxxx/mysql-bin.000003 not found,errno 2"，如下所示：

数据库：mysql 5.5.18

系统：rhel 6.5 64位

架构：一主一从

场景描述：主库最新binlog文件被手动删除后，重启数据库报错.

1、异常重现

230820 14:52:19 InnoDB: 1.1.8 started; log sequence number 1604156

/usr/local/webserver/mysql/bin/mysqld: File '/var/mysql/binlog/mysql-bin.000003' not found (Errcode: 2)

230820 14:52:19 [ERROR] Failed to open log (file '/var/mysql/binlog/mysql-bin.000003', errno 2)

230820 14:52:19 [ERROR] Could not open log file

230820 14:52:19 [ERROR] Can't init tc log

230820 14:52:19 [ERROR] Aborting

 

230820 14:52:19  InnoDB: Starting shutdown...

230820 14:52:19  InnoDB: Shutdown completed; log sequence number 1604156

230820 14:52:19 [Note] /usr/local/webserver/mysql/bin/mysqld: Shutdown complete

 

230820 14:52:19 mysqld_safe mysqld from pid file /var/mysql/data/MySQL5518-Master.pid ended

 

2、解决过程

2.1、重建mysql-bin.000003

[mysql@MySQL5518-Master ~]$ touch /var/mysql/binlog/mysql-bin.000003

[mysql@MySQL5518-Master ~]$ logout

[root@MySQL5518-Master ~]# service mysqld start

Starting MySQL..The server quit without updating PID file ([FAILED]ql/data/MySQL5518-Master.pid).

 

日志文件：

230820 15:03:53 mysqld_safe Starting mysqld daemon with databases from /var/mysql/data

230820 15:03:53 InnoDB: The InnoDB memory heap is disabled

230820 15:03:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins

230820 15:03:53 InnoDB: Compressed tables use zlib 1.2.3

230820 15:03:53 InnoDB: Initializing buffer pool, size = 1.0G

230820 15:03:53 InnoDB: Completed initialization of buffer pool

230820 15:03:53 InnoDB: highest supported file format is Barracuda.

230820 15:03:54  InnoDB: Waiting for the background threads to start

230820 15:03:55 InnoDB: 1.1.8 started; log sequence number 1604156

230820 15:03:55 [ERROR] I/O error reading the header from the binary log, errno=175, io cache code=0

230820 15:03:55 [ERROR] I/O error reading the header from the binary log

230820 15:03:55 [ERROR] Can't init tc log

230820 15:03:55 [ERROR] Aborting

 

230820 15:03:55  InnoDB: Starting shutdown...

230820 15:03:55  InnoDB: Shutdown completed; log sequence number 1604156

230820 15:03:55 [Note] /usr/local/webserver/mysql/bin/mysqld: Shutdown complete

 

230820 15:03:55 mysqld_safe mysqld from pid file /var/mysql/data/MySQL5518-Master.pid ended

 

说明：如上所示，创建同名日志文件后开启mysql进程失败，因为mysql知道文件已不是原有的日志文件.

 

2.2、删除index中的记录信息

[mysql@MySQL5518-Master ~]$ cd /var/mysql/binlog

[mysql@MySQL5518-Master binlog]$ ll

total 12

-rw-rw----. 1 mysql mysql 1404 Aug 20 13:37 mysql-bin.000001

-rw-rw----. 1 mysql mysql  126 Aug 20 13:40 mysql-bin.000002

-rw-rw-r--. 1 mysql mysql    0 Aug 20 15:03 mysql-bin.000003

-rw-rw----. 1 mysql mysql  105 Aug 20 13:40 mysql-bin.index

[mysql@MySQL5518-Master binlog]$ vi mysql-bin.index

说明：删除mysql-bin.000003条目.

/var/mysql/binlog/mysql-bin.000001

/var/mysql/binlog/mysql-bin.000002

/var/mysql/binlog/mysql-bin.000003

 

[mysql@MySQL5518-Master binlog]$ cat mysql-bin.index

/var/mysql/binlog/mysql-bin.000001

/var/mysql/binlog/mysql-bin.000002

 

--系统层面删除mysql-bin.000003

[mysql@MySQL5518-Master binlog]$ ll

total 12

-rw-rw----. 1 mysql mysql 1404 Aug 20 13:37 mysql-bin.000001

-rw-rw----. 1 mysql mysql  126 Aug 20 13:40 mysql-bin.000002

-rw-rw-r--. 1 mysql mysql    0 Aug 20 15:03 mysql-bin.000003

-rw-rw----. 1 mysql mysql   70 Aug 20 15:09 mysql-bin.index

[mysql@MySQL5518-Master binlog]$ rm mysql-bin.000003

[mysql@MySQL5518-Master binlog]$ logout

 

2.3、重启数据库

[root@MySQL5518-Master ~]# service mysqld start

Starting MySQL..                                           [  OK  ]

 

告警日志：

230820 15:10:25 mysqld_safe Starting mysqld daemon with databases from /var/mysql/data

230820 15:10:25 InnoDB: The InnoDB memory heap is disabled

230820 15:10:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins

230820 15:10:25 InnoDB: Compressed tables use zlib 1.2.3

230820 15:10:25 InnoDB: Initializing buffer pool, size = 1.0G

230820 15:10:26 InnoDB: Completed initialization of buffer pool

230820 15:10:26 InnoDB: highest supported file format is Barracuda.

230820 15:10:26  InnoDB: Waiting for the background threads to start

230820 15:10:27 InnoDB: 1.1.8 started; log sequence number 1604156

230820 15:10:27 [Note] Event Scheduler: Loaded 0 events

230820 15:10:27 [Note] /usr/local/webserver/mysql/bin/mysqld: ready for connections.

Version: '5.5.18-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

 

说明：如上所示，修改完mysql-bin.index后，mysql成功被开启.

 

3、数据验证

root@MySQL5518-Master [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| booksDB            |

| fruitsDB           |

| mysql              |

| performance_schema |

| test               |

+--------------------+

6 rows in set (0.00 sec)

 

root@MySQL5518-Master [(none)]> use booksDB;

Database changed

root@MySQL5518-Master [booksDB]> show tables;

+-------------------+

| Tables_in_booksDB |

+-------------------+

| authorbook        |

| authors           |

| books             |

+-------------------+

3 rows in set (0.00 sec)

 

root@MySQL5518-Master [booksDB]> select * from authorbook;

+---------+-------+

| auth_id | bk_id |

+---------+-------+

|    1001 | 11033 |

|    1002 | 11035 |

|    1003 | 11072 |

|    1004 | 11028 |

|    1011 | 11078 |

|    1012 | 11026 |

|    1012 | 11041 |

|    1014 | 11069 |

+---------+-------+

8 rows in set (0.00 sec)

 

说明：最新update的数据在启动数据库后依然存在.

 

参考网址：

https://blog.csdn.net/weixin_30200131/article/details/113328482

https://www.yzktw.com.cn/post/861139.html