Mysql数据库故障恢复

數據庫無法啟動故障處理

2021-06-30 09:17:19 0x7f8511215700  InnoDB: Assertion failure in thread 140209494775552 in file fut0lst.ic line 93
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA      

開啟forcing-innodb-recovery模式,從1-6逐增

my.cnf添加參數

innodb_force_recovery = 1      

啟動mysql

systemctl start mysqld      

備份數據庫

mysqldump -uroot -p --all-databases --default-character-set=utf8 > all.sql
cd /var/lib
tar -zcvf mysql.bak.tar.gz mysql      

注釋innodb_force_recovery參數,關閉mysqld服務

systemctl stop mysqld      

刪除原數據

rm -rf /var/lib/mysql/*      

重新初始化數據庫

mysqld --initialize --user=mysql      

檢查日誌,獲取mysql數據庫初始化密碼

啟動mysql服務

systemctl start mysqld      

修改密碼

set password = password('P@ssw0rd');      

導入備份

mysql -uroot -p < all.sql