mysql 数据库 Database page corruption 时的恢复参数 innodb_force_recovery、innodb_purge_threads

今天药忘吃喽~ 2023-03-03 05:59 4阅读 0赞

# innodb\_force\_recovery #

mysql 启动报错

InnoDB: Page may be an index page where index id is 8005
    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 84692.
    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.

page 已经存在损坏，需要修改innodb\_force\_recovery=1(通常设置为1)，使mysqld跳过恢复步骤，启动后马上逻辑导出数据，重新初始化后再导入。

innodb\_force\_recovery可以设置为1-6,大的数字包含前面所有数字的影响。

1.  (SRV\_FORCE\_IGNORE\_CORRUPT):忽略检查到的corrupt页。
2.  (SRV\_FORCE\_NO\_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3.  (SRV\_FORCE\_NO\_TRX\_UNDO):不执行事务回滚操作。
4.  (SRV\_FORCE\_NO\_IBUF\_MERGE):不执行插入缓冲的合并操作。
5.  (SRV\_FORCE\_NO\_UNDO\_LOG\_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6.  (SRV\_FORCE\_NO\_LOG\_REDO):不执行前滚的操作。

注意

当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

# innodb\_purge\_threads #

设置 innodb\_force\_recovery 为非0后，需要设置下 innodb\_purge\_thread=0

innodb_force_recovery=1
    innodb_purge_thread=0

参考  
https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html  
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html\#sysvar\_innodb\_force\_recovery  
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html\#sysvar\_innodb\_purge\_threads