[笔记]修正Xampp的MySQL无法启动

关机重开后，
启动 XAMPP 后发现 MySQL 无法启动...
从XAMPP的讯息看不出什么...

直接去xampp的资料夹执行mysql_start.bat
C:\xampp\mysql_start.bat
发现执行失败
接着去C:\xampp\mysql\data\mysql_error.log发现已下错误

(PS: 出现错误1的LOG一开始没想到要没保存下来，所以以下是中途的备份)

2022-02-27 16:21:02 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions2022-02-27 16:21:02 0 [Note] InnoDB: Uses event mutexes2022-02-27 16:21:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.112022-02-27 16:21:02 0 [Note] InnoDB: Number of pools: 12022-02-27 16:21:02 0 [Note] InnoDB: Using SSE2 crc32 instructions2022-02-27 16:21:02 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M2022-02-27 16:21:02 0 [Note] InnoDB: Completed initialization of buffer pool2022-02-27 16:21:02 0 [Note] InnoDB: 128 out of 128 rollback segments are active.2022-02-27 16:21:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables2022-02-27 16:21:02 0 [Note] InnoDB: Setting file '\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...2022-02-27 16:21:02 0 [Note] InnoDB: File '\xampp\mysql\data\ibtmp1' size is now 12 MB.2022-02-27 16:21:02 0 [Note] InnoDB: Waiting for purge to start2022-02-27 16:21:02 0 [Note] InnoDB: 10.4.21 started; log sequence number 47646; transaction id 82022-02-27 16:21:02 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool2022-02-27 16:21:02 0 [Note] Plugin 'FEEDBACK' is disabled.2022-02-27 16:21:02 0 [Note] InnoDB: Buffer pool(s) load completed at 220227 16:21:022022-02-27 16:21:02 0 [Note] Server socket created on IP: '::'.2022-02-27 16:21:02 0 [ERROR] mysqld: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed2022-02-27 16:21:02 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed2022-02-27 16:21:02 0 [ERROR] Aborting

以下是我的修补方式提供大家参考

错误1:
Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.

尝试过这篇文章没有成功，
改参考这篇进行修改

修改方式如下:

错误2:
[ERROR] mysqld: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed
[ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed

参考一些网路文章几乎都是要透过myisamchk修正，
但我却会出现 XXX is not a MyISAM-table 的错误讯息，
在bin资料夹发现aria_chk.exe
于是两只都拿来试试看。

不知为啥aria_chk网路上几乎没啥资料...
如果有更好的参数使用建议的话欢迎告知

执行以下两条指令
ps. 如果错误讯息不是.\mysql\db，自行修正成自己的路径

C:\xampp\mysql\bin\myisamchk -c -r C:\xampp\mysql\data\mysql\db*C:\xampp\mysql\bin\aria_chk -c -r -n C:\xampp\mysql\data\mysql\db*

接着发现有以下错误

myisamchk: error: 'C:\xampp\mysql\data\mysql\db.frm' is not a MyISAM-tablemyisamchk: error: 'C:\xampp\mysql\data\mysql\db.MAD' is not a MyISAM-tablemyisamchk: error: 'C:\xampp\mysql\data\mysql\db.MAI' is not a MyISAM-tablearia_chk: Got error 'Can't find file' when trying to use aria control file '.\aria_log_control'aria_chk: error: 'C:\xampp\mysql\data\mysql\db.frm' is not a Aria tablearia_chk: error: 'C:\xampp\mysql\data\mysql\db.MAD' is not a Aria tablearia_chk: Index is corruptedaria_chk: error: 'C:\xampp\mysql\data\mysql\db.MAI' doesn't have a correct index definition. You need to recreate it before you can do a repair

拜google后发现这篇文章建议直接用备份取代

因为不是我专案的资料表，
直接取代比较快
将C:\xampp\mysql\backup\mysql\db.* 複製到 C:\xampp\mysql\data\mysql\db.*直接取代
重启后就成功了

最后，
如果想要全部资料库的资料表都跑过一次，
提供以下批次档，
有需要的话自行调整路径

@echo offset data=C:\xampp\mysql\dataset bin=C:\xampp\mysql\bin%bin%\myisamchk -c -r %data%\*%bin%\aria_chk -r %data%\*for /d %%i in (./data/*) do (%bin%\myisamchk -c -r %data%\%%i\*%bin%\aria_chk -c -r -n %data%\%%i\*)