【MySQL】记录解决Innodb文件损坏问题

矫情吗；* 2022-10-30 15:22 218阅读 0赞

#### 问题 ####

2020-06-10 06:29:15 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_daily" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:42:30 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:42:30 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_daily" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:42:40 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:42:40 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_daily" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:42:51 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:42:51 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_daily" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:43:31 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:43:31 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_member" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:43:41 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:43:41 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_member" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:44:12 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:44:12 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_milestone" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:44:52 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:44:52 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_notificate" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:45:02 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:45:02 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_notificate" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:45:27 7f16b2294700 InnoDB: 
    Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:45:27 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_notificate_users" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:45:37 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:45:37 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_notificate_users" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:45:57 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:45:57 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_project" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:46:31 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:46:31 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."pm_sonar" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:46:41 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:46:41 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."pm_sonar" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:47:03 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:47:03 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_data_dict" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:47:13 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:47:13 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_data_dict" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:47:35 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:47:35 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_permission_config" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:47:45 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:47:45 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_permission_config" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:48:12 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:48:12 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_role" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:48:22 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:48:22 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_role" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:48:45 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:48:45 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_role_permission" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:48:55 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:48:55 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_role_permission" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:49:14 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:49:14 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_user" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:49:24 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:49:24 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_user" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:49:45 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:49:45 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_user_level" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:49:55 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:49:55 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_user_level" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 06:50:13 7f16b2294700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:50:13 7f16b2294700 InnoDB: Error: Fetch of persistent statistics requested for table "pm"."sys_user_role" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2020-06-10 06:50:23 7f16a9ffb700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2020-06-10 06:50:23 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_user_role" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
    2020-06-10 07:11:55 1 [Note] mysqld: Normal shutdown

由上面CLI输出可以看出，MySQL在启动的时候报了一个“表”错误。我们择取其中一段开看看：

2020-06-10 06:49:55 7f16a9ffb700 InnoDB: Recalculation of persistent statistics requested for table "pm"."sys_user_level" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

上面语句已经说得很明白了，MySQL系统在启动时需要做持久性统计，但是该统计的存储信息已经损坏，所以只能采用瞬时统计模式。emmmmm~，这里只需要了解到文件块已经被“损坏”就够了，至于Innodb的块损坏一般来说我们都会尝试先使用Recover模式恢复，实在不行再说。

--------------------

#### 解决 ####

这个时候先将当前的应用停止，之后在`mysql.cnf`中加入`innodb_force_recovery = 1`配置，然后重新启动服务。

幸运的是，通过使用Recover模式后MySQL居然能够正常启动了。但是当我们重新将`innodb_force_recovery`关闭后，系统就恢复原样又启动不了了。简单来说MySQL的Recover模式就好像Windows安全模式一样只是一种过渡的状态。

因此，我们需要先使用Recover模式进入MySQL后将受影响的数据库中所有内容以SQL方式导出（这里之所以不用Dump导出是因为提示上面说到的，是持久性统计遭到破坏或者丢失了。而Dump导出会全环境内容导出的，所以这里建议采用SQL方式导出）。

导出完毕后将当前`schema`删掉并关闭MySQL服务并将`innodb_force_recovery`注释掉。之后按照正常的方式启动并通过脚本重建库和表并将数据导入就可以了。