InnoDB故障排除
以下一般准则适用于故障排除InnoDB
问题:
- 当操作失败或怀疑有错误时,请参见MySQL服务器错误日志(请参见“MySQL服务器错误日志”)。第B.3.1节“服务器错误消息参考”提供了一些
InnoDB
可能遇到的常见于特定错误的故障排除信息。 - 如果故障与死锁有关,请在
innodb_print_all_deadlocks
启用该选项的情况下运行,以便将有关每个死锁的详细信息打印到MySQL服务器错误日志中。有关死锁的信息,请参见“ InnoDB中的死锁”。 - 如果问题与
InnoDB
数据字典有关,请参见“对InnoDB数据字典操作进行故障排除”。 - 在进行故障排除时,通常最好从命令提示符下运行MySQL服务器,而不是通过mysqld_safe或作为Windows服务运行。然后,您可以看到mysqld打印到控制台的内容,因此可以更好地了解正在发生的情况。在Windows上,使用选项启动mysqld以
--console
将输出定向到控制台窗口。 使
InnoDB
监视器能够获取有关问题的信息(请参见“ InnoDB监视器”)。如果问题与性能有关,或者服务器似乎已挂起,则应启用标准监视器以打印有关的内部状态的信息InnoDB
。如果问题出在锁上,请启用“锁监控器”。如果问题出在表创建,表空间或数据字典操作上,请参考 InnoDB信息模式系统表以检查InnoDB
内部数据字典的内容。InnoDB
InnoDB
在以下情况下临时启用标准 Monitor输出:- 长时间的信号灯等待
InnoDB
在缓冲池中找不到可用的块- 超过67%的缓冲池被锁堆或自适应哈希索引占用
- 如果怀疑表已损坏,请
CHECK TABLE
在该表上运行。
对InnoDB I / O问题进行故障排除
InnoDB
I / O问题的疑难解答步骤取决于问题发生的时间:在MySQL服务器启动期间,或在正常操作过程中,由于文件系统级别的问题而导致DML或DDL语句失败。
初始化问题
如果在InnoDB
尝试初始化其表空间或日志文件时出现问题,请删除由InnoDB
:所有ibdata
文件和所有ib_logfile
文件创建的所有文件。如果您已经创建了一些InnoDB
表,则还要.ibd
从MySQL数据库目录中删除所有文件。然后InnoDB
再次尝试创建数据库。为了最简单的故障排除,请从命令提示符启动MySQL服务器,以便您了解正在发生的情况。
运行时问题
如果InnoDB
在文件操作过程中显示操作系统错误,通常该问题具有以下解决方案之一:
- 确保
InnoDB
数据文件目录和InnoDB
日志目录存在。 - 确保mysqld具有在这些目录中创建文件的访问权限。
- 确保mysqld可以读取正确的文件
my.cnf
或my.ini
选项文件,以便它以您指定的选项开头。 - 确保磁盘未满,并且没有超出任何磁盘配额。
- 确保为子目录和数据文件指定的名称不冲突。
- 仔细检查
innodb_data_home_dir
和innodb_data_file_path
值的语法。特别是,MAX
该innodb_data_file_path
选项中的任何值都是硬限制,超过该限制会导致致命错误。
强制InnoDB恢复
要调查数据库页面损坏,您可以使用来从数据库中转储表SELECT ... INTO OUTFILE
。通常,以这种方式获得的大多数数据都是完整的。严重损坏可能导致语句或后台操作崩溃或断言,甚至导致前滚恢复崩溃。在这种情况下,可以使用该选项在阻止后台操作运行的同时强制启动存储引擎,以便转储表。例如,您可以在重新启动服务器之前将以下行添加到选项文件的部分中:SELECT * FROM tbl_name
InnoDB
InnoDB
innodb_force_recovery
InnoDB
[mysqld]
[mysqld] innodb_force_recovery = 1
有关使用选项文件的信息,请参见“使用选项文件”。
警告仅
innodb_force_recovery
在紧急情况下设置为大于0的值,以便您可以启动InnoDB
和转储表。这样做之前,请确保您拥有数据库的备份副本,以防万一您需要重新创建它。值大于等于4可能会永久损坏数据文件。innodb_force_recovery
在数据库的单独物理副本上成功测试设置之后,请仅在生产服务器实例上使用 4或更大的设置。强制InnoDB
恢复时,应始终从头开始,innodb_force_recovery=1
并根据需要仅逐渐增加该值。
innodb_force_recovery
默认情况下为0(正常启动而不强制恢复)。允许的非零值innodb_force_recovery
是1到6。较大的值包括较小值的功能。例如,值3包含值1和2的所有功能。
如果能够转储innodb_force_recovery
值为3或更小的表,则相对安全的是,仅丢失损坏的单个页面上的某些数据。4或更大的值被认为是危险的,因为数据文件可能会永久损坏。值6被认为是过大的,因为数据库页面处于过时状态,这反过来可能会使B树和其他数据库结构遭受更多破坏。
为了安全起见,请InnoDB
防止INSERT
,UPDATE
或DELETE
在innodb_force_recovery
大于0时进行操作。在只读模式下,将位置innodb_force_recovery
设置为4或更大InnoDB
。
1
(SRV_FORCE_IGNORE_CORRUPT
)使服务器即使检测到损坏的页面也可以运行。尝试跳过损坏的索引记录和页,这有助于转储表。
SELECT * FROM tbl_name
2
(SRV_FORCE_NO_BACKGROUND
)阻止主线程和任何清除线程运行。如果在清除操作期间发生崩溃,则此恢复值可防止崩溃。
3
(SRV_FORCE_NO_TRX_UNDO
)崩溃恢复后不运行事务回滚。
4
(SRV_FORCE_NO_IBUF_MERGE
)防止插入缓冲区合并操作。如果它们会导致崩溃,请不要这样做。不计算表统计信息。此值可能会永久损坏数据文件。使用此值后,准备删除并重新创建所有二级索引。设置
InnoDB
为只读。5
(SRV_FORCE_NO_UNDO_LOG_SCAN
)启动数据库时不参见撤消日志:
InnoDB
甚至将未完成的事务也视为已提交。此值可能会永久损坏数据文件。设置InnoDB
为只读。6
(SRV_FORCE_NO_LOG_REDO
)不进行与恢复有关的重做日志前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态,这又可能导致B树和其他数据库结构遭受更多破坏。设置
InnoDB
为只读。
您可以SELECT
从表中转储它们。随着innodb_force_recovery
3或更低就可以值DROP
或CREATE
表。DROP TABLE
还支持innodb_force_recovery
大于3 DROP TABLE
的innodb_force_recovery
值。不允许大于4 的值。
如果您知道给定的表导致回滚崩溃,则可以将其删除。如果遇到由于批量导入或失败而导致的失控回滚ALTER TABLE
,则可以终止mysqld进程,并设置innodb_force_recovery
为3
不回滚就启动数据库,然后DROP
启动导致失控回滚的表。
如果表数据中的损坏阻止您转储整个表内容,则带有子句的查询可能能够转储损坏部分后的表部分。ORDER BY primary_key DESC
如果一个高innodb_force_recovery
值是必需的开始InnoDB
,有可能是,可能导致(含有查询的复杂查询损坏的数据结构WHERE
,ORDER BY
或其它条款)失败。在这种情况下,您可能只能运行基本SELECT * FROM t
查询。
对InnoDB数据字典操作进行故障排除
有关表定义的信息存储在InnoDB 数据字典中。如果四处移动数据文件,则词典数据可能会变得不一致。
如果数据字典损坏或一致性问题使您无法启动InnoDB
,请参阅“强制InnoDB恢复”以获取有关手动恢复的信息。
无法打开数据文件
随着innodb_file_per_table
启用(默认值),下面的消息可能会在启动时出现,如果一个文件的每个表的表空间文件(.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
语句以从数据字典中删除有关丢失表的数据。
恢复每表孤儿文件ibd文件
此过程描述了如何将每表孤立文件还原.ibd
到另一个MySQL实例。如果系统表空间丢失或不可恢复,并且要.ibd
在新的MySQL实例上还原文件备份,则可以使用此过程。
常规表空间.ibd
文件不支持该过程。
该过程假定您只有.ibd
文件备份,并且要恢复到最初创建孤立.ibd
文件的MySQL版本,并且.ibd
文件备份是干净的。有关创建干净备份的信息,请参见“移动或复制InnoDB表”。
“导入InnoDB表”中概述的表导入限制适用于此过程。
在新的MySQL实例上,在同名数据库中重新创建表。
mysql>
CREATE DATABASE sakila; mysql>USE sakila; mysql>CREATE TABLE actor ( actor_id SMALLINTUNSIGNED NOT NULLAUTO_INCREMENT , first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,PRIMARY KEY (actor_id),KEY idx_actor_last_name (last_name) )ENGINE =InnoDBDEFAULT CHARSET =utf8;丢弃新创建的表的表空间。
mysql>
ALTER TABLE sakila.actorDISCARD TABLESPACE ;将孤立
.ibd
文件从备份目录复制到新的数据库目录。shell>
cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/- 确保
.ibd
文件具有必要的文件权限。 导入孤立
.ibd
文件。发出警告,指示InnoDB
将尝试在不进行模式验证的情况下导入文件。mysql>
ALTER TABLE sakila.actorIMPORT TABLESPACE ;SHOW WARNINGS ; Query OK, 0 rows affected, 1 warning (0.15 sec) Warning | 1810 | InnoDB:IO Read error : (2,No suchfile ordirectory )Error opening './sakila/actor.cfg', will attemptto import without schema verification查询表以确认
.ibd
文件已成功还原。mysql>
SELECT COUNT(*)FROM sakila.actor; +---------- + | count(*) | +---------- + | 200 | +---------- +
InnoDB错误处理
以下各项描述了如何InnoDB
执行错误处理。InnoDB
有时仅回滚失败的语句,而其他时候回滚整个事务。
- 如果表空间中的文件空间用完,
Table is full
则会发生MySQL 错误并InnoDB
回滚SQL语句。 一个事务死锁导致
InnoDB
要回滚整个事务。发生这种情况时,请重试整个事务。锁定等待超时导致
InnoDB
仅回滚正在等待锁定且遇到超时的单个语句。(要使整个事务回滚,请使用--innodb-rollback-on-timeout
选项启动服务器。)如果使用当前行为,请重试该语句;如果使用,请重试整个事务--innodb-rollback-on-timeout
。死锁和锁等待超时在繁忙的服务器上都是正常的,应用程序必须意识到它们可能发生并通过重试来处理它们。您可以通过在事务期间第一次更改数据和提交之间进行尽可能少的工作来减少它们的可能性,因此将锁保持在最短的时间内,并且行的数量最少。有时,在不同交易之间分配工作可能是实用且有用的。
当由于死锁或锁定等待超时而导致事务回滚时,它将取消事务中语句的影响。但是,如果start-transaction语句是
START TRANSACTION
orBEGIN
语句,则回滚不会取消该语句。进一步的SQL语句成为交易的一部分,直到发生COMMIT
,ROLLBACK
或某些SQL语句导致隐式提交。- 如果未
IGNORE
在语句中指定选项,则重复键错误会回滚SQL 语句。 - 一个
row too long error
回滚SQL语句。 - 其他错误大多数由MySQL代码层(在
InnoDB
存储引擎级别之上)检测到,并且它们回滚相应的SQL语句。在单个SQL语句的回滚中不会释放锁。
在隐式回滚期间以及在执行显式ROLLBACK
SQL语句期间,将在相关连接的列中SHOW PROCESSLIST
显示。Rolling back
State