MySQL :: MySQL 8.4 参考手册 :: 17.20.4 InnoDB 数据字典操作故障排除

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 17 InnoDB 存储引擎 / 17.20 InnoDB 故障排除 / 17.20.4 InnoDB 数据字典操作故障排除

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

17.20.4 解决 InnoDB 数据字典操作问题17.20.4 Troubleshooting InnoDB Data Dictionary Operations

关于表定义的信息存储在 InnoDB 数据字典中。如果你移动数据文件，字典数据可能会变得不一致。Information about table definitions is stored in the InnoDB data dictionary. If you move data files around, dictionary data can become inconsistent.

如果数据字典的损坏或一致性问题阻止你启动 InnoDB，请参阅第 17.20.3 节，“强制 InnoDB 恢复” 了解手动恢复的信息。If a data dictionary corruption or consistency issue prevents you from starting InnoDB, see Section 17.20.3, “Forcing InnoDB Recovery” for information about manual recovery.

无法打开数据文件Cannot Open Datafile

启用 innodb_file_per_table（默认设置）时，如果缺少每表单独文件表空间文件（.ibd 文件），启动时可能会出现以下消息：With innodb_file_per_table enabled (the default), the following messages may appear at startup if a file-per-table tablespace file (.ibd file) is missing:

[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.

要处理这些消息，请执行 DROP TABLE 语句，从数据字典中删除关于缺失表的数据。To address these messages, issue DROP TABLE statement to remove data about the missing table from the data dictionary.

恢复孤立的每表单独文件 ibd 文件Restoring Orphan File-Per-Table ibd Files

此过程描述了如何将孤立的每表文件 .ibd 文件恢复到另一个 MySQL 实例。如果系统表空间丢失或无法恢复，并且您想在新的 MySQL 实例上恢复 .ibd 文件备份，则可以使用此过程。This procedure describes how to restore orphan file-per-table .ibd files to another MySQL instance. You might use this procedure if the system tablespace is lost or unrecoverable and you want to restore .ibd file backups on a new MySQL instance.

该过程不支持通用表空间 .ibd 文件。The procedure is not supported for general tablespace .ibd files.

该过程假设您只有 .ibd 文件备份，您正在恢复到最初创建孤立 .ibd 文件的相同版本的 MySQL，并且 .ibd 文件备份是干净的。有关创建干净备份的信息，请参见第 17.6.1.4 节，“移动或复制 InnoDB 表”。The procedure assumes that you only have .ibd file backups, you are recovering to the same version of MySQL that initially created the orphan .ibd files, and that .ibd file backups are clean. See Section 17.6.1.4, “Moving or Copying InnoDB Tables” for information about creating clean backups.

在第 17.6.1.3 节，“导入 InnoDB 表” 中概述的表导入限制适用于此过程。Table import limitations outlined in Section 17.6.1.3, “Importing InnoDB Tables” are applicable to this procedure.

在新的 MySQL 实例上，在同名数据库中重新创建表。On the new MySQL instance, recreate the table in a database of the same name.

mysql> CREATE DATABASE sakila;

mysql> USE sakila;

mysql> CREATE TABLE actor (
    ->     actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     first_name VARCHAR(45) NOT NULL,
    ->     last_name VARCHAR(45) NOT NULL,
    ->     last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     PRIMARY KEY  (actor_id),
    ->     KEY idx_actor_last_name (last_name)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

放弃新创建表的表空间。Discard the tablespace of the newly created table.
```
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
```
将孤立的 .ibd 文件从备份目录复制到新的数据库目录中。Copy the orphan .ibd file from your backup directory to the new database directory.
```
$> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
```
确保 .ibd 文件具有必要的文件权限。Ensure that the .ibd file has the necessary file permissions.

导入孤立的 .ibd 文件。系统会发出警告，指出 InnoDB 正在尝试导入该文件而不进行模式验证。Import the orphan .ibd file. A warning is issued indicating that InnoDB is attempting to import the file without schema verification.

mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.15 sec)

Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
Error opening './sakila/actor.cfg', will attempt to import
without schema verification

查询表以验证 .ibd 文件是否已成功恢复。Query the table to verify that the .ibd file was successfully restored.
```
mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
```

首页上一级下一节