MySQL InnoDB 表不存在问题修复

MySQL 可能会由于各种原因表空间损坏，导致 show tables 可以看到表，但 show create table 或 select 查询等操作时，提示表不存在

问题

MySQL InnoDB 引擎的表 show tables 可以看到表存在，但使用时提示表不存在，为了避免数据丢失，需要进行数据恢复。如果数据可以丢失或再次写入，可直接 drop 之后，手动删除表物理文件，并重新创建表写入数据。
这里只针对需要保留数据的情况进行恢复

前提条件

• 数据文件ibd文件存在
• 存在表结构备份，可以找到一模一样的create table语句
• 这里以系统表 mysql.gtid_executed 表为例

恢复步骤

为了避免操作失误导致数据丢失，可先将对应数据文件多复制一份到临时路径
本身可以直接在业务库进行恢复，但这里为了避免恢复重要数据，导致其他不可控异常，所以新建临时库进行恢复

-- 在问题数据库创建一个临时测试库，修复完成可进行删除
create database gtid_recover;

use gtid_recover
-- 创建对应异常的表，所以此操作需要存在表结构备份
CREATE TABLE `gtid_executed` (
  `source_uuid` char(36) NOT NULL COMMENT 'uuid of the source where the transaction was originally executed.',
  `interval_start` bigint(20) NOT NULL COMMENT 'First number of interval.',
  `interval_end` bigint(20) NOT NULL COMMENT 'Last number of interval.',
  PRIMARY KEY (`source_uuid`,`interval_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 数据库临时表上操作
-- 丢弃该表的表空间，这步会删除 ibd 文件
ALTER TABLE gtid_executed DISCARD TABLESPACE;
-- 在机器上进行操作，将备份 ibd 文件放入表中
cp /backup_dir/gtid_executed.ibd /mysqldatadir/mysql/
chown -R mysql.mysql /mysqldatadir
-- 导入表空间
alter table gtid_executed IMPORT TABLESPACE;
-- 此时在测试库的临时表已经恢复，show create table 和 select 应该都可以正常执行

-- 删除故障表，rename 到业务库中，恢复业务，这里使用的是 MySQL 系统库示例，所以 rename 到了 mysql 库
use gtid_recover
rename table gtid_executed to mysql.gtid_executed_bak;
use mysql
-- 此时drop会报错，但相关数据仍可以被删除，下步 rename 可以执行成功
drop table gtid_executed;
rename table gtid_executed_bak to gtid_executed;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

改表期间数据库崩溃导致异常

如果使用临时表的方式改表，改表过程中数据库异常崩溃，此时需要先找到删除该表中间的临时表，再进行上述操作。

如果数据库存在主从架构，需执行完成后，根据主从异常报错信息处理对应主从问题即可

1. 查找临时表名称 SQL
   SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
2. 在物理机上，将临时表空间文件改为查询后的表名
   mv \#sql-物理机显示名称.frm \#sql-上步查询结果.frm
3. 删除这个孤立的临时表
   drop table 第一步查询结果的临时表名
4. 按照前面恢复步骤再重构表，恢复数据库异常