mysql启动异常 The server quit without updating PID file InnoDB: Failed解决

背景

服务器硬盘满异常重启后无法启动，启动mysql是提示，zabbix数据库因为不是重要环境没备份，但重建又比较麻烦，尝试去恢复。

Starting MySQL............ ERROR! The server quit without updating PID file (/data/mysql/localhost.localdomain.pid).

查看相关目录下的*. err日志

2021-09-07 11:41:25 7f3a22906740 InnoDB: Error: page 1661 log sequence number 969064480886
2021-09-07 11:41:25 7f3a22906740 InnoDB: Error: page 1608 log sequence number 969065652981
2021-09-07 11:41:25 7f3a22906740 InnoDB: Error: page 48 log sequence number 969064490362
2021-09-07 11:41:26 7f3a22906740 InnoDB: Error: page 398 log sequence number 969065057348
2021-09-07 11:41:26 7f3a22906740 InnoDB: Error: page 1600 log sequence number 969064490362
2021-09-07 11:41:26 7f3a22906740 InnoDB: Error: page 49 log sequence number 969065360982
2021-09-07 11:41:26 7f3a22906740 InnoDB: Error: page 659 log 

大量的InnoDB报错

解决办法

1. vi /etc/my.cnf 最后添加

innodb_force_recovery = 6     ##异常启动6是最强模式（1-6模式我只要6能正常启动）

正常启动mysql 尝试mysqldump和Navicatcat导出备份都是报错，
再去查看error日志

[root@localhost mysql]# tail -f localhost.localdomain.err
2021-10-26 11:43:27 5733 [ERROR] InnoDB: Failed to find tablespace for table '"zabbix"."graph_discovery"' in the cache. Attempting to load the tablespace with space id 326.
2021-10-26 11:43:27 5733 [ERROR] InnoDB: Failed to find tablespace for table '"zabbix"."graph_theme"' in the cache. Attempting to load the tablespace with space id 575.
2021-10-26 11:43:27 5733 [ERROR] InnoDB: Failed to find tablespace for table '"zabbix"."graphs"' in the cache. Attempting to load the tablespace with space id 174.
2021-10-26 11:43:27 5733 [ERROR] InnoDB: Failed to find tablespace for table '"zabbix"."graphs_items"' in the cache. Attempting to load the tablespace with space id 176.
2021-10-26 11:43:28 5733 [ERROR] InnoDB: Failed to find tablespace for table '"zabbix"."group_discovery"' in the cache. Attempting to load the tablespace with space id 121.

2.尝试检查表：

mysqlcheck -c --databases  zabbix -uroot -p 

检查全部表是否正常，但可能损坏严重，无法正常全部check，
思路：导出全部表然后拼接成shell去执行，查看那些表执行中无法正常进行

3.导出全部表名：select table_name from information_schema.tables where table_schema=‘zabbix’ and table_type=‘base table’;

然后进行拼接，类似

##!/bin/sh
 mysqlcheck -uroot -p**** zabbix scripts
 mysqlcheck -uroot -p**** zabbix service_alarms
 mysqlcheck -uroot -p**** zabbix services
 mysqlcheck -uroot -p**** zabbix services_links
 mysqlcheck -uroot -p**** zabbix services_times
....
....

执行到报错，注释掉，再执行，找出全部异常表三张
history history_uint history_str_1

4.尝试修复报错（时报），：

[root@localhost home]# mysqlcheck -uroot -p**** -r zabbix history_str
Warning: Using a password on the command line interface can be insecure.
zabbix.history_str
note     : The storage engine for the table doesn't support repair

需要修改表引擎：异常模式无法修改MyISAM
5.尝试 Percona Data Recovery Tool 修复(失败)：
安装步骤不写了，无法修复报错

./innochecksum -f /data/mysql/ibdata1 
page 290 invalid (fails log sequence number check)
page 290: log sequence number: first = 0xA0BC753E; second = 0xA065D067

6.现在思路只能备份数据库，删除重建

导出整个数据库，排除异常表：
/usr/local/mysql/bin/mysqldump -uroot -p***** zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --ignore-table=zabbix.history_str > backup_zabbix.sql

用navicat去导出异常表结构，用复制等方法尽量导出有些表数据；

然后进行进入数据目录/data/mysql
移动备份 （此方式只使用只有这个数据库，其他有数据库的话也要dump出来move掉）
mv ib_logfile0 ib_logfile1 ib_logfile101 ibdata1 /home/mysql_bak/
mv zabbix /home/mysql_bak/

vi /etc/my.cnf

取消注释#innodb_force_recovery = 6 启动mysql

还原数据库，还原有些表结构和导出数据
mysql -uroot -p****** -Dzabbix < backup_zabbix.sql
用navicat去恢复异常表的一些结构和数据，这边日志类的不是非常重要没去核对