14.22.3 对 InnoDB 数据字典操作进行故障排除

有关 table 定义的信息既存储在.frm文件中，又存储在 InnoDB data dictionary中。如果四处移动.frm个文件，或者服务器在数据字典操作过程中崩溃，则这些信息源可能会变得不一致。

如果数据字典损坏或一致性问题使您无法启动InnoDB，请参阅第 14.22.2 节“强制 InnoDB 恢复”以获取有关手动恢复的信息。

CREATE TABLE 由于孤立 table 而失败

数据字典不同步的症状是CREATE TABLE语句失败。如果发生这种情况，请查看服务器的错误日志。如果日志 table 明该 table 已经存在于InnoDB内部数据字典中，则说明InnoDBtable 空间文件中有一个孤立 table，该 table 没有相应的.frm文件。错误消息如下所示：

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

您可以按照错误消息中给出的说明删除孤立 table。如果仍然无法成功使用DROP TABLE，则问题可能是由于mysqlClient 端中的名称完成。要变通解决此问题，请使用--skip-auto-rehash选项启动mysqlClient 端，然后再次尝试DROP TABLE。 (启用名称补全后，mysql尝试构造 table 名称列 table，如果存在上述问题，该列 table 将失败。)

无法打开数据文件

启用innodb_file_per_table(默认)后，如果缺少file-per-tabletable 空间文件(.ibd文件)，则可能在启动时显示以下消息：

[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语句以从数据字典中删除有关丢失的 table 的数据。

无法打开文件错误

数据字典不同步的另一个症状是 MySQL 显示错误，无法打开InnoDB文件：

ERROR 1016: Can't open file: 'child2.ibd'. (errno: 1)

在错误日志中，您可以找到以下消息：

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

这意味着存在一个孤立的.frm文件，而InnoDB内没有相应的 table。您可以通过手动删除.frm孤立文件来删除它。

孤立中级 table 格

如果 MySQL 在原位ALTER TABLE操作(ALGORITHM=INPLACE)中间退出，则可能会留下一个孤立的中间 table，该 table 占用了系统空间。此外，否则为空的general tablespace中的孤立中间 table 可防止您删除常规 table 空间。本节介绍如何识别和删除孤立的中间 table。

中间 table 名称以#sql-ib前缀(例如#sql-ib87-856498050)开头。随附的.frm文件具有#sql-*前缀，并具有不同的名称(例如#sql-36ab_2.frm)。

要标识系统上的孤立中间 table，您可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的 table 名。如果原始 table 位于file-per-tabletable 空间中，那么孤立的中间 table 的 table 空间文件(#sql-*.ibd文件)应该在数据库目录中可见。

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

要删除孤立的中间 table，请执行以下步骤：

  • 在数据库目录中，重命名#sql-*.frm文件以匹配孤立中间 table 的基本名称：
shell> mv #sql-36ab_2.frm #sql-ib87-856498050.frm

Note

如果没有.frm文件，则可以重新创建它。 .frm文件必须具有与孤立中间 table 相同的 table 架构(它必须具有相同的列和索引)，并且必须放置在孤立中间 table 的数据库目录中。

  • 通过发出DROP TABLE语句，在 table 名前面加上#mysql50#并将 table 名括在反引号中来删除孤立的中间 table。例如：
mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

#mysql50#前缀告诉 MySQL 忽略 MySQL 5.1 中引入的file name safe encoding。要对具有特殊字符(例如“#”)的 table 名执行 SQL 语句，需要将 table 名括在反引号中。

Note

如果在将 table 移动到其他 table 空间的就地ALTER TABLE操作期间发生崩溃，则恢复过程会将 table 还原到其原始位置，但将孤立的中间 table 保留在目标 table 空间中。

Note

如果 MySQL 在分区 table 上执行ALTER TABLE操作的中间退出，则可能会留下多个孤立的中间 table，每个分区一个。在这种情况下，请使用以下过程删除孤立的中间 table：

  • 在相同 MySQL 版本的单独实例中，创建一个具有与分区 table 相同的架构名称和列的未分区 table。

  • 将非分区 table 的.frm文件复制到带有孤立中间 table 的数据库目录中。

  • 为每个 table 制作一个.frm文件的副本，并重命名.frm文件以匹配孤立中间 table 的名称(如上所述)。

  • 对每个 table 执行DROP TABLE操作(如上所述)。

孤立的临时 table 格

如果 MySQL 在执行 table 复制ALTER TABLE操作(ALGORITHM=COPY)的中间退出，则可能会留下一个孤立的临时 table，该 table 会占用系统空间。另外，否则为空的general tablespace中的孤立临时 table 可防止您删除常规 table 空间。本节介绍如何识别和删除孤立的临时 table。

孤立的临时 table 名称以#sql-前缀(例如#sql-540_3)开头。随附的.frm文件具有与孤立临时 table 相同的基本名称。

Note

如果没有.frm文件，则可以重新创建它。 .frm文件必须具有与孤立临时 table 相同的 table 架构(必须具有相同的列和索引)，并且必须放置在孤立临时 table 的数据库目录中。

要标识系统上的孤立临时 table，您可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的 table 名。如果原始 table 位于file-per-tabletable 空间中，则孤立临时 table 的 table 空间文件(#sql-*.ibd文件)应该在数据库目录中可见。

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

要删除孤立的临时 table，请通过发出DROP TABLE语句来删除该 table，并在 table 名前加上#mysql50#并将 table 名括在反引号中。例如：

mysql> DROP TABLE `#mysql50##sql-540_3`;

#mysql50#前缀告诉 MySQL 忽略 MySQL 5.1 中引入的file name safe encoding。要对具有特殊字符(例如“#”)的 table 名执行 SQL 语句，需要将 table 名括在反引号中。

Note

如果 MySQL 在分区 table 的 table 复制ALTER TABLE操作中间退出，则可能会留下多个孤立的临时 table，每个分区一个。在这种情况下，请使用以下过程删除孤立的临时 table：

  • 在相同 MySQL 版本的单独实例中，创建一个具有与分区 table 相同的架构名称和列的未分区 table。

  • 将非分区 table 的.frm文件复制到带有孤立临时 table 的数据库目录中。

  • 为每个 table 制作.frm文件的副本，并重命名.frm文件以匹配孤立临时 table 的名称(如上所述)。

  • 对每个 table 执行DROP TABLE操作(如上所述)。

table 空间不存在

启用innodb_file_per_table时，如果缺少.frm或.ibd文件(或两者)，则可能会出现以下消息：

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

如果发生这种情况，请尝试以下过程解决问题：

  • 在其他一些数据库目录中创建匹配的.frm文件，并将其复制到孤立 table 所在的数据库目录中。

  • 原始 table 的发布DROP TABLE。那应该成功删除该 table，并且InnoDB应该在错误日志中显示一条警告，指出.ibd文件丢失。

恢复每 table 孤儿文件 ibd 文件

此过程描述了如何将孤立的file-per-table .ibd文件还原到另一个 MySQL 实例。如果系统 table 空间丢失或不可恢复，并且您想在新的 MySQL 实例上还原.ibd文件备份，则可以使用此过程。

general tablespace .ibd文件不支持该过程。

该过程假定您只有.ibd个文件备份，并且要恢复到最初创建孤立.ibd文件的 MySQL 版本，并且.ibd文件备份是干净的。有关创建干净备份的信息，请参见第 14.6.1.4 节“移动或复制 InnoDBtable”。

第 14.6.1.3 节“导入 InnoDBtable”中概述的 table 导入限制适用于此过程。

  • 在新的 MySQL 实例上，在同名数据库中重新创建 table。
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=utf8;
  • 丢弃新创建的 table 的 table 空间。
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
  • 将孤立的.ibd文件从备份目录复制到新的数据库目录。
shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/

  • 确保.ibd文件具有必要的文件权限。

  • 导入孤立的.ibd文件。发出警告，指示InnoDB将尝试在不进行模式验证的情况下导入文件。

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
  • 查询 table 以验证.ibd文件已成功还原。
mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
Name MySql 中文文档
Version
Language
Badge
Last Updated 2021-07-03T12:24:35