MySQL数据库宕机,启动不起来,教你一招搞定!

2024-09-09 17:44

本文主要是介绍MySQL数据库宕机,启动不起来,教你一招搞定!,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)
公众号:老苏畅谈运维
欢迎关注本人公众号,更多精彩与您分享。

MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。

查看MySQL error日志

查看 MySQL error日志,排查哪个表(表空间)文件破坏或者丢失,线索就是[page id: space=xxx, page number=xxx]。

2024-09-09T10:12:39.111413+08:00 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=73, page number=3]. You may have to recover from a backup.
.......................................
InnoDB: End of page dump
InnoDB: Page may be an index page where index id is 89
2024-09-09T10:12:39.907855+08:00 0 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or,  there was a failure in tagging the tablespace  as corrupt.
2024-09-09 10:12:39 0x7f7fe37fe700  InnoDB: Assertion failure in thread 140187254384384 in file ut0ut.cc line 921
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
02:12:39 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

从日志内容来看,“page id: space=73, page number=3”,MySQL表空间page id 73 损坏,导致无法正常启动,读取不到需要的数据。

添加强制恢复参数

往配置文件中添加强制恢复参数,先将数据库忽略错误启动(强制启动数据库服务)。innodb_force_recovery值最高支持设置到 6,但是值为 4 或更大可能会永久损坏数据文件。因此当强制 InnoDB 恢复时,应始终以innodb_force_recovery=1开头,并仅在必要时递增该值。

[mysqld]
innodb_force_recovery = 1
  • MySQL 有个一个特性:Forcing InnoDB Recovery,启用这个特性需要设置 innodb_force_recovery 大于 0。
  • innodb_force_recovery 可以设置为 1-6,大的值包含前面所有小于它的值的影响。
  • 建议从最小的开始尝试,1到6依次的依次启动。

innodb_force_recovery相关值说明:

1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可,然后 dump 出库表进行重建。2 (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行,如主线程需要执行 full purge 操作,会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。3 (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值,则将来要删除和重建辅助索引。5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志,InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态,继而可能引起 B 树或者其他数据库结构更多的损坏。

另外从 MySQL 5.7.18 开始, DROP TABLE不允许使用 innodb_force_recovery大于 4 的值。

定位损坏的表

根据MYSQL服务启动之后的报错日志提示,定位有问题的表相关信息。我们需要进入information_schma 数据库,查看相关视图,获取信息:

针对MySQL5.7:
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from INNODB_SYS_TABLES where SPACE=73;
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
|       88 | dbtest/t_corrupt |   33 |      4 |    73 | Barracuda   | Dynamic    |             0 | Single     |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)mysql> select * from INNODB_SYS_TABLESPACES where SPACE=73;
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME             | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|    73 | dbtest/t_corrupt |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 |     98304 |          98304 |
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.00 sec)如果是MySQL8.0,则使用以下方式进行查看:
select * from INNODB_TABLES where SPACE=73
或
select * from INNODB_TABLESPACES where SPACE=73

INNODB_SYS_TABLESPACES该表提供查询有关InnoDB独立表空间和普通表空间的元数据信息(也包含了全文索引表空间),等同于InnoDB数据字典中SYS_TABLESPACES表中的信息。

  • 该表为memory引擎临时表,查询该表的用户需要有process权限
  • INFORMATION_SCHEMA.FILES表提供查询的信息中包含所有InnoDB表空间类型的元数据信息,包括独立表空间、普通表空间、系统表空间、临时表空间和undo表空间(如果有)
  • 因为对于所有Antelope文件格式的表空间文件(注意与表的FLAG不同),表空间FLAG信息始终为零,所以如果表空间行格式为 Redundant 或 Compact,则无法使用该FLAG信息确定一个十进制的整数(也就是说在Antelope文件格式的表空间文件中,无法通过表空间文件的FLAG信息判断行格式是Compact、 Redundant、Compressed、Dynamic中的哪一种)
  • 普通表空间引入之后,系统表空间的元数据信息也在INNODB_SYS_TABLESPACES表暴露出来提供了查询
mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name like '%country%';
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME                  | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|    27 | sakila/country        |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |     98304 |          98304 |
|    51 | world/country         |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |    180224 |         180224 |
|    52 | world/countrylanguage |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |    229376 |         229376 |
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.10 sec)

字段含义如下:

  • SPACE:表空间文件ID
  • NAME:数据库和表名组合字符串,例如:test/t1
  • FLAG:有关表空间文件存储格式和存储特性的bit位级数据
  • FILE_FORMAT:表空间文件存储格式。例如:Antelope、Barracuda或普通表空间支持的任何行格式。该字段中的数据是根据驻留在.ibd文件中的表空间FLAG信息进行解释的。
  • ROW_FORMAT:表空间的行格式(Compact、 Redundant、Compressed、Dynamic),该字段中的数据是根据驻留在.ibd文件中的表空间FLAG信息进行解释的
  • PAGE_SIZE:表空间中的页大小。该字段中的数据是根据驻留在.ibd文件中的表空间FLAG信息进行解释的
  • ZIP_PAGE_SIZE:表空间zip页大小。该字段中的数据是根据驻留在.ibd文件中的表空间FLAG信息进行解释的
  • SPACE_TYPE:表空间文件的类型。可能的值包括:General (普通表空间)和Single (独立表空间文件)
  • FS_BLOCK_SIZE:文件系统中的块大小,它是用于hole punching技术的单位大小。该字段是在InnoDB透明页压缩功能被引入之后新增的
  • FILE_SIZE:文件表面上的大小(即表示文件未压缩时的最大大小)。该字段是在InnoDB透明页压缩功能被引入之后新增的
  • ALLOCATED_SIZE:文件的实际大小,即在磁盘上分配的空间大小。该字段是在InnoDB透明页压缩功能被引入之后新增的

INNODB_SYS_TABLES该表提供查询有关InnoDB表的元数据,等同于InnoDB数据字典中SYS_TABLES表的信息,该表为memory引擎临时表,查询该表的用户需要有process权限。

mysql> select * from information_schema.INNODB_SYS_TABLES where NAME like '%country%';
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                  | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
|       44 | sakila/country        |   33 |      6 |    27 | Barracuda   | Dynamic    |             0 | Single     |
|       68 | world/country         |   33 |     18 |    51 | Barracuda   | Dynamic    |             0 | Single     |
|       69 | world/countrylanguage |   33 |      7 |    52 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
3 rows in set (0.00 sec)

字段含义如下:

  • TABLE_ID:Innodb表ID,在整个实例中唯一
  • NAME:表名称。该字符串包含db_name+tb_name,例如"test/t1",该字符串值可能受lower_case_table_names系统参数设置的影响
  • FLAG:有关表格式和存储特性的位级信息数据,包括行格式,压缩页大小(如果适用)以及DATA DIRECTORY子句是否与CREATE TABLE或ALTER TABLE一起使用等
  • N_COLS:表中的列数量。该字段值包含了Innodb表的三个隐藏列(DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR),另外,如果存在虚拟生成列,则该字段值还包含虚拟生成列
  • SPACE:表所在表空间ID。0表示InnoDB系统表空间。任何其他非0数字独立表空间或普通表空间。该ID值在执行过TRUNCATE TABLE语句后保持不变。对于每个表的表空间ID记录,在此表中的ID值是唯一的
  • FILE_FORMAT:表空间文件的存储格式(有效值为:Antelope、Barracuda)
  • ROW_FORMAT:表的数据行存储格式(有效值为:Compact,、Redundant,、Dynamic、Compressed)
  • ZIP_PAGE_SIZE:压缩页大小。仅适用于使用压缩行格式的表
  • SPACE_TYPE:表所属的表空间类型。可能的值包括:System(系统表空间)、General(普通表空间)、Single(独立表空间)、使用CREATE TABLE或ALTER TABLE 语句时使用TABLESPACE建表选项指定表空间名称,例如:TABLESPACE = innodb_system,表示分配该表到系统表空间,如果需要指定到一个普通表空间(针对NDB存储引擎适用)

处理有问题的表

查看page number内容:

mysql> select * from information_schema.INNODB_BUFFER_PAGE where  SPACE=73 and PAGE_NUMBER=3 ;
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
| POOL_ID | BLOCK_ID | SPACE | PAGE_NUMBER | PAGE_TYPE | FLUSH_TYPE | FIX_COUNT | IS_HASHED | NEWEST_MODIFICATION | OLDEST_MODIFICATION | ACCESS_TIME | TABLE_NAME           | INDEX_NAME      | NUMBER_RECORDS | DATA_SIZE | COMPRESSED_SIZE | PAGE_STATE | IO_FIX  | IS_OLD | FREE_PAGE_CLOCK |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
|       1 |      158 |    73 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |     6180293 | `dbtest`.`t_corrupt` | GEN_CLUST_INDEX |            100 |      2900 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
1 row in set (0.11 sec)

注意:查询 INNODB_BUFFER_PAGE 系统表会对性能有影响,因此不建议随意在生产环境执行。

如果错误日志中有提示space idindex id相关信息,则也可以通过如下方式进行查询:

mysql>  select b.INDEX_ID, a.NAME as TableName, a.SPACE as Space,b.NAME as IndexName from INNODB_SYS_TABLES a,INNODB_SYS_INDEXES b where a.SPACE =b.SPACE and a.SPACE=73 and b.INDEX_ID=89;
+----------+------------------+-------+-----------------+
| INDEX_ID | TableName        | Space | IndexName       |
+----------+------------------+-------+-----------------+
|       89 | dbtest/t_corrupt |    73 | GEN_CLUST_INDEX |
+----------+------------------+-------+-----------------+
1 row in set (0.00 sec)

根据上面的查询结果,确定损坏的页是属于主键还是辅助索引,如果属于主键索引,因为在 MySQL 中索引即数据,则可能会导致数据丢失,如果是辅助索引,删除索引重建即可。

从上面可以查出dbtest库下的t_corrupt这张表是主键索引存在问题,数据可能会有丢失。如果我们想要完整的数据,使用SELECT * FROM t1 就会发生如下错误:

mysql> select * from t_corrupt;
ERROR 2013 (HY000): Lost connection to MySQL server during query

尝试先读取部分数据,看看会不会报错。

select * from t_corrupt limit 100;

用 mysqldump 或者 SELECT … INTO OUTFILE 把表数据导出。

使用mysqldump导出数据
mysqldump -uroot -proot dbtest t_corrupt --where=" true limit 100"  > t_corrupt.sql或者使用SELECT … INTO OUTFILE 把表数据导出
--要设置secure_file_priv参数,才能使用 into outfile 参数把表中数据导出
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)select * 
INTO OUTFILE '/tmp/t_corrupt.sql'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_corrupt
limit 100;

导出完成后,我们就需要把之前旧的数据表删除掉

mysql> drop table t_corrupt;
Query OK, 0 rows affected (0.00 sec)

去掉 innodb_force_recovery 或者设置为 0,,重新启动生产数据库。

[mysqld]
innodb_force_recovery=0  #配置成0

然后重新创建表,把数据导入。

CREATE TABLE t_corrupt (id int(11));mysql -uroot -proot dbtest < t_corrupt.sql或者
LOAD DATA LOCAL INFILE '/tmp/t_corrupt.sql' 
INTO TABLE t_corrupt
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

检查恢复后的数据

mysql> select * from t_corrupt;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
...........
|   98 |
|   99 |
|  100 |
+------+
100 rows in set (0.00 sec)

最后说明,这个方法仅仅是紧急情况下的一种补救,不能依赖于这个办法。对于DBA来说,日常要做好数据备份工作,包括全备份和日志备份。及时备份是非常有必要的措施,同时我们还需要定时验证备份文件的有效性,保证备份文件可以正常使用。确定要使用该方法是要确保有原始损坏数据的备份,innodb_force_recovery设置4以上的值可能永久导致数据文件损坏,谨慎在生产环境使用。

关注我,学习更多的数据库知识!
请添加图片描述

这篇关于MySQL数据库宕机,启动不起来,教你一招搞定!的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1151841

相关文章

MySQL中慢SQL优化的不同方式介绍

《MySQL中慢SQL优化的不同方式介绍》慢SQL的优化,主要从两个方面考虑,SQL语句本身的优化,以及数据库设计的优化,下面小编就来给大家介绍一下有哪些方式可以优化慢SQL吧... 目录避免不必要的列分页优化索引优化JOIN 的优化排序优化UNION 优化慢 SQL 的优化,主要从两个方面考虑,SQL 语

Python实现将MySQL中所有表的数据都导出为CSV文件并压缩

《Python实现将MySQL中所有表的数据都导出为CSV文件并压缩》这篇文章主要为大家详细介绍了如何使用Python将MySQL数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到... python将mysql数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到另一个

如何解决mysql出现Incorrect string value for column ‘表项‘ at row 1错误问题

《如何解决mysql出现Incorrectstringvalueforcolumn‘表项‘atrow1错误问题》:本文主要介绍如何解决mysql出现Incorrectstringv... 目录mysql出现Incorrect string value for column ‘表项‘ at row 1错误报错

一文带你了解SpringBoot中启动参数的各种用法

《一文带你了解SpringBoot中启动参数的各种用法》在使用SpringBoot开发应用时,我们通常需要根据不同的环境或特定需求调整启动参数,那么,SpringBoot提供了哪些方式来配置这些启动参... 目录一、启动参数的常见传递方式二、通过命令行参数传递启动参数三、使用 application.pro

Mysql中深分页的五种常用方法整理

《Mysql中深分页的五种常用方法整理》在数据量非常大的情况下,深分页查询则变得很常见,这篇文章为大家整理了5个常用的方法,文中的示例代码讲解详细,大家可以根据自己的需求进行选择... 目录方案一:延迟关联 (Deferred Join)方案二:有序唯一键分页 (Cursor-based Paginatio

SpringBoot项目启动报错"找不到或无法加载主类"的解决方法

《SpringBoot项目启动报错找不到或无法加载主类的解决方法》在使用IntelliJIDEA开发基于SpringBoot框架的Java程序时,可能会出现找不到或无法加载主类com.example.... 目录一、问题描述二、排查过程三、解决方案一、问题描述在使用 IntelliJ IDEA 开发基于

MySQL中慢SQL优化方法的完整指南

《MySQL中慢SQL优化方法的完整指南》当数据库响应时间超过500ms时,系统将面临三大灾难链式反应,所以本文将为大家介绍一下MySQL中慢SQL优化的常用方法,有需要的小伙伴可以了解下... 目录一、慢SQL的致命影响二、精准定位问题SQL1. 启用慢查询日志2. 诊断黄金三件套三、六大核心优化方案方案

SQL Server清除日志文件ERRORLOG和删除tempdb.mdf

《SQLServer清除日志文件ERRORLOG和删除tempdb.mdf》数据库再使用一段时间后,日志文件会增大,特别是在磁盘容量不足的情况下,更是需要缩减,以下为缩减方法:如果可以停止SQLSe... 目录缩减 ERRORLOG 文件(停止服务后)停止 SQL Server 服务:找到错误日志文件:删除

MySQL中闪回功能的方案讨论及实现

《MySQL中闪回功能的方案讨论及实现》Oracle有一个闪回(flashback)功能,能够用户恢复误操作的数据,这篇文章主要来和大家讨论一下MySQL中支持闪回功能的方案,有需要的可以了解下... 目录1、 闪回的目标2、 无米无炊一3、 无米无炊二4、 演示5、小结oracle有一个闪回(flashb

MySQL使用binlog2sql工具实现在线恢复数据功能

《MySQL使用binlog2sql工具实现在线恢复数据功能》binlog2sql是大众点评开源的一款用于解析MySQLbinlog的工具,根据不同选项,可以得到原始SQL、回滚SQL等,下面我们就来... 目录背景目标步骤准备工作恢复数据结果验证结论背景生产数据库执行 SQL 脚本,一般会经过正规的审批