MySQL数据页损坏，数据库启动不起来，再教你一招搞定！

老苏畅谈运维

已于 2024-09-17 10:49:46 修改

阅读量112

点赞数 2

分类专栏： MySQL 故障诊断文章标签： mysql 数据库

于 2024-09-17 10:39:05 首次发布

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/142312160

版权

MySQL 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

故障诊断

6 篇文章 0 订阅

订阅专栏

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

上一篇文章MySQL数据库宕机，启动不起来，教你一招搞定！介绍了数据页损坏一种修复方法，现在介绍另外一种方法，使用第三方工具Inno_space来进行修复。Inno_space 是一个专为解析和修复 InnoDB 表空间文件（.ibd）设计的命令行工具。它允许用户直接访问和操作这些文件，同时还支持修复 corrupt page 功能，如果遇到InnoDB 表文件中的page 损坏、实例无法启动的情况，inno_space 可以将corrupt page 跳过。从而保证实例能够启动，并且将绝大部分的数据找回。

inno_space安装

相关地址：https://github.com/baotiao/inno_space

首先，确保你的系统上已经安装了 make 和 gcc。然后，通过以下命令克隆并编译 Inno_space：

git clone https://github.com/baotiao/inno_space.git

yum install gcc gcc-c++ make -y
cd inno_space
make -j 4

###只需要make, 就可以得到可执行文件, 做到开箱即用.

查看使用帮助：

# ./inno --help
Inno space
usage: inno [-h] [-f test/t.ibd] [-p page_num]
	-h                -- show this help
	-f test/t.ibd     -- ibd file 
		-c list-page-type      -- show all page type
		-c index-summary       -- show indexes information
		-c show-undo-file       -- show undo log file detail
	-p page_num       -- show page information
		-c show-records        -- show all records information
	-u page_num       -- update page checksum
	-d page_num       -- delete page 
Example: 
====================================================
Show sbtest1.ibd all page type
./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -c list-page-type
Show sbtest1.ibd all indexes information
./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -c index-summary
Show undo_001 all rseg information
./inno -f ~/git/primary/dbs2250/log/undo_001 -c show-undo-file
Show specify page information
./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -p 10
Delete specify page
./inno -f ~/git/primary/dbs2250/test/t1.ibd -d 2
Update specify page checksum
./inno -f ~/git/primary/dbs2250/test/t1.ibd -u 2

修复损坏页

MySQL error日志报错如下：

2024-09-15T16:54:52.144828+08:00 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=97, page number=2]. You may have to recover from a backup.
...................................
InnoDB: End of page dump
InnoDB: Page may be an 'inode' page
2024-09-15T16:54:53.031786+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-15 16:54:53 0x7f455affd700  InnoDB: Assertion failure in thread 139935856187136 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
...........................
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf7ab35]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4b9)[0x7faa09]
/lib64/libpthread.so.0(+0xf630)[0x7f45db520630]
/lib64/libc.so.6(gsignal+0x37)[0x7f45d9f08387]
/lib64/libc.so.6(abort+0x148)[0x7f45d9f09a78]
/usr/local/mysql/bin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7e993e]
/usr/local/mysql/bin/mysqld(_ZN2ib5fatalD1Ev+0xb3)[0x1276283]
/usr/local/mysql/bin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x3ff)[0x12b276f]
/usr/local/mysql/bin/mysqld[0x12d48ac]
/usr/local/mysql/bin/mysqld(_Z24buf_read_page_backgroundRK9page_id_tRK11page_size_tb+0x2b)[0x12d4e2b]
/usr/local/mysql/bin/mysqld[0x12c3ca3]
/usr/local/mysql/bin/mysqld(buf_dump_thread+0x155)[0x12c52c5]
/lib64/libpthread.so.0(+0x7ea5)[0x7f45db518ea5]
/lib64/libc.so.6(clone+0x6d)[0x7f45d9fd0b0d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

从日志报错看，page number=2 这个页存在问题。

修复方法：删除损坏的数据页（page number=2）

./inno -f /data/3307/data/dbtest/t_corrupt.ibd -d 2
File path /data/3307/data/dbtest/t_corrupt.ibd path, page num 2
=========================2's block==========================
FIL Header:
CheckSum: 2562232120
Page number: 2
Previous Page: 0
Next Page: 0
Page LSN: 4578585
Page Type: 3
Flush LSN: 0

Index Header:
Number of Directory Slots: 65535
Garbage Space: 65535
Number of Head Records: 0
Number of Records: 0
Max Trx id: 281474976710656
Page level: 0
Index ID: 18446744069414649855
==========================DeletePage==========================
CheckSum: 2562232120
crc 1973070033
Delete Page can't next or prev page, prev_page 0, next_page 0

更新数据页中checksum 值

./inno -f /data/3307/data/dbtest/t_corrupt.ibd -u 2
File path /data/3307/data/dbtest/t_corrupt.ibd path, page num 2
=========================2's block==========================
FIL Header:
CheckSum: 2562232120
Page number: 2
Previous Page: 0
Next Page: 0
Page LSN: 4578585
Page Type: 3
Flush LSN: 0

Index Header:
Number of Directory Slots: 65535
Garbage Space: 65535
Number of Head Records: 0
Number of Records: 0
Max Trx id: 281474976710656
Page level: 0
Index ID: 18446744069414649855
==========================DeletePage==========================
CheckSum: 2562232120
crc 1973070033
UpdateCheckSum 16384

验证数据

重新启动数据库

systemctl start mysqld

检查数据

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

可以正常查到数据，但是数据可能会减少了（数据少了删除掉的page的那部分数据），建议做个备份，然后重建该表，否则可能出现其它问题。