search
HomeDatabaseMysql TutorialHow 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!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
What are some tools you can use to monitor MySQL performance?What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

How does MySQL handle character sets and collations?How does MySQL handle character sets and collations?Apr 23, 2025 am 12:19 AM

MySQLmanagescharactersetsandcollationsbyusingUTF-8asthedefault,allowingconfigurationatdatabase,table,andcolumnlevels,andrequiringcarefulalignmenttoavoidmismatches.1)Setdefaultcharactersetandcollationforadatabase.2)Configurecharactersetandcollationfor

What are triggers in MySQL?What are triggers in MySQL?Apr 23, 2025 am 12:11 AM

A MySQL trigger is an automatically executed stored procedure associated with a table that is used to perform a series of operations when a specific data operation is performed. 1) Trigger definition and function: used for data verification, logging, etc. 2) Working principle: It is divided into BEFORE and AFTER, and supports row-level triggering. 3) Example of use: Can be used to record salary changes or update inventory. 4) Debugging skills: Use SHOWTRIGGERS and SHOWCREATETRIGGER commands. 5) Performance optimization: Avoid complex operations, use indexes, and manage transactions.

How do you create and manage user accounts in MySQL?How do you create and manage user accounts in MySQL?Apr 22, 2025 pm 06:05 PM

The steps to create and manage user accounts in MySQL are as follows: 1. Create a user: Use CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password'; 2. Assign permissions: Use GRANTSELECT, INSERT, UPDATEONmydatabase.TO'newuser'@'localhost'; 3. Fix permission error: Use REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost'; then reassign permissions; 4. Optimization permissions: Use SHOWGRA

How does MySQL differ from Oracle?How does MySQL differ from Oracle?Apr 22, 2025 pm 05:57 PM

MySQL is suitable for rapid development and small and medium-sized applications, while Oracle is suitable for large enterprises and high availability needs. 1) MySQL is open source and easy to use, suitable for web applications and small and medium-sized enterprises. 2) Oracle is powerful and suitable for large enterprises and government agencies. 3) MySQL supports a variety of storage engines, and Oracle provides rich enterprise-level functions.

What are the disadvantages of using MySQL compared to other relational databases?What are the disadvantages of using MySQL compared to other relational databases?Apr 22, 2025 pm 05:49 PM

The disadvantages of MySQL compared to other relational databases include: 1. Performance issues: You may encounter bottlenecks when processing large-scale data, and PostgreSQL performs better in complex queries and big data processing. 2. Scalability: The horizontal scaling ability is not as good as Google Spanner and Amazon Aurora. 3. Functional limitations: Not as good as PostgreSQL and Oracle in advanced functions, some functions require more custom code and maintenance.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.