数据页损坏
常规情况下如数据库出现坏块，如果数据库出现极端情况导致innodb_force_recovery=6，无法恢复，需要直接读取ibd 文件数据进行恢复，因此整体的恢复思路是把数据从ibd 读取出来，恢复到另外一个实例中
1.创建测试表
mysql> create table student(stu_id int(12),stu_name varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER $
mysql> CREATE PROCEDURE proc_initData()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i<=10000 DO -> INSERT INTO student (stu_id,stu_name) VALUES(i,concat("张三",i));
-> SET i = i+1;
-> END WHILE;
-> END $
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL proc_initData();
Query OK, 1 row affected (4 min 3.81 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
2.标记某个数据块为坏块
[root@localhost soft]# ./bcview /mysqldata/mysql/test/student.ibd 16 0 4
******************************************************************
This Tool Is Uesed For Find The Data In Binary format(Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!
file: Is Your File Will To Find Data!
blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize(Oracle)!
Eg: 16 Is 16 Kb Blocksize(Innodb)!
offset:Is Every Block Offset Your Want Start!
cnt-bytes:Is After Offset,How Bytes Your Want Gets!
Edtor QQ:22389860!
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)
******************************************************************
----Current file size is :0.515625 Mb
----Current use set blockszie is 16 Kb
----Current file name is /mysqldata/mysql/test/student.ibd
current block:00000000--Offset:00000--cnt bytes:04--data is:808a1848
current block:00000001--Offset:00000--cnt bytes:04--data is:7e4a1bda
current block:00000002--Offset:00000--cnt bytes:04--data is:edb00808
current block:00000003--Offset:00000--cnt bytes:04--data is:572aacd7
current block:00000004--Offset:00000--cnt bytes:04--data is:c8d8b056
current block:00000005--Offset:00000--cnt bytes:04--data is:a216e84e
current block:00000006--Offset:00000--cnt bytes:04--data is:3c61ba87
current block:00000007--Offset:00000--cnt bytes:04--data is:f029a1f5
current block:00000008--Offset:00000--cnt bytes:04--data is:100e39cc
current block:00000009--Offset:00000--cnt bytes:04--data is:076fb5f1
current block:00000010--Offset:00000--cnt bytes:04--data is:7274b593
current block:00000011--Offset:00000--cnt bytes:04--data is:db3e0380
current block:00000012--Offset:00000--cnt bytes:04--data is:4ba9595f
current block:00000013--Offset:00000--cnt bytes:04--data is:614beb2e
current block:00000014--Offset:00000--cnt bytes:04--data is:6d3eafa2
current block:00000015--Offset:00000--cnt bytes:04--data is:02aa16e8
current block:00000016--Offset:00000--cnt bytes:04--data is:9d89e9d8
current block:00000017--Offset:00000--cnt bytes:04--data is:6df78bcf
current block:00000018--Offset:00000--cnt bytes:04--data is:2bacdd4d
current block:00000019--Offset:00000--cnt bytes:04--data is:5aec928c
current block:00000020--Offset:00000--cnt bytes:04--data is:f8645813
current block:00000021--Offset:00000--cnt bytes:04--data is:6fe5578f
current block:00000022--Offset:00000--cnt bytes:04--data is:9bfdc9da
current block:00000023--Offset:00000--cnt bytes:04--data is:02cf14da
current block:00000024--Offset:00000--cnt bytes:04--data is:428d4784
current block:00000025--Offset:00000--cnt bytes:04--data is:b2ef644d
current block:00000026--Offset:00000--cnt bytes:04--data is:7759cc93
current block:00000027--Offset:00000--cnt bytes:04--data is:a68e54f6
current block:00000028--Offset:00000--cnt bytes:04--data is:711ad7b5
current block:00000029--Offset:00000--cnt bytes:04--data is:d5c3ea4b
current block:00000030--Offset:00000--cnt bytes:04--data is:b0dfed41
current block:00000031--Offset:00000--cnt bytes:04--data is:ebb3250c
current block:00000032--Offset:00000--cnt bytes:04--data is:00000000
[root@localhost soft]# ./bctool /mysqldata/mysql/test/student.ibd 2 0 11111111
******************************************************************
This tool is uesed to check data ues binary format,no Big-Endian
or Little-Endian diff,this tool is base one byte on byte to change
!block is 16k.if want change other block eg:8k! please set blocks
0 and offset blocks*8192+offset!
usage:./bctool yfile blocks offset yourdata(XX)!
Warings:backup file frist!!!!!!!!!
Editor QQ:22389860
Ues gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)
******************************************************************
11-11-11-11
重启mysql服务
2024-03-19T07:07:20.192633Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=51, page number=2]. You may have to recover from a backup. 2024-03-19T07:07:20.192684Z 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 11111111000000020000000000000000000000000089ae2b0003000000000000000000000033ffffffff2024-03-19T07:07:20.193910Z 0 [Note] Failed to start slave threads for channel '' 0000ffffffff000000000000000000010000000000000000ffffffff0000ffffffff000000000000ffffffff0000ffffffff000000000000ffffffff0000ffffffff000005d669d200000003ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff00000000000000020000000000000000ffffffff0000ffffffff000000000000ffffffff0000ffffffff000000000000ffffffff0000ffffffff000005d669d20000000400000005000000060000000700000008000000090000000a0000000b0000000c0000000d0000000e0000000f000000100000001100000012000000130000001400000015000000160000001700000018000000190000001a0000001b0000001c0000001d0000001e0000001fffffffffffffffffffffffffffffffff00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002024-03-19T07:07:20.211011Z 0 [Note] Event Scheduler: Loaded 0 events
扫描数据字典文件
一般情况下表结构是存储在frm 文件中，drop table 会删除frm 文件，还好我们可以从innodb 系统表里读取一些信息恢复表结构。innodb 系统表有SYS_COLUMNS | SYS_FIELDS |SYS_INDEXES | SYS_TABLES。需要从系统表中恢复，而系统表是保存在$datadir/ibdata1 文件中的解析ibdata 文件，扫描出所有符合innodb 格式的数据页，结果会按照index_id 进行重新组织
[root@localhost undrop-for-innodb-master]# ./stream_parser -f /mysqldata/mysql/ibdata1 Opening file: /mysqldata/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 Opening file: /mysqldata/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 time of last access: 1710832037 Tue Mar 19 15:07:17 2024 time of last modification: 1710832037 Tue Mar 19 15:07:17 2024 time of last status change: 1710832037 Tue Mar 19 15:07:17 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) time of last access: 1710832037 Tue Mar 19 15:07:17 2024 time of last modification: 1710832037 Tue Mar 19 15:07:17 2024 time of last status change: 1710832037 Tue Mar 19 15:07:17 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) Opening file: /mysqldata/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 Opening file: /mysqldata/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 time of last access: 1710832037 Tue Mar 19 15:07:17 2024 time of last modification: 1710832037 Tue Mar 19 15:07:17 2024 time of last status change: 1710832037 Tue Mar 19 15:07:17 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) time of last access: 1710832037 Tue Mar 19 15:07:17 2024 time of last modification: 1710832037 Tue Mar 19 15:07:17 2024 time of last status change: 1710832037 Tue Mar 19 15:07:17 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) All workers finished in 0 sec
扫描数据文件
[root@localhost undrop-for-innodb-master]# ./stream_parser -f /mysqldata/mysql/test/student.ibd Opening file: /mysqldata/mysql/test/student.ibd File information: ID of device containing file: 64768 inode number: 139912091 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 Opening file: /mysqldata/mysql/test/student.ibd group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 File information: number of blocks allocated: 1064 ID of device containing file: 64768 inode number: 139912091 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 1064 time of last access: 1710832027 Tue Mar 19 15:07:07 2024 time of last modification: 1710832014 Tue Mar 19 15:06:54 2024 time of last status change: 1710832014 Tue Mar 19 15:06:54 2024 total size, in bytes: 540672 (528.000 kiB) time of last access: 1710832027 Tue Mar 19 15:07:07 2024 time of last modification: 1710832014 Tue Mar 19 15:06:54 2024 Size to process: 540672 (528.000 kiB) time of last status change: 1710832014 Tue Mar 19 15:06:54 2024 total size, in bytes: 540672 (528.000 kiB) Size to process: 540672 (528.000 kiB) Opening file: /mysqldata/mysql/test/student.ibd File information: ID of device containing file: 64768 inode number: 139912091 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 1064 Opening file: /mysqldata/mysql/test/student.ibd File information: ID of device containing file: 64768 inode number: 139912091 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 1064 time of last access: 1710832027 Tue Mar 19 15:07:07 2024 time of last modification: 1710832014 Tue Mar 19 15:06:54 2024 time of last status change: 1710832014 Tue Mar 19 15:06:54 2024 total size, in bytes: 540672 (528.000 kiB) Size to process: 540672 (528.000 kiB) time of last access: 1710832027 Tue Mar 19 15:07:07 2024 time of last modification: 1710832014 Tue Mar 19 15:06:54 2024 time of last status change: 1710832014 Tue Mar 19 15:06:54 2024 total size, in bytes: 540672 (528.000 kiB) Size to process: 540672 (528.000 kiB) All workers finished in 0 sec
获取table_id
[root@localhost undrop-for-innodb-master]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep student SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`); 000000004808 A80000011B0110 SYS_TABLES "test/student" 63 2 33 0 80 "" 51 获取index_id [root@localhost undrop-for-innodb-master]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 63 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); 000000004808 A80000011B0166 SYS_INDEXES 63 52 "GEN\_CLUST\_INDEX" 0 1 51 3
解析获得ddl 语句
[root@localhost scripts-2.7]# mysqlfrm --diagnostic /mysqldata/mysql/test/student.frm |grep -v "^#" CREATE TABLE `test`.`student` ( `stu_id` int(12) DEFAULT NULL, `stu_name` varchar(80) DEFAULT NULL ) ENGINE=InnoDB; [root@localhost tmp]# vi student.sql CREATE TABLE `student` ( `stu_id` int(12) DEFAULT NULL, `stu_name` varchar(80) DEFAULT NULL ) ENGINE=InnoDB;
解析对应的page 页数据
请注意表结构中不要带有中文和schema_name
[root@localhost undrop-for-innodb-master]# ./c_parser -6f pages-student.ibd/FIL_PAGE_INDEX/0000000000000052.page -t /tmp/student.sql > dumps/default/student 2> dumps/default/student.sql [root@localhost undrop-for-innodb-master]# more dumps/default/student.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/student' REPLACE INTO TABLE `student` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'student\t' (`stu_id`, `stu_name`); [root@localhost undrop-for-innodb-master]# more dumps/default/student -- Page id: 9, Format: COMPACT, Records list: Valid, Expected records: (376 376) 0000000039B4 0000000050CD C5000001770110 student 1717 "张三1717" 0000000039B5 0000000050CE C6000001780110 student 1718 "张三1718" 0000000039B6 0000000050CF C7000001790110 student 1719 "张三1719" 0000000039B7 0000000050D0 C80000017A0110 student 1720 "张三1720" 0000000039B8 0000000050D1 C90000017B0110 student 1721 "张三1721" 0000000039B9 0000000050D2 CA000001540110 student 1722 "张三1722" 0000000039BA 0000000050D3 CB000001550110 student 1723 "张三1723" 0000000039BB 0000000050D4 CC000001560110 student 1724 "张三1724" 0000000039BC 0000000050D5 CD000001570110 student 1725 "张三1725" 0000000039BD 0000000050D6 CE000001580110 student 1726 "张三1726" 0000000039BE 0000000050D7 CF000001590110 student 1727 "张三1727" 0000000039BF 0000000050D8 D00000015A0110 student 1728 "张三1728" 0000000039C0 0000000050D9 D10000015B0110 student 1729 "张三1729" 0000000039C1 0000000050DA D20000015C0110 student 1730 "张三1730" 0000000039C2 0000000050DB D30000015D0110 student 1731 "张三1731" 0000000039C3 0000000050DC D40000015E0110 student 1732 "张三1732" 0000000039C4 0000000050DD D50000015F0110 student 1733 "张三1733" 0000000039C5 0000000050DE D60000017C0110 student 1734 "张三1734" 0000000039C6 0000000050DF D70000017D0110 student 1735 "张三1735" 0000000039C7 0000000050E0 D80000017E0110 student 1736 "张三1736" 0000000039C8 0000000050E1 D90000017F0110 student 1737 "张三1737" 0000000039C9 0000000050E2 DA000001610110 student 1738 "张三1738" 0000000039CA 0000000050E3 DB000001800110 student 1739 "张三1739" 0000000039CB 0000000050E4 DC000001630110 student 1740 "张三1740" 0000000039CC 0000000050E5 DD000001810110 student 1741 "张三1741" 0000000039CD 0000000050E6 DE000001820110 student 1742 "张三1742" 0000000039CE 0000000050E7 DF000001830110 student 1743 "张三1743" 0000000039CF 0000000050E8 E0000001840110 student 1744 "张三1744" 0000000039D0 0000000050E9 E1000001850110 student 1745 "张三1745" 0000000039D1 0000000050EA E2000001860110 student 1746 "张三1746" 0000000039D2 0000000050EB E3000001870110 student 1747 "张三1747" 0000000039D3 0000000050EC E4000001880110 student 1748 "张三1748" 0000000039D4 0000000050ED E5000001890110 student 1749 "张三1749" 0000000039D5 0000000050EE E60000018A0110 student 1750 "张三1750" 0000000039D6 0000000050EF E70000018B0110 student 1751 "张三1751" 0000000039D7 0000000050F0 E80000018C0110 student 1752 "张三1752" 0000000039D8 0000000050F1 E90000018D0110 student 1753 "张三1753" 0000000039D9 0000000050F2 EA0000018E0110 student 1754 "张三1754" 0000000039DA 0000000050F3 EB0000018F0110 student 1755 "张三1755" 0000000039DB 0000000050F4 EC000001900110 student 1756 "张三1756" 0000000039DC 0000000050F5 ED000001910110 student 1757 "张三1757" 0000000039DD 0000000050F6 EE000001920110 student 1758 "张三1758" 0000000039DE 0000000050F7 EF000001930110 student 1759 "张三1759" 0000000039DF 0000000050F8 F0000001940110 student 1760 "张三1760" 0000000039E0 0000000050F9 F1000001950110 student 1761 "张三1761" 0000000039E1 0000000050FA F2000001960110 student 1762 "张三1762" 0000000039E2 0000000050FB F3000001970110 student 1763 "张三1763" 0000000039E3 0000000050FC F4000001990110 student 1764 "张三1764" 0000000039E4 0000000050FD F50000019A0110 student 1765 "张三1765" 0000000039E5 0000000050FE F60000019B0110 student 1766 "张三1766" 0000000039E6 0000000050FF F70000019C0110 student 1767 "张三1767" 0000000039E7 000000005100 F80000019D0110 student 1768 "张三1768" 0000000039E8 000000005101 F90000019E0110 student 1769 "张三1769" 0000000039E9 000000005102 FA0000019F0110 student 1770 "张三1770" 0000000039EA 000000005103 FB000001A00110 student 1771 "张三1771" 0000000039EB 000000005104 FC000001A10110 student 1772 "张三1772" 0000000039EC 000000005105 FD000001A20110 student 1773 "张三1773" 0000000039ED 000000005106 FE000001A30110 student 1774 "张三1774" 0000000039EE 000000005107 FF000001A40110 student 1775 "张三1775" 0000000039EF 000000005108 800000010D0110 student 1776 "张三1776" 0000000039F0 000000005109 A1000001120110 student 1777 "张三1777"
数据导入
[root@localhost tmp]# scp student.sql root@10.138.130.250:/ The authenticity of host '10.138.130.250 (10.138.130.250)' can't be established. ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.138.130.250' (ECDSA) to the list of known hosts. root@10.138.130.250's password: student.sql 100% 116 0.1KB/s 00:00 [root@localhost tmp]# scp /undrop-for-innodb-master/dumps/default/student* root@10.138.130.250:/ root@10.138.130.250's password: student 100% 1699KB 1.7MB/s 00:00 student.sql 100% 235 0.2KB/s 00:00 [root@localhost tmp]# mysql> source /mysqldata/student.sql Query OK, 0 rows affected (0.00 sec) Query OK, 25828 rows affected (0.28 sec) Records: 25828 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 25828 | +----------+ 1 row in set (0.01 sec)
比损坏之前多了一万多条记录。
mysql> select count(distinct stu_id,stu_name) from student; +---------------------------------+ | count(distinct stu_id,stu_name) | +---------------------------------+ | 10000 | +---------------------------------+ 1 row in set (0.03 sec) mysql> select * from student where stu_id=1; +--------+----------+ | stu_id | stu_name | +--------+----------+ | 1 | 张三1 | +--------+----------+ 1 row in set (0.02 sec) mysql> select * from student where stu_id=2; +--------+----------+ | stu_id | stu_name | +--------+----------+ | 2 | 张三2 | +--------+----------+ 1 row in set (0.01 sec) mysql> select * from student where stu_id=100; +--------+-----------+ | stu_id | stu_name | +--------+-----------+ | 100 | 张三100 | +--------+-----------+ 1 row in set (0.02 sec) mysql> select * from student where stu_id=1800; +--------+------------+ | stu_id | stu_name | +--------+------------+ | 1800 | 张三1800 | | 1800 | 张三1800 | +--------+------------+ 2 rows in set (0.02 sec)
也就是标记为损坏的数据块所存储的记录在解析时出现了重复。将重复记录删除掉就可以恢复到损坏状态。