XAMPP中MySQL数据库崩溃无法启动的探索及批量导出

本文主要介绍在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:开始后面删掉

 

参考资料

https://stackoverflow.com/questions/18022809/xampp-mysql-shutdown-unexpectedly/

https://stackoverflow.com/questions/9472804/batch-file-for-mysqldump-to-backup-each-database-into-a-separate-file

 

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注