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

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

复制代码

上一篇文章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：

1. git clone https://github.com/baotiao/inno_space.git
3. yum install gcc gcc-c++ make -y
4. cd inno_space
5. make -j 4
7. ###只需要make, 就可以得到可执行文件, 做到开箱即用.

复制代码

查看利用帮助：

1. # ./inno --help
2. Inno space
3. usage: inno [-h] [-f test/t.ibd] [-p page_num]
4.         -h                -- show this help
5.         -f test/t.ibd     -- ibd file
6.                 -c list-page-type      -- show all page type
7.                 -c index-summary       -- show indexes information
8.                 -c show-undo-file       -- show undo log file detail
9.         -p page_num       -- show page information
10.                 -c show-records        -- show all records information
11.         -u page_num       -- update page checksum
12.         -d page_num       -- delete page
13. Example:
14. ====================================================
15. Show sbtest1.ibd all page type
16. ./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -c list-page-type
17. Show sbtest1.ibd all indexes information
18. ./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -c index-summary
19. Show undo_001 all rseg information
20. ./inno -f ~/git/primary/dbs2250/log/undo_001 -c show-undo-file
21. Show specify page information
22. ./inno -f ~/git/primary/dbs2250/sbtest/sbtest1.ibd -p 10
23. Delete specify page
24. ./inno -f ~/git/primary/dbs2250/test/t1.ibd -d 2
25. Update specify page checksum
26. ./inno -f ~/git/primary/dbs2250/test/t1.ibd -u 2

复制代码

修复损坏页

MySQL error日志报错如下：

1. 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.
2. ...................................
3. InnoDB: End of page dump
4. InnoDB: Page may be an 'inode' page
5. 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.
6. 2024-09-15 16:54:53 0x7f455affd700  InnoDB: Assertion failure in thread 139935856187136 in file ut0ut.cc line 921
7. InnoDB: We intentionally generate a memory trap.
8. InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
9. InnoDB: If you get repeated assertion failures or crashes, even
10. InnoDB: immediately after the mysqld startup, there may be
11. InnoDB: corruption in the InnoDB tablespace. Please refer to
12. ...........................
13. where mysqld died. If you see no messages after this, something went
14. terribly wrong...
15. stack_bottom = 0 thread_stack 0x40000
16. /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf7ab35]
17. /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4b9)[0x7faa09]
18. /lib64/libpthread.so.0(+0xf630)[0x7f45db520630]
19. /lib64/libc.so.6(gsignal+0x37)[0x7f45d9f08387]
20. /lib64/libc.so.6(abort+0x148)[0x7f45d9f09a78]
21. /usr/local/mysql/bin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7e993e]
22. /usr/local/mysql/bin/mysqld(_ZN2ib5fatalD1Ev+0xb3)[0x1276283]
23. /usr/local/mysql/bin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x3ff)[0x12b276f]
24. /usr/local/mysql/bin/mysqld[0x12d48ac]
25. /usr/local/mysql/bin/mysqld(_Z24buf_read_page_backgroundRK9page_id_tRK11page_size_tb+0x2b)[0x12d4e2b]
26. /usr/local/mysql/bin/mysqld[0x12c3ca3]
27. /usr/local/mysql/bin/mysqld(buf_dump_thread+0x155)[0x12c52c5]
28. /lib64/libpthread.so.0(+0x7ea5)[0x7f45db518ea5]
29. /lib64/libc.so.6(clone+0x6d)[0x7f45d9fd0b0d]
30. The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
31. information that should help you find out what is causing the crash.

复制代码

从日志报错看，page number=2 这个页存在问题。
修复方法：删除损坏的数据页（page number=2）

1. ./inno -f /data/3307/data/dbtest/t_corrupt.ibd -d 2
2. File path /data/3307/data/dbtest/t_corrupt.ibd path, page num 2
3. =========================2's block==========================
4. FIL Header:
5. CheckSum: 2562232120
6. Page number: 2
7. Previous Page: 0
8. Next Page: 0
9. Page LSN: 4578585
10. Page Type: 3
11. Flush LSN: 0
13. Index Header:
14. Number of Directory Slots: 65535
15. Garbage Space: 65535
16. Number of Head Records: 0
17. Number of Records: 0
18. Max Trx id: 281474976710656
19. Page level: 0
20. Index ID: 18446744069414649855
21. ==========================DeletePage==========================
22. CheckSum: 2562232120
23. crc 1973070033
24. Delete Page can't next or prev page, prev_page 0, next_page 0

复制代码

更新数据页中checksum 值

1. ./inno -f /data/3307/data/dbtest/t_corrupt.ibd -u 2
2. File path /data/3307/data/dbtest/t_corrupt.ibd path, page num 2
3. =========================2's block==========================
4. FIL Header:
5. CheckSum: 2562232120
6. Page number: 2
7. Previous Page: 0
8. Next Page: 0
9. Page LSN: 4578585
10. Page Type: 3
11. Flush LSN: 0
13. Index Header:
14. Number of Directory Slots: 65535
15. Garbage Space: 65535
16. Number of Head Records: 0
17. Number of Records: 0
18. Max Trx id: 281474976710656
19. Page level: 0
20. Index ID: 18446744069414649855
21. ==========================DeletePage==========================
22. CheckSum: 2562232120
23. crc 1973070033
24. UpdateCheckSum 16384

复制代码

验证数据

重新启动数据库

1. systemctl start mysqld

复制代码

查抄数据

1. mysql> select * from t_corrupt;
2. +------+
3. | id   |
4. +------+
5. |    1 |
6. |    2 |
7. |    3 |
8. |    4 |
9. ...........
10. |   98 |
11. |   99 |
12. |  100 |
13. +------+
14. 100 rows in set (0.00 sec)

复制代码

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

损坏的页，可以通过工具删掉，删除的page数据可能会丢失，以是生产要慎重思量。
关注我，学习更多的数据库知识！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。