重启机器导致mysql启动失败

2022-11-22 21:23:46

一、问题

断电重启机器后，mariadb服务启动失败。使用mysql -uroot -p 连接时报错
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

二、常见解决方案

网上的解决方法大致为 ①空间不足（df -h 查看） ②权限不足（/var/lib文件夹）chown -R mysql:mysql /vdb1/lib/ ③修改my.cnf ,重新配置socket路径 ④使用mysql -h127.0.0.1 -uroot -p连接 ⑤干掉僵尸进程 ps -ef | grep mysql

三、本次解决方案

1、以上方法对于本人当前环境均没有用，使用④方法，报错为

ERROR 2003 (HY000): Can't connect to MySQL server on "host" (111)

2、经大神指导，在mariadb日志（/var/log/mariadb/mariadb.log）中看到 mysqld got signal 6

Version: ‘5.5.64-MariaDB’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MariaDB Server
200617 22:54:24 InnoDB: Assertion failure in thread 140689172858624 in file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
200617 22:54:24 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

3、根据错误，网上查询，得到可通过设置恢复模式启动mysql

[设置恢复模式启动mysql官网](https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html)

编辑配置文件
vim /etc/my.cnf
添加配置项：
innodb_force_recovery = 1
其中后面的值设置为1、如果1不想再逐步增加为2/3/4/5/6等。直到能启动mysql为止，若不想尝试直接写6即可；
对于含有业务的服务器，最好是慢慢上调，以免永久性损坏数据。值得意义在官网有说明，摘录如下：

Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Before doing so, ensure that you have a backup copy of your database in case you need to recreate it. Values of 4 or greater can permanently corrupt data files. Only use an innodb_force_recovery setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and only increase the value incrementally, as necessary.

设置完成即重启mariadb服务：
systemctl restart mariadb
若重启失败，或启动时一直打印：200618 17:45:56 InnoDB: Waiting for the background threads to start
则在my.cnf中的[mysqld]中增加：innodb_purge_thread=0再尝试重启。
启动成功后测试数据库连接：mysql -uroot -p;

4、备份数据库

备份数据库方法在另一篇文章:mysql备份与恢复

5、备份数据文件与重启

关闭服务
systemctl stop mariadb
备份文件
mv /var/lib/mysql /root/mysql_back

正常模式在启动mysql：
vim /etc/my.cnf
注释配置项：

#innodb_force_recovery = 1
#innodb_purge_thread=0

再重启：
systemctl restart mariadb

6、数据恢复

mysql备份与恢复

至此大功告成！

四、经验与教训

出问题，可以先根据表面的错误去寻找答案，但是在试了各种方法后还不能解决，就需要换个思路，去仔细阅读日志
如果其值内容较复杂，则优先看error日志

解决问题的过程中，也要稍加总结分类已查到的解决方案，以免重复试错。

这篇文章仅仅简单记述一下解决问题的经过，同样的错误，可能有各种各样的理由。欢迎大家留言该错误的更多解决方案，以便于后面的人更快解决问题！另外亦欢迎大家可以关注我的微信公众号，方便利用零碎时间互相交流。共勉！

路漫漫其修远兮，吾将上下而求索。。。

码农公寓