mysql-强制InnoDB恢复

只有在紧急情况下才将innodb_force_recovery设置为大于0的值，这样才能启动和转储表。在此之前，确保有数据库的备份副本，以备需要重新创建时使用。值为4或更大可能永久损坏数据文件。只有在数据库的单独物理副本上成功测试了innodb_force_recovery设置后，才能在生产服务器实例上使用4或更大的innodb_force_recovery设置。当强制恢复时，应该始终以innodb_force_recovery=1开始，并且只在必要时增量地增加值。InnoDBInnoDB

[mysqld]
innodb_force_recovery = 1
1
2

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20