一．数据头损坏
1：观察日志
数据库启动失败，同时查看日志文件中出现头文件校验时出现报错
[root@localhost soft]# service mysqld start
从日志文件可以看到mysql启动失败了，因为Space ID in fsp header is 39, but in the page header it is 38.
2024-03-11T07:32:33.414216Z 0 [ERROR] InnoDB: Space ID in fsp header is 39, but in the page header it is 38. 2024-03-11T07:32:33.414356Z 0 [ERROR] [FATAL] InnoDB: Tablespace id is 38 in the data dictionary but in file ./test/iuser.ibd it is 18446744073709551615! 2024-03-11 15:32:33 0x7efc05ffb700 InnoDB: Assertion failure in thread 139620897502976 in file ut0ut.cc line 942 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 InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 07:32:33 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=0 max_threads=151 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68196 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x40000 /mysqlsoft/mysql/bin/mysqld(my_print_stacktrace+0x2c)[0xebd4ec] /mysqlsoft/mysql/bin/mysqld(handle_fatal_signal+0x451)[0x7aa371] /lib64/libpthread.so.0(+0xf130)[0x7efc4d9e7130] /lib64/libc.so.6(gsignal+0x37)[0x7efc4c7ec5d7] /lib64/libc.so.6(abort+0x148)[0x7efc4c7edcc8] /mysqlsoft/mysql/bin/mysqld[0x77a965] /mysqlsoft/mysql/bin/mysqld(_ZN2ib5fatalD1Ev+0x4d)[0x108763d] /mysqlsoft/mysql/bin/mysqld[0x1136bf3] /mysqlsoft/mysql/bin/mysqld[0x113727a] /mysqlsoft/mysql/bin/mysqld(_Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_+0x262)[0x1141cc2] /mysqlsoft/mysql/bin/mysqld[0x10f215a] /mysqlsoft/mysql/bin/mysqld(_Z24buf_read_page_backgroundRK9page_id_tRK11page_size_tb+0x2b)[0x10f400b] /mysqlsoft/mysql/bin/mysqld[0x10d7de5] /mysqlsoft/mysql/bin/mysqld(buf_dump_thread+0x121)[0x10d8371] /lib64/libpthread.so.0(+0x7df5)[0x7efc4d9dfdf5] /lib64/libc.so.6(clone+0x6d)[0x7efc4c8ad60d] 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.
2：space id 说明
正常情况下Space ID 在fsp header 和page header 是一样的。Space id 在所有的页块中中都有存在。其中fsp header(file space header)占用的空间为(数据文件的第一个块的38-150 字节)。page header 占用的空间为每个块固有的前38 字节。而space id 在fsp header 中为38-41，在page header 为34-37.
3：查找正确的space id 值.
由于是某个页头损坏，space id 不正确，而space id 在每个页中都存在，只需要确认绝大部分的页头的的space id 为多少即可。可以看到页头space id 为00000026（16 进制），换成10 进制为38，也就是38 是正确的数字space id。
[root@localhost soft]# ./bcview /mysqldata/mysql/test/iuser.ibd 16 34 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.093750 Mb
----Current use set blockszie is 16 Kb
----Current file name is /mysqldata/mysql/test/iuser.ibd
current block:00000000--Offset:00034--cnt bytes:04--data is:00000026
current block:00000001--Offset:00034--cnt bytes:04--data is:00000026
current block:00000002--Offset:00034--cnt bytes:04--data is:00000026
current block:00000003--Offset:00034--cnt bytes:04--data is:00000026
current block:00000004--Offset:00034--cnt bytes:04--data is:00000000
current block:00000005--Offset:00034--cnt bytes:04--data is:00000000
4：查看fsp header 中的space id
由于fsp header 只存在第一个块中38-150，而space id 又存在与38-41 中，因此需要查看第一个块中偏移量为38 开始的后4 个字节内容根据脚本可以查看到具体的为00000027(10 进制为39)
[root@localhost soft]# ./bcview /mysqldata/mysql/test/iuser.ibd 16 38 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.093750 Mb
----Current use set blockszie is 16 Kb
----Current file name is /mysqldata/mysql/test/iuser.ibd
current block:00000000--Offset:00038--cnt bytes:04--data is:00000027
current block:00000001--Offset:00038--cnt bytes:04--data is:00000000
current block:00000002--Offset:00038--cnt bytes:04--data is:ffffffff
current block:00000003--Offset:00038--cnt bytes:04--data is:000200d9
current block:00000004--Offset:00038--cnt bytes:04--data is:00000000
current block:00000005--Offset:00038--cnt bytes:04--data is:00000000
5：修改fsb header 中的space id
[root@localhost soft]# ./bctool /mysqldata/mysql/test/iuser.ibd 0 38 00000026 ****************************************************************** 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) ****************************************************************** 0-0-0-26
再检查确认是否修改正确
[root@localhost soft]# ./bcview /mysqldata/mysql/test/iuser.ibd 16 38 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.093750 Mb
----Current use set blockszie is 16 Kb
----Current file name is /mysqldata/mysql/test/iuser.ibd
current block:00000000--Offset:00038--cnt bytes:04--data is:00000026
current block:00000001--Offset:00038--cnt bytes:04--data is:00000000
current block:00000002--Offset:00038--cnt bytes:04--data is:ffffffff
current block:00000003--Offset:00038--cnt bytes:04--data is:000200d9
current block:00000004--Offset:00038--cnt bytes:04--data is:00000000
current block:00000005--Offset:00038--cnt bytes:04--data is:00000000
6：启动恢复
[root@localhost soft]# service mysqld start Starting MySQL.. SUCCESS! 2024-03-11T08:11:11.170599Z 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-11T08:11:11.170822Z 0 [Note] /mysqlsoft/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 14764 ... 2024-03-11T08:11:11.181460Z 0 [Note] InnoDB: PUNCH HOLE support available 2024-03-11T08:11:11.181548Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2024-03-11T08:11:11.181574Z 0 [Note] InnoDB: Uses event mutexes 2024-03-11T08:11:11.181591Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2024-03-11T08:11:11.181622Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2024-03-11T08:11:11.182346Z 0 [Note] InnoDB: Number of pools: 1 2024-03-11T08:11:11.182638Z 0 [Note] InnoDB: Using CPU crc32 instructions 2024-03-11T08:11:11.187180Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2024-03-11T08:11:11.204123Z 0 [Note] InnoDB: Completed initialization of buffer pool 2024-03-11T08:11:11.209837Z 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-11T08:11:11.251385Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2024-03-11T08:11:11.471672Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2024-03-11T08:11:11.471768Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2024-03-11T08:11:11.472040Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2024-03-11T08:11:11.570867Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2024-03-11T08:11:11.572707Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2024-03-11T08:11:11.572759Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2024-03-11T08:11:11.574109Z 0 [Note] InnoDB: Waiting for purge to start 2024-03-11T08:11:11.624691Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2950883 2024-03-11T08:11:11.626078Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool 2024-03-11T08:11:11.626717Z 0 [Note] Plugin 'FEDERATED' is disabled. 2024-03-11T08:11:11.649744Z 0 [Note] Recovering after a crash using /mysqldata/mysql/binlog 2024-03-11T08:11:11.649798Z 0 [Note] Starting crash recovery... 2024-03-11T08:11:11.649921Z 0 [Note] Crash recovery finished. 2024-03-11T08:11:11.736054Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2024-03-11T08:11:11.737385Z 0 [Warning] CA certificate ca.pem is self signed. 2024-03-11T08:11:11.740836Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2024-03-11T08:11:11.743232Z 0 [Note] IPv6 is available. 2024-03-11T08:11:11.743360Z 0 [Note] - '::' resolves to '::'; 2024-03-11T08:11:11.743427Z 0 [Note] Server socket created on IP: '::'. 2024-03-11T08:11:11.790698Z 0 [Note] Failed to start slave threads for channel '' 2024-03-11T08:11:11.811031Z 0 [Note] Event Scheduler: Loaded 0 events 2024-03-11T08:11:11.811400Z 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-11T08:11:11.818280Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240311 16:11:11