标签：搞定 SPACE 宕机 corrupt InnoDB MySQL 空间 PAGE SIZE

查看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 -A

Database 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 系统表[1] 会对性能有影响，因此不建议随意在生产环境执行。

如果错误日志中有提示space id和index 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来说，日常要做好数据备份工作，包括全备份和日志备份。及时备份是非常有必要的措施，同时我们还需要定时验证备份文件的有效性，保证备份文件可以正常使用。确定要使用该方案是要确保有原始损坏数据的备份，谨慎在生产环境使用。

关于oracle，如何强制开库，可以参考oracle无法open，教你用“六脉神剑”来开库这篇文章。

标签：搞定,SPACE,宕机,corrupt,InnoDB,MySQL,空间,PAGE,SIZE
From： https://www.cnblogs.com/cheyunhua/p/18468497

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

查看MySQL error日志

添加强制恢复参数

定位损坏的表

处理有问题的表

相关文章

赞助商

阅读排行