MySQL数据页损坏，数据库启动不起来，再教你一招搞定！
上一篇文章[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的那部分数据），建议做个备份，然后重建该表，否则可能出现其它问题。
小结
损坏的页，可以通过工具删掉，删除的page数据可能会丢失，所以生产要慎重考虑。
关注我，学习更多的数据库知识！
