只有在紧急情况下才将innodb_force_recovery设置为大于0的值,这样才能启动和转储表。在此之前,确保有数据库的备份副本,以备需要重新创建时使用。值为4或更大可能永久损坏数据文件。只有在数据库的单独物理副本上成功测试了innodb_force_recovery设置后,才能在生产服务器实例上使用4或更大的innodb_force_recovery设置。当强制恢复时,应该始终以innodb_force_recovery=1开始,并且只在必要时增量地增加值。InnoDBInnoDB
[mysqld]
innodb_force_recovery = 1
2. 无法打开数据文件
[ERROR] InnoDB: Operating system error number 2 in a file operation.
[ERROR] InnoDB: The error means the system cannot find the path specified.
[ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71
[Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.
src:
flush tables table_name for export;
cd $datadir/dbname
cp tbname.* /data/bak
dst:
create database database;
use database;
create table xxx
cp /data/bak $datadir/dbname(PS:注意权限)
ALTER TABLE database.table_name DISCARD TABLESPACE;
SHOW WARNINGS;
https://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html