mysql无法启动-You may have to recover from a backup

错误日志报以下错误（片断）：

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

2020-06-19 17:22:00 7f11c48e1720 InnoDB: Page dump in ascii and hex (16384 bytes):

 len 16384; hex 7478d078000000050000000000000000000000000f001f4d000700000000000000000000000000000000001b4000000000000000000200f20000000000000006000000000000002d000000000000002e000000000000002f0000000000000030000000000...

InnoDB: End of page dump

2020-06-19 17:22:00 7f11c48e1720 InnoDB: uncompressed page, stored checksum in field1 1954074744, calculated checksums for field1: crc32 993334256, innodb 2046145943, none 3735928559, stored checksum in field2 1139795846, calculated checksums for field2: crc32 993334256, innodb 1606613742, none 3735928559, page LSN 0 254222157, low 4 bytes of LSN at page end 254221236, page number (if stored to page already) 5, space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be a transaction system page

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has corrupted its own file cache

InnoDB: and rebooting your computer removes the

InnoDB: error.

InnoDB: If the corrupt page is an index page

InnoDB: you can also try to fix the corruption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the corrupt table. You can use CHECK

InnoDB: TABLE to scan your table for corruption.

InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

InnoDB: Ending processing because of a corrupt database page.

 

* 这是由于系统崩溃或重启导致MYSQL恢复文件损坏而无法启动

 

解决办法：

 

1、编辑my.cnf（vim /etc/my.cnf），添加如下参数：

[mysqld]
innodb_force_recovery = 1

 

2、再次启动mysql（此时可以启动成功，启动SHELL脚本示例如下：）

service mysql start

 

3、使用mysqldump备份数据库（示例SHELL脚本如下：）

mysqldump -uroot -proot dbName>db.sql

所有非系统数据库都得备份

 

4、删除备份的数据库（所有非系统数据库，示例SQL脚本如下：）

drop database dbName

 

5、关闭（注释）第一步的选项（参数），编辑my.cnf（vim /etc/my.cnf）

 

6、重新启动数据库（此时启动成功，代表恢复正常了）

 

7、恢复备份的数据库（示例脚本如下）

create database dbName;
use cdbName;
source /opt/db.sql;

 

* 至此已完成数据库的修复。总结一下，其实很简单：打开参数 > 启动数据库 > 备份数据 > 关闭参数 > 重启数据库 > 恢复数据