Windows MySQL 启动失败，数据文件损坏修复

参考：MySQL数据恢复官方文档

一、问题

windows平台，MySQL服务启动失败，启动后过几秒自己停止。cmd mysqld.exe启动无效，cmd命令行也没有任何日志输出。

二、解决

1、在MySQL data目录下，找到.err结尾的文件（MySQL启动错误日志），内容如下：

240226 13:39:03 [Note] Plugin ‘FEDERATED’ is disabled. 240226 13:39:03
InnoDB: The InnoDB memory heap is disabled 240226 13:39:03 InnoDB:
Mutexes and rw_locks use Windows interlocked functions 240226 13:39:03
InnoDB: Compressed tables use zlib 1.2.3 240226 13:39:03 InnoDB:
Initializing buffer pool, size = 47.0M 240226 13:39:03 InnoDB:
Completed initialization of buffer pool 240226 13:39:03 InnoDB:
highest supported file format is Barracuda. InnoDB: The log sequence
number in ibdata files does not match InnoDB: the log sequence number
in the ib_logfiles! 240226 13:39:03 InnoDB: Database was not shut
down normally! InnoDB: Starting crash recovery. InnoDB: Reading
tablespace information from the .ibd files… InnoDB: Restoring
possible half-written data pages from the doublewrite InnoDB:
buffer… 240226 13:39:03 InnoDB: Waiting for the background threads
to start 240226 13:39:04 InnoDB: 5.5.40 started; log sequence number
14639647781 240226 13:39:04 [Note] Server hostname (bind-address):
‘0.0.0.0’; port: 3307 240226 13:39:04 [Note] - ‘0.0.0.0’ resolves to
‘0.0.0.0’; 240226 13:39:04 [Note] Server socket created on IP:
‘0.0.0.0’. 240226 13:39:04 [Note] Event Scheduler: Loaded 0 events
240226 13:39:04 [Note] mysqld.exe: ready for connections. Version:
‘5.5.40’ socket: ‘’ port: 3307 MySQL Community Server (GPL) 240226
13:39:45 InnoDB: Assertion failure in thread 45844 in file fsp0fsp.c
line 2102 InnoDB: Failing assertion: inode 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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

This application has requested the Runtime to terminate it in an
unusual way. Please contact the application’s support team for more
information. 05:39:46 UTC - mysqld got exception 0x80000003 ; 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. We will try our best to scrape up some info
that will hopefully help diagnose the problem, but since we have
already crashed, something is definitely wrong and this may fail.

2、根据日志内容，你的MySQL服务器在启动时遇到了一些问题。具体来说，日志中提到了以下关键信息：

undefined.InnoDB: The log sequence number in ibdata files does not
match the log sequence number in the ib_logfiles!
：这表示InnoDB数据文件中的日志序列号与日志文件中的日志序列号不匹配，可能是由于数据文件损坏或不一致导致的。
undefined.InnoDB: Assertion failure in thread 45844 in file fsp0fsp.c
line 2102 ：这表示在InnoDB线程中发生了断言失败，可能是由于数据文件损坏或其他问题导致的。 undefined.This
application has requested the Runtime to terminate it in an unusual
way. ：这表示MySQL服务器由于异常情况而被终止。

3、根据这些信息，建议你采取以下步骤来尝试解决问题：

（1）备份数据 ：在尝试任何修复操作之前，请确保备份了重要的数据。
（2）尝试强制恢复
根据日志中提供的链接，可以尝试强制InnoDB恢复，但请谨慎操作，因为这可能会导致数据丢失或损坏。

强制恢复：

1. 停止 MySQL 服务器：使用适当的方式停止 MySQL 服务器，确保所有与数据库相关的进程都已终止。

2. 备份数据：在执行任何修复操作之前，请确保已经备份了数据库文件。

3. 启动 MySQL 服务器并进入恢复模式：通过以下方式启动 MySQL 服务器并进入恢复模式：

   • Windows：

     mysqld --innodb-force-recovery=1
     

   • Linux/MacOS：

     sudo mysqld --innodb-force-recovery=1
     

   注意：在 --innodb-force-recovery 参数中使用的值取决于需要的恢复级别，可以尝试从 1 开始逐步增加，直到找到适合的级别。有关不同级别的说明，请参阅官方文档。

4. 导出数据：在恢复模式下，尝试导出数据以便后续重新导入。

5. 修复表：对于出现问题的表，可以尝试修复：

   REPAIR TABLE table_name;
   

   请注意，这可能不适用于所有情况，特别是在强制恢复后。

6. 重启 MySQL 服务器：在完成修复和数据导出后，正常启动 MySQL 服务器并检查是否可以正常访问数据。

请注意，强制恢复可能无法解决所有问题，并且可能导致数据丢失或其他不可预测的结果。如果在强制恢复后仍然遇到问题，可能需要考虑其他恢复方法或专业数据库恢复服务。