InnoDB数据字典操作的故障处理
表定义的信息存储在.frm文件和InnoDB数据字典中。如果您移动了.frm文件,或者服务器在数据字典操作过程中崩溃,这些信息源可能会变得不一致。如果数据字典损坏或一致性问题导致无法启动InnoDB
由于孤儿表导致CREATE TABLE失败
数据字典不同步的一个症状是CREATE TABLE语句失败。如果发生这种情况,请查看服务器的错误日志。如果日志显示这个表已经存在于InnoDB内 部数据字典中,那么在InnoDB表空间文件中就有一个孤儿表,没有相应的.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.
您可以按照错误消息中给出的说明删除孤立表。如果你仍然不能成功使用DROP TABLE,问题可能是由于mysql客户端的名称完成。要解决这个问 题,使用--skip-auto-rehash选项启动mysql客户端并再次尝试DROP TABLE表。(在名称完成的情况下,mysql试图构建一个表名列表,当存在上 述问题时,该列表会失败。)
无法打开文件错误
数据字典不同步的另一个症状是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?
这意味着在InnoDB中存在一个没有相应表的孤儿.frm文件。您可以通过手动删除.frm文件来删除它。
孤立中间表
如果MySQL在执行就地ALTER TABLE操作(ALGORITHM=INPLACE)的过程中退出,可能会留下一个孤立的中间表,占用系统空间。另外,在空的通用 表空间中存在孤立的中间表可以防止删除通用表空间。本节描述如何识别和删除孤立中间表。
中间表名以#sql-ib前缀开始(例如,#sql-ib87-856498050)。附带的.frm文件有一个#sql-*前缀,并且命名不同(例如,#sql-36ab_2.frm)。
要识别系统中的孤儿中间表,可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的表名。如果原始表驻留在file-per-table的 表空间中,对于孤儿中间表的表空间文件(#sql-*.ibd文件)应该在数据库目录中可见。
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
删除孤立中间表的步骤如下:
1.在数据库目录下,重命名#sql-*.frm文件,以匹配孤立中间表的基名:
#mv #sql-36ab_2.frm #sql-ib87-856498050.frm
注意:
如果没有.frm文件,您可以重新创建它。.frm文件必须具有与孤立中间表相同的表模式(它必须具有相同的列和索引),并且必须放置在孤立中间 表的数据库目录中。
2.通过drop table语句删除孤立的中间表,例如在表名前加上#mysql50#,并将表名括起来:
mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;
#mysql50#前缀告诉MySQL忽略在MySQL5.1中引入的文件名安全编码。需要用反引号括起表名,以便对带有特殊字符(如“#”)的表名执行SQL语句 。
注意:
如果在将表移动到另一个表空间的就地ALTER TABLE操作期间发生崩溃,则恢复进程将表恢复到其原始位置,但在目标表空间中留下一个孤立的 中间表。
孤立临时表
如果MySQL在执行复制表的ALTER TABLE操作(ALGORITHM=COPY)时退出,可能会留下一个孤立的临时表,占用系统空间。另外,在一个空的通用表 空间中存在孤立临时表可以防止删除通用表空间。本节介绍如何识别和删除孤立临时表。
孤立临时表名以#sql-开头(例如,#sql-540_3)。附带的.frm文件具有与孤立临时表相同的基名。
注意:
如果没有.frm文件,您可以重新创建它。.frm文件必须具有与孤立临时表相同的表模式(它必须具有相同的列和索引),并且必须放置在孤立临时 表的数据库目录中
要识别系统上的孤立临时表,可以进行查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的表名。如果原始表驻留在file-per- table表空间中,则孤立临时表的表空间文件(#sql-*.ibd文件),在数据库目录中应该可以看到。
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
要删除孤立临时表,可以通过发出drop table语句删除表,例如在表名前加上#mysql50#,并将表名括起来:
mysql> DROP TABLE `#mysql50##sql-540_3`;
#mysql50#前缀告诉MySQL忽略在MySQL5.1中引入的文件名安全编码。需要用反引号括起表名,以便对带有特殊字符(如“#”)的表名执行SQL语句 。
表空间不存在
启用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.
如果出现这种情况,请尝试以下步骤来解决问题:
1.在其他数据库目录中创建一个匹配的.frm文件,并将其复制到孤立表所在的数据库目录中。
2.对原始表发出DROP TABLE命令。这样就可以成功地删除表,InnoDB应该会在错误日志中打印一个.ibd文件丢失的警告。
恢复孤立的File-Per-Table的ibd文件
这个过程描述了如何将孤立的file-per-table的ibd文件恢复到另一个MySQL实例。如果系统表空间丢失或不可恢复,并且希望在新的MySQL实例 上恢复.idb文件备份,则可以使用此过程。
通用表空间.ibd文件不支持此过程。
该过程假设您只有.ibd文件备份,您将恢复到最初创建.idb文件的MySQL的相同版本,并且.idb文件备份是干净的。
1.在新的MySQL实例上,在同名的数据库中重新创建表。
mysql> CREATE DATABASE test; mysql> USE test; mysql> CREATE TABLE `user` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `data` json DEFAULT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
2.丢弃新创建表的表空间
mysql> ALTER TABLE test.user DISCARD TABLESPACE;
3.将孤立.idb文件从备份目录复制到新的数据库目录。
scp root@10.13.13.25:/mysqldata/mysql/test/user.ibd /mysqldata/mysql/test/
4.确保.ibd文件具有必要的文件权限。
[root@localhost test]# chown -R mysql:mysql user.ibd [root@localhost test]# chmod 660 user.ibd
5.导入孤立.ibd文件。发出一个警告,表示InnoDB将尝试在没有模式验证的情况下导入文件。
mysql> ALTER TABLE test.user IMPORT TABLESPACE; SHOW WARNINGS; Query OK, 0 rows affected, 1 warning (0.18 sec) +---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+ | Level | Code | Message | +---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/user.cfg', will attempt to import without schema verification | +---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+ 1 row in set (0.00 sec)
6.查询表以验证.ibd文件是否已成功恢复。
mysql> select * from test.user; +-----+------------------------------------------------------------------------------+ | uid | data | +-----+------------------------------------------------------------------------------+ | 1 | {"mail": "jiangchengyao@gmail.com", "name": "David", "address": "Shangahai"} | | 2 | {"mail": "amy@gmail.com", "name": "Amy"} | +-----+------------------------------------------------------------------------------+ 2 rows in set (0.01 sec)