環(huán)境

系統(tǒng):centos7
數(shù)據(jù)庫:mysql5.7.37
數(shù)據(jù)庫目錄: /usr/bin
數(shù)據(jù)目錄: /var/lib/mysql
配置文件位置: /etc/my.cnf?

報(bào)錯(cuò)日志

2024-02-18T08:45:58.039432Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=238] log sequence number 8738273916 is in the future! Current system log sequence number 8738272796.
2024-02-18T08:45:58.039450Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2024-02-18T08:45:58.039504Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=239] log sequence number 8738275019 is in the future! Current system log sequence number 8738272796.
2024-02-18T08:45:58.039510Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2024-02-18T08:45:58.040921Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...

? 打開提示的引用地址?MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recoveryhttp://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html? 查看網(wǎng)頁 可知主要 通過修改配置文件?innodb_force_recovery來 打開恢復(fù)模式

打開恢復(fù)模式

編輯數(shù)據(jù)庫配置文件

在配置文件[mysqld]下加入?yún)?shù) innodb_force_recovery = 1 ，其中后面的值設(shè)置為1、如果1不能恢復(fù)，再逐步增加為2/3/4等。直到能啟動(dòng)mysql為止！??！
注意：最高值為6，但當(dāng)參數(shù)值大于3的時(shí)候。會(huì)對(duì)數(shù)據(jù)文件造成永久性的破壞。

[mysqld]
innodb_force_recovery = 1

啟動(dòng)數(shù)據(jù)庫

systemctl start mysqld.service

此時(shí)進(jìn)入的恢復(fù)模式，數(shù)據(jù)庫是只讀的

數(shù)據(jù)備份

下面需要把數(shù)據(jù)庫備份出來，然后清除之前損壞的數(shù)據(jù)，利用備份數(shù)據(jù)恢復(fù)
執(zhí)行:

/usr/bin/mysqldump -uroot -p123456 --all-databases > /ambuf/mysql/backup240818.sql

執(zhí)行完了 去掉 innodb_force_recovery = 1 參數(shù)

清除損壞的數(shù)據(jù)
清除之前需要把服務(wù)停止：

systemctl stop mysqld.service

備份原data目錄以防萬一：

cp -r /var/lib/mysql/ /ambuf/mysql/data_bak/
rm -rf /var/lib/mysql/*

數(shù)據(jù)庫初始化

上個(gè)步驟已經(jīng)清空了data目錄,所以此時(shí)數(shù)據(jù)庫是啟動(dòng)不了的，需要進(jìn)行初始化
進(jìn)入 安裝目錄下進(jìn)行初始化

/usr/bin/mysql_install_db --user=mysql&

下面就可以利用mysqldump出來的數(shù)據(jù)進(jìn)行恢復(fù)了，但因?yàn)閯倓傔M(jìn)行了初始化，之前的密碼已經(jīng)沒用了，需要在配置文件加入 skip-grant-tables
vi /etc/mysql/my.cnf
?

[mysqld]
skip-grant-tables

啟動(dòng)mysql數(shù)據(jù)庫?

systemctl start mysqld.service

通過 查找日志獲取臨時(shí)密碼

grep "temporary password" /var/log/mysqld.log

執(zhí)行結(jié)果如下

2023-11-08T03:40:10.570444Z 1 [Note] A temporary password is generated for root@localhost: bf*17blep(_R
2024-02-18T09:23:22.815511Z 1 [Note] A temporary password is generated for root@localhost: i45emkw99*Ug

再次登錄數(shù)據(jù)庫

mysql -uroot -pi45emkw99*Ug

重新設(shè)置 登錄用戶密碼權(quán)限。執(zhí)行：

set global validate_password_policy = 0;
SET PASSWORD = PASSWORD("123456");
ALTER USER USER() IDENTIFIED BY "123456";
grant all privileges on *.* to root@'%' identified by "123456";
flush privileges;

打開配置文件my.cnf,?去掉 skip-grant-tables 參數(shù)

?vi /etc/mysql/my.cnf

登錄數(shù)據(jù)庫恢復(fù)數(shù)據(jù)

?再次登錄數(shù)據(jù)庫

mysql -uroot -p123456

?導(dǎo)入之前 備份文件

 source /ambuf/mysql/backup-240218.sql

重啟數(shù)據(jù)庫查看 數(shù)據(jù)恢復(fù)情況。
?文章來源地址http://www.zghlxwxcb.cn/news/detail-835096.html

到了這里，關(guān)于服務(wù)器斷電導(dǎo)致mysql崩潰，使用恢復(fù)模式的解決步驟的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！