月份：2024年5月

MySQL数据块损坏的恢复

Posted on by YongJing(敬勇)

数据页损坏
常规情况下如数据库出现坏块，如果数据库出现极端情况导致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)

也就是标记为损坏的数据块所存储的记录在解析时出现了重复。将重复记录删除掉就可以恢复到损坏状态。

MySQL数据文件被删除后不能正常启动的恢复

Posted on by YongJing(敬勇)

数据文件被删除后的恢复
常规情况下如数据库出现坏块，如果数据库出现极端情况导致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.将数据文件student.ibd模拟损坏其中一个数据块

[root@localhost test]# dd if=/dev/zero of=/mysqldata/mysql/test/student.ibd bs=16384 skip=2 count=1
1+0 records in
1+0 records out
16384 bytes (16 kB) copied, 0.00080267 s, 20.4 MB/s

[root@localhost undrop-for-innodb-master]# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/mysqldata/mysql/mysqld.pid).

2024-03-15T07:53:59.455587Z 0 [ERROR] InnoDB: The size of tablespace file ./test/student.ibd is only 16384, should be at least 65536!
2024-03-15 15:53:59 0x7f53857fa700  InnoDB: Assertion failure in thread 139996698748672 in file fil0fil.cc line 793

3.设置恢复参数innodb_force_recovery
设置innodb_force_recovery=1-6分别尝试启动mysql服务。

[root@localhost undrop-for-innodb-master]# service mysqld start
Starting MySQL.. SUCCESS!
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| intable        |
| iuser          |
| student        |
| t2             |
+----------------+
4 rows in set (0.01 sec)

mysql> drop table student;
ERROR 1051 (42S02): Unknown table 'test.student'

4.在其它mysql实例中创建一个同名的表并将表的frm文件拷贝到被意外删除表相关文件的目录下后再执行删除

mysql> drop table student;
Query OK, 0 rows affected (0.16 sec)

2024-03-15T08:27:07.906624Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-03-15T08:27:07.906835Z 0 [Note] /mysqlsoft/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 3036 ...
2024-03-15T08:27:07.916535Z 0 [Note] InnoDB: PUNCH HOLE support available
2024-03-15T08:27:07.916618Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-03-15T08:27:07.916719Z 0 [Note] InnoDB: Uses event mutexes
2024-03-15T08:27:07.916771Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2024-03-15T08:27:07.916831Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-03-15T08:27:07.917573Z 0 [Note] InnoDB: Number of pools: 1
2024-03-15T08:27:07.917926Z 0 [Note] InnoDB: Using CPU crc32 instructions
2024-03-15T08:27:07.922739Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2024-03-15T08:27:07.939838Z 0 [Note] InnoDB: Completed initialization of buffer pool
2024-03-15T08:27:07.945196Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2024-03-15T08:27:07.987840Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2024-03-15T08:27:08.275126Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-03-15T08:27:08.275233Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-03-15T08:27:08.275297Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2024-03-15T08:27:08.275331Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './test/student.ibd' OS error: 71
2024-03-15T08:27:08.275371Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-03-15T08:27:08.275422Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-03-15T08:27:08.275450Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2024-03-15T08:27:08.275481Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `test/student`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2024-03-15T08:27:08.275521Z 0 [Warning] InnoDB: Ignoring tablespace `test/student` because it could not be opened.
2024-03-15T08:27:08.277095Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-03-15T08:27:08.277447Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-03-15T08:27:08.411733Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-03-15T08:27:08.414031Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2024-03-15T08:27:08.414082Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2024-03-15T08:27:08.414841Z 0 [Note] InnoDB: Waiting for purge to start
2024-03-15T08:27:08.465455Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 6008194
2024-03-15T08:27:08.465540Z 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
2024-03-15T08:27:08.465938Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool
2024-03-15T08:27:08.466538Z 0 [Note] Plugin 'FEDERATED' is disabled.
2024-03-15T08:27:08.485021Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240315 16:27:08
2024-03-15T08:27:08.555227Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2024-03-15T08:27:08.556469Z 0 [Warning] CA certificate ca.pem is self signed.
2024-03-15T08:27:08.559933Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2024-03-15T08:27:08.560137Z 0 [Note] IPv6 is available.
2024-03-15T08:27:08.560258Z 0 [Note]   - '::' resolves to '::';
2024-03-15T08:27:08.560370Z 0 [Note] Server socket created on IP: '::'.
2024-03-15T08:27:08.632833Z 0 [Note] Failed to start slave threads for channel ''
2024-03-15T08:27:08.652361Z 0 [Note] Event Scheduler: Loaded 0 events
2024-03-15T08:27:08.652743Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: ready for connections.
Version: '5.7.26-log'  socket: '/mysqlsoft/mysql/mysql.sock'  port: 3306  Source distribution
2024-03-15T08:27:24.889709Z 2 [ERROR] Invalid (old?) table or database name 'mysql-keyring'
2024-03-15T08:36:28.778461Z 2 [ERROR] InnoDB: Failed to find tablespace for table `test`.`student` in the cache. Attempting to load the tablespace with space id 45
2024-03-15T08:36:28.789158Z 2 [ERROR] InnoDB: In file './test/student.ibd', tablespace id and flags are 47 and 33, but in the InnoDB data dictionary they are 45 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2024-03-15T08:36:28.789251Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-03-15T08:36:28.789297Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-03-15T08:36:28.789365Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for `test/student`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
Proudly powered by WordPress | Indrajeet by Sus Hill.