How to solve Mysql8 power failure crash

1. Overview

The stand-alone Mysql8 database server suddenly lost power during operation, causing the database to crash and unable to restart.

2. Find the reason

View mysql running error log: WIN-SOTMI68HRV6.err (in the Data directory)

InnoDB: End of page dump
InnoDB: Page may be a system page
2023-02-01T09:31:02.878917Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-02-01T09:31:02.882631Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.23) starting as process 3496
2023-02-01T09:31:02.923391Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-01T09:31:05.964384Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'innodb_system' Page [page id: space=0, page number=5] log sequence number 3275776865 is in the future! Current system log sequence number 3197057036.
2023-02-01T09:31:05.966225Z 1 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2023-02-01T09:31:05.98

InnoDB: End of page dump
InnoDB: Page may be a system page
2023-02-01T11:03:39.767939Z 1 [ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=4294967278, page number=101]. You may have to recover from a backup.
len 16384; hex

Obviously[ ERROR], the disk file cannot be found. From the above log, we can know that an error occurred in the database and cannot be restarted.

InnoDB uses checksums to protect data and stores them with pages. When InnoDB reads from disk, it calculates a checksum for each page and then compares it with the checksum loaded on disk. If the values ​​are different, something might really have gone wrong. To prevent further logical or physical damage, the MySQL server will shut down InnoDB.

3. Solution

1. How to find out the cause of the damage

There is no universal solution. Some common hardware problems include physical disk or memory failure, corrupted drives/controllers, and errors in the operating system kernel. Here are some suggestions:

On Linux platforms, sometimes resetting the page cache can solve this problem:

echo 2 > /proc/sys/vm/drop_caches

Check the system log for possible hardware failures.
If InnoDB crashes every time on a specific page, most typically a physical disk failure: run detailed disk diagnostics for your OS/hardware.
If the crash is random and not repeated for the same query, it may be a RAM fault: run detailed RAM diagnostics.
When MySQL is shut down, it is helpful to check the InnoDB file with the innochecksum tool.

作者这里故障原因是断电导致数据出现问题，只能重装Mysql。

2.如何从损坏中恢复

最重要的是执行详细的硬件诊断，以消除问题扩散的机会。如果操作系统I / O缓存是磁盘读损坏的原因，重置缓存或重新启动操作系统应有助于消除当前的问题，数据库可能会重新运作。
有时唯一的解决办法是在有效恢复模式下备份数据。

笔者后面尝试强制启动，可以启动Mysql，但是数据库只能读不能写，通过日志又找不到损坏的数据表，无奈，只能先备份数据库，然后重装Mysql。

修改数据库，一直报错：

running in read_only mode 1836

将mysql改为强制启动：

在my.ini中【mysqld】节点下加上

innodb_force_recovery=0

然后对数据库进行备份。
备份方式：

一、数据库备份

第一种：（cmd窗口使用）
在命令提示符用mysqldump命令行备份数据库。
命令格式
mysqldump -u用户名 -p 数据库名 > 保存名.sql
范例：

mysqldump -uroot -p dataname > d:\data.sql

（导出数据库dataname到data.sql文件）
提示输入密码时，输入该数据库用户名的密码。

第二种：指定导出备份编码

mysqldump -u root -p密码 --default-character-set=数据编码 数据库名称> data.sql

案例：

mysqldump -u root -p123456 --default-character-set=utf8 discuss_chi>d:/data.sql

mySQL数据库在windows环境下备份与恢复：

二，恢复数据库，一共二种方式。

第一种；定义还原编码类型（cmd窗使用）
定义编码导入:

mysql -u root -p --default-character-set=utf8 -f dataname<d:/dis.sql

如果乱码使用二进导入

mysql -u root -p --default-character-set=binary -f dataname<d:/dis.sql

第二种：
source 命令（mysql控制台窗口使用）
进入mysql数据库控制台，

如在运行中输入:mysql -u root -p

mysql>use databasename;

1、确定数据库默认编码，比如编码为gbk,将读入途径编码同样设为gbk，命令为：
set names gbk;（导入数据出现乱码的时候用平常不用）
2、然后使用source命令，后面参数为脚本文件（如这里用到的.sql）

mysql>source d:\data.sql;

备份后，重装Mysql，恢复数据库。

The above is the detailed content of How to solve Mysql8 power failure crash. For more information, please follow other related articles on the PHP Chinese website!