本文主要介绍在XAMPP中数据库突然出现崩溃后无法启动，本人对处理方法的探索。并提供一个windows下批量导出数据库的脚本。
尝试处理流程
1. 先备份/xampp/mysql/data文件夹，如备份为databackup
2. 检查mysql错误日志。看其中的提示，如果没有error类报错，尝试A：
A1. 把原data文件夹删除，新建一个空白的data文件夹
A2. 复制/xampp/mysql/backup中所有文件（包括文件和文件夹）复制到data文件夹中
A3. 将databackup里面的所有文件夹和idatadb1文件复制到新的data文件夹中
A4. 尝试启动mysql。
3. 若有Error报错log sequence number xxx is in the future!，则尝试B：
B1. 打开my.ini在[mysqld]下面添加innodb_force_recovery=1，保存后，尝试启动mysql
B2. 如果无效，则逐个尝试2 ~ 6 ，从2开始，直到启动成功为止。
B3. 启动成功后，导出所有数据库删除原有数据库。
4. 如果1 ~ 6 全部启动失败。尝试：
C1. 从A1做到A2
C2. 将databackup里面你需要的数据库那一个文件夹和idatadb1文件复制到新的data文件夹中
C3. 尝试启动mysql，如果启动失败，更换另外一个数据库文件夹。
C4. 此步骤可排查出是哪个数据库出现问题。
探索过程
我是一个版本控，从来有新版本不用旧版本。2020年3月左右，我更新了xampp的版本到7.4.2（内置的MariaDB 10.4.11）。但是使用一段时间以后，我偶尔会发现一个问题。当进入PhpMyAdmin后，总有一个报错：
Warning in .\libraries\classes\Dbi\DbiMysqli.php#199 mysqli::query(): (HY000/1034): Index for table 'global_priv' is corrupt; try to repair it ...... ......
这个错误我简单看了一下，他说是一个table需要修复，但是这个table名字明显不是我起的，所以我估计可能是phpmyadmin的某个表出现了问题。我自认为无大碍，因为其它表的处理没有问题。
数日以后，早上打开电脑，点击启动XAMPP里面的MySQL，发现启动失败，报错：
12:52:58 [mysql] Error: MySQL shutdown unexpectedly. 12:52:58 [mysql] This may be due to a blocked port, missing dependencies, 12:52:58 [mysql] improper privileges, a crash, or a shutdown by another method. 12:52:58 [mysql] Press the Logs button to view error logs and check 12:52:58 [mysql] the Windows Event Viewer for more clues 12:52:58 [mysql] If you need more help, copy and post this 12:52:58 [mysql] entire log window on the forums
首先我分析，肯定不是端口问题，然后果断看一下日志。日志里面竟然没有任何Error，只有几个Note：
InnoDB: using atomic writes. 2020-05-30 12:54:25 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2020-05-30 12:54:25 0 [Note] InnoDB: Uses event mutexes 2020-05-30 12:54:25 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-05-30 12:54:25 0 [Note] InnoDB: Number of pools: 1 2020-05-30 12:54:25 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-05-30 12:54:25 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M 2020-05-30 12:54:25 0 [Note] InnoDB: Completed initialization of buffer pool 2020-05-30 12:54:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=300288
我想，几个note竟然能导致无法启动？那是不是小问题而已？
我找到xampp目录下的mysql，打开data文件夹，看到里面一堆乱七八糟的文件：
aria_log.00000001 │ aria_log_control │ ibdata1 │ ib_buffer_pool │ ib_logfile0 │ ib_logfile1 │ master-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.info │ master-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.info │ master-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.info │ master-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.info │ master-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.info │ master-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.info │ master-2020@002d05@002d27@002013@003a41@003a16@002035@0020@005bwarning@005d@0020checking@0020table@003a@0020@0020@0020@0027@002e@005ccovid@005carctype@0027@000d.info │ master-version@003a@0020@002710@002e4@002e11@002dmariadb@0027@0020@0020socket@003a@0020@0027@0027@0020@0020port@003a@00203306@0020@0020mariadb@002eorg@0020binary@0020distribution@000d.info │ multi-master.info │ my.ini │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.000009 │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.index │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.000009 │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.index │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.000009 │ mysql-relay-bin-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.index │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.000002 │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.index │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.000002 │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.index │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.000002 │ mysql-relay-bin-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.index │ mysql-relay-bin-2020@002d05@002d27@002013@003a41@003a16@002035@0020@005bwarning@005d@0020checking@0020table@003a@0020@0020@0020@0027@002e@005ccovid@005carctype@0027@000d.000002 │ mysql-relay-bin-2020@002d05@002d27@002013@003a41@003a16@002035@0020@005bwarning@005d@0020checking@0020table@003a@0020@0020@0020@0027@002e@005ccovid@005carctype@0027@000d.index │ mysql-relay-bin-version@003a@0020@002710@002e4@002e11@002dmariadb@0027@0020@0020socket@003a@0020@0027@0027@0020@0020port@003a@00203306@0020@0020mariadb@002eorg@0020binary@0020distribution@000d.000010 │ mysql-relay-bin-version@003a@0020@002710@002e4@002e11@002dmariadb@0027@0020@0020socket@003a@0020@0027@0027@0020@0020port@003a@00203306@0020@0020mariadb@002eorg@0020binary@0020distribution@000d.000011 │ mysql-relay-bin-version@003a@0020@002710@002e4@002e11@002dmariadb@0027@0020@0020socket@003a@0020@0027@0027@0020@0020port@003a@00203306@0020@0020mariadb@002eorg@0020binary@0020distribution@000d.index │ mysql_error.log │ relay-log-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.info │ relay-log-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.info │ relay-log-2020@002d05@002d11@002017@003a48@003a32@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.info │ relay-log-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020added@0020new@0020master_info@0020@0027@0027@0020to@0020hash@0020table@000d.info │ relay-log-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020d@003a@005cxampp@005cmysql@005cbin@005cmysqld@002eexe@003a@0020ready@0020for@0020connections@002e@000d.info │ relay-log-2020@002d05@002d27@002013@003a29@003a27@00200@0020@005bnote@005d@0020reading@0020of@0020all@0020master_info@0020entries@0020succeeded@000d.info │ relay-log-2020@002d05@002d27@002013@003a41@003a16@002035@0020@005bwarning@005d@0020checking@0020table@003a@0020@0020@0020@0027@002e@005ccovid@005carctype@0027@000d.info │ relay-log-version@003a@0020@002710@002e4@002e11@002dmariadb@0027@0020@0020socket@003a@0020@0027@0027@0020@0020port@003a@00203306@0020@0020mariadb@002eorg@0020binary@0020distribution@000d.info
这些master和relay-bin、relay-log文件，我一看就生气了。果断先备份了data文件夹，然后把这些乱七八糟的删掉，然后尝试。不好使。
注：我特别感谢我备份文件夹这个动作，后面一系列小骚操作，差点要的我的命。
不好使，我想，删的不够充分呗。我知道这个idbdata1很重要，没到特殊情况不能删。所以我把其它文件都删了，只留这一个，再尝试，还是不好使。
一咬牙，idbdata也给删了。然后开启mysql，成功！
瞬间很开心，然后进入了phpmyadmin开始操作数据库，发现完犊子了。innodb引擎的表全部没了数据。我立刻认识到是删除idbdata文件惹的祸。幸亏，幸亏我备份了。
去stackoverflow搜索，发现了这篇解决方案：https://stackoverflow.com/questions/18022809/xampp-mysql-shutdown-unexpectedly/
但是，我很清晰的发现，第一个高票方案明显是在作死。经过多次尝试，我发现贴子内一个名为“Fabrizio Valencia”的网友给出的解决方案似乎靠谱。问题解决！
自已为万事大吉，二天后，我进phpmyadmin中时，又发现了那个错误提示。心中有一丢丢的颤抖。
第三天，早上打开电脑，又完蛋了。立刻尝试上一次的处理方案。结果刺激了，这次不好使了。然后我看了一下mysql的错误提示，竟然报错了。这回错误是：
2020-05-30 12:54:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=300288 2020-05-30 12:54:26 0 [Warning] InnoDB: Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 300288, less than the log sequence number in the first system tablespace file header, 269330047. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Page [page id: space=0, page number=7] log sequence number 268884267 is in the future! Current system log sequence number 300306. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 267899446 is in the future! Current system log sequence number 300306. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Page [page id: space=0, page number=4] log sequence number 269228397 is in the future! Current system log sequence number 300306. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Page [page id: space=0, page number=11] log sequence number 264885526 is in the future! Current system log sequence number 300306. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Page [page id: space=0, page number=355] log sequence number 171695314 is in the future! Current system log sequence number 300306. 2020-05-30 12:54:26 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
很长，我认识到这次和上次不是一个问题。但是我看到提示中有recovery的字样。而记忆里，之前的帖子中也有有关recovery的处理。再回头看。发现可以在my.ini中加入innodb_force_recovery = 1-6来尝试，我还真试了，结果不好使。此时我心中的颤抖增强了，我感觉要完。
到此为止，我已经丢失了2个表数据。庆幸的是，这两天正在写代码，基本没有动数据库，这两个表只有少量不太重要的数据。
innodb_force_recovery 从1到6全部尝试完成以后，我已经花了数个小时的时间解决这个问题。鉴于之前的数据很少，我最终决定，不再继续探索，而是将3日前的数据恢复。
这次事情给我两个教训：第一，数据库问题无小事，不要自以为是。第二，本地数据库也要定期备份，以备不时之需。
大概一个月以后，某天打开电脑，再次出现以上问题。在尝试完1-6以后，我觉得总崩溃的可能是mysql自带数据库，或者是phpmyadmin自带数据库，所以我将以上两个文件夹排除在外，没想到，成功了。所以我再次修订了本文。
数据库备份批处理
批处理实现将所有数据库导出，并备份到指定文件夹，以”年月日\数据库名-时分.sql”格式存储。支持一日多次备份。不冲突。
tasklist /nh|find /i "mysqld.exe" if ErrorLevel 1 ( start D:\xampp\mysql_start.bat rem msg %username% /time:10 "MySQL process not found" goto START ) else ( goto START ) :START mkdir F:\DB-BACKUP\%date:~0,4%%date:~5,2%%date:~8,2% D:\xampp\mysql\bin\mysql.exe -uroot -pdbpass -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do D:\xampp\mysql\bin\mysqldump %%D -uroot -pdbpass > "F:\DB-BACKUP\%date:~0,4%%date:~5,2%%date:~8,2%\%%D-%time:~0,2%%time:~3,2%.sql" F: cd F:\DB-BACKUP\%date:~0,4%%date:~5,2%%date:~8,2%\ "D:\Program Files\7-Zip\7z" a "%time:~0,2%%time:~3,2%".zip *.sql -r del *.sql :END
需要注意修改的地方：
F:\DB-BACKUP：数据库sql文件备份路径 D:\xampp\mysql\bin\ -uroot -ppassword D:\Program Files\7-Zip\7z 我最后用7z对备份的文件进行了压缩，如果不需要压缩，从F:开始后面删掉
其它
附：使用mysqlcheck检查并所有数据库表
mysqlcheck -uROOT -pPASSWORD --auto-repair --check --all-databases
参考资料
https://stackoverflow.com/questions/18022809/xampp-mysql-shutdown-unexpectedly/
