XAMPP启动遇InnoDB操作系统错误3，已尝试部分操作仍未解决

此前服务器运行正常，今早启动XAMPP时MySQL无法启动，错误日志如下：

2022-12-10  9:55:29 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 
2022-12-10  9:55:29 0 [Note] InnoDB: Uses event mutexes 
2022-12-10  9:55:29 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2022-12-10  9:55:29 0 [Note] InnoDB: Number of pools: 1 
2022-12-10  9:55:29 0 [Note] InnoDB: Using SSE2 crc32 instructions 
2022-12-10  9:55:29 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M 
2022-12-10  9:55:29 0 [Note] InnoDB: Completed initialization of buffer pool 
2022-12-10  9:55:29 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 
2022-12-10  9:55:29 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 
2022-12-10  9:55:29 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 
2022-12-10  9:55:29 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\nbc_db2022@002e1\ja_usermeta.ibd' OS error: 203 
2022-12-10  9:55:29 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 
2022-12-10  9:55:29 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 
2022-12-10  9:55:29 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``nbc_db2022.1`.`ja_usermeta\`\`. 
Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2022-12-10  9:55:29 0 [Warning] InnoDB: Ignoring tablespace for `nbc_db2022.1`.`ja_usermeta` because it could not be opened.
2022-12-10  9:55:29 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2022-12-10  9:55:29 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2022-12-10  9:55:29 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2022-12-10  9:55:29 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\\nbc_db2022@002e1\\wp_commentmeta.ibd' OS error: 203
2022-12-10  9:55:29 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2022-12-10  9:55:29 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/

已尝试操作：

• 切换PHPMyAdmin与MySQL端口
• 删除aria_log文件
  系统环境：Windows 11 Version 22H2

解决方案 #

1. 修正数据库目录名称 #

日志中nbc_db2022@002e1是nbc_db2022.1的URL编码形式（.被转义为@002e），MySQL找不到对应目录：

• 打开XAMPP控制面板，点击MySQL的「Config」→选择「my.ini」，找到datadir配置项（通常为C:\xampp\mysql\data），进入该目录
• 检查是否存在nbc_db2022.1目录：
  • 如果存在，将其重命名为nbc_db2022@002e1
  • 如果不存在nbc_db2022@002e1目录，手动创建该目录，并检查是否有备份的ja_usermeta.ibd和wp_commentmeta.ibd文件，复制到该目录

2. 修复无效的InnoDB表项（无备份时） #

如果缺少ibd文件且无备份，可强制移除无效表：

• 停止MySQL服务
• 备份datadir下的ibdata1文件（重要，防止数据丢失）
• 编辑my.ini，添加配置：innodb_force_recovery=1（可逐步尝试1-6，优先用低数值，避免损坏数据）
• 启动MySQL服务，登录后执行SQL命令：

  DROP TABLE `nbc_db2022.1`.`ja_usermeta`;
  DROP TABLE `nbc_db2022.1`.`wp_commentmeta`;
  

• 停止MySQL服务，移除innodb_force_recovery配置，若需要恢复数据，将备份的ibdata1替换回去，重新导入表结构和数据

3. 检查目录权限 #

确保MySQL服务有访问数据目录的权限：

• 右键数据目录（如C:\xampp\mysql\data）→「属性」→「安全」
• 给当前用户或NETWORK SERVICE账号添加「完全控制」权限，应用后重启MySQL服务

4. 恢复备份 #

如果有最近的数据库备份，直接停止MySQL，替换datadir下对应数据库目录，或通过PHPMyAdmin导入备份文件

内容的提问来源于stack exchange，提问作者thenoobitguy