MySQL断电崩溃数据恢复方法

发表于 | 更新于 | 评论数： | 阅读次数：

服务器使用wamp提供web服务，因断电导致mysql无法启动。下面记录恢复mysql的方法流程。

服务器配置

  • wampserver 3.2.0
  • apache 2.4.41
  • php 7.3.12
  • mysql 8.0.18

mysql数据库恢复过程

故障表现

mysql后台服务无法启动。查找mysql.log日志文件发现innodb数据库引擎报如下错误，说明mysql表空间损坏，线程在数据页中读取不到需要的 page 和数据，因此判断mysql的innodb奔溃了。

1
[ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=86, page number=4]. You may have to recover from a backup.

修复方法

启动mysql后台服务

首先使用mysql的Forcing InnoDB Recovery特性，启动mysql后台服务。打开my.ini配置文件，设置如下：

1
innodb_force_recovery=1

再重启mysql后台服务成功。

innodb_force_recovery 可以设置为 1-6，大的值包含前面所有小于它的值的影响。

1
2
3
4
5
6
7
8
9
10
11
1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可，然后 dump 出库表进行重建。

2 (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。

3 (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。

4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。

6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。

注意：

1
2
3
4
5
6
7
8
9
为了安全，当设置参数值大于 0 后，可以对表进行 select, create, drop 操作,但 insert, update 或者 delete 这类操作是不允许的。MySQL 5.6.15 以后，当 innodb_force_recovery 的值大于等于 4 的时候，InnoDB 表处于只读模式。

在值小于等于 3 时可以通过 select 来 dump 表，可以 drop 或者 create 表。MySQL 5.6.27 后大于 3 的值也支持 DROP TABLE；

如果事先知道哪个表导致了崩溃则可 drop 掉这个表。如果碰到了由失败的大规模导入或大量 ALTER TABLE 操作引起的 runaway rollback，则可 kill 掉 mysqld 线程然后设置 innodb_force_recovery = 3 使数据库重启后不进行 rollback。然后删除导致 runaway rollback 的表；

如果表内的数据损坏导致不能 dump 整个表内容。那么附带 order by primary_key desc 从句的查询或许能够 dump 出损坏部分之后的部分数据；

若使用更高的 innodb_force_recovery 值，那么一些损坏的数据结构可能引起复杂的查询无法运行。此时可能只能运行最基本的 select * from table 语句。

修复备份mysql数据库

使用如下命名自动修复数据库：

1
mysqlcheck -u root -p --all-databases --auto-repair

修复完成后，使用如下命令备份重要数据库的数据：

1
2
3
4
5
6
7
8
9
10
# 备份全部数据库（包含存储过程、自定义函数及事件） 
mysqldump -uroot -p --single-transaction -R -E --all-databases > /tmp/all_database.sql 

# 备份指定数据库 
mysqldump -uroot -p --single-transaction -R -E --databases db1 > /tmp/db1.sql 
mysqldump -uroot -p --single-transaction -R -E --databases db1 db2 > /tmp/db1_db2.sql 

# 备份部分表 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 > /tmp/tb1.sql 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sql

注意：一定要先修复再备份，防止备份数据时因页表错误丢失数据。

完成修复备份后，注释my.ini中innodb_force_recovery，再重启mysql数据库后台服务，如果成功，则修复完成。如果未成功，则准备利用备份数据重建数据库。

wamp备份修复

当mysql遭遇严重错误，修复备份后仍然不能启动，可考虑重装wamp。具体操作如下：

  • 备份整个wamp文件夹，以便保留mysql数据库文件、apache、php关键配置文件等。
  • 重装wamp后，重建mysql数据库，重设apache、php关键配置，参考之前备份的wamp文件夹
  • apache主要配置httpd.conf和httpd-vhost.conf，设置documentroot和vhost
  • php主要配置php.ini文件
1
2
3
4
5
# 针对mysql 8.0.18，使用如下命令修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'

# 通过增加参数 --default-character-set = utf8 解决乱码问题
mysql -u root -p < path_to_import_file --default-character-set = utf8

重装后配置遇到的问题请参考：

参考链接

  1. mysql InnoDB crash and recover,by Fisher.
  2. 利用 Forcing InnoDB Recovery 特性解决 MySQL 重启失败的问题,by GlonHo.
  3. INFORMATION_SCHEMA获取元数据,by 十五十六.
  4. MySQLDump的备份小技巧,by MySQL技术.
  5. MYSQL 自带4个默认数据库,by bolang100.
  6. wamp设置mysql8.0.18初始密码问题,by PHPer.
  7. MySQL字符集和排序规则,by 流逝的傷.
  8. 关于Apache本地能访问外网不能访问的问题,by programminghunter.
  9. php中的时区设置和php.ini中配置时区的方法,by quyu.
  10. 设置修改 Apache 文件根目录 (Document Root),by needrunning.
  11. windows 下自动备份mysql数据库，删除以前的数据库文件,by 森林屿麓.
  12. windows下mysql自动备份脚本,by 雪竹子.