Please read this article thoroughly before proceeding with the solution. Be sure to make server snapshot backups to avoid secondary failures.
Reason for appearing:
Under normal circumstances, when the database executes the query statement "Select * From Table_Name", it will normally return complete data to the user. When the query is executed, or the background operation of the InnoDB storage engine exits unexpectedly or is interrupted, it will cause InnoDB to roll back forward. Failed to restore data, the main error is displayed as follows:
Error 1：
2021-12-2306:50:44.061994Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=448] log sequence number 453794788325 is in the future! Current system log sequence number 453794473178.
2021-12-2306:50:44.062002Z 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 ,
2021-12-2306:50:44.062039Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=452] log sequence number 453794788552 is in the future! Current system log sequence number 453794473178.
2021-12-2306:50:44.062042Z 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 ,
2021-12-2306:50:44.062071Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=457] log sequence number 453794788763 is in the future! Current system log sequence number 453794473178.
2021-12-23T06:50:44.062073Z 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
2021-12-23T06:50:44.062159Z 0 [ERROR] InnoDB: Page (page id: space=0, page number=487] log sequence number 453794789850 is in the future! Current system log sequence number 453794473178.
2021-12-2306:50:44.062161Z 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 ,
2021-12-2306:50:44.062202Z 0 [ERROR] InnoDB: Page [page id: space=0, page number= 501] log sequence number 453794790288 is in the future! Current system log sequence number 453794473178.
2021-12-2306:50:44.062204Z 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 ,
2021-12-23T06:50:44.062223Z 0 [ERROR] InnoDB: Page [page id: space=0, page number= 514] log sequence number 453795083144 is in the future! Current system log sequence number 453794473178.
2021-12-2306:50:44.062225Z 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
2021-12-2306:50:44.062234Z 0 [ERROR] InnoDB: Page (page id: space=0, page number=515) log sequence number 453795104290 is in the future! Current system log sequence number 453794473178.
2021-12-23T06:50:44.062236Z 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 ,
2021-12-23T06:50:44.062245Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page (page id: space=0, page number=519]. You may have to recover from a backup.
2021-12-2306:50:44.062247Z 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex fd121f7200000207000000000000000000000069a85032a0002000000000000000000000000000200383f700000206002c0000020800200751c821482b20000008ec93ae02f0000020200382461323833346164
Error 2:
2022-01-02 22:05:36 7f114f118720 InnoDB: Error: page 251 log sequence number 135630634
InnoDB: is in the future! Current system log sequence number 43744780.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: for more information.
2022-01-02 22:05:36 7f114f118720 InnoDB: Error: page 530 log sequence number 43789292
InnoDB: is in the future! Current system log sequence number 43744780.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: for more information.
2022-01-02 22:05:36 7f114f118720 InnoDB: Error: page 252 log sequence number 151634539
InnoDB: is in the future! Current system log sequence number 43744780.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: for more information.
2022-01-02 22:05:36 7f114f118720 InnoDB: Error: page 517 log sequence number 151634539
InnoDB: is in the future! Current system log sequence number 43744780.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: for more information.
The solution to this error is as follows:
1, First determine whether the error is consistent with the above error message, and then close the web service (Nginx, Apache, Openlitespeed).
2, Make a backup of the MySQL data storage directory. The backup is just in case. If there is a snapshot, try to make a disk snapshot.
The default database storage path in the panel is：
/www/server/data
Backup directory command:
\cp -rp /www/server/data \www\backup\data_2022
3, Add to the MySQL configuration file
The default MySQL configuration file for the panel is: /etc/my.cnf
innodb_force_recovery = 1
Restart the database after setting
innodb_force_recovery is a non-zero integer by default. There are a total of 6 parameter values 1--6. Refer to the official manual for parameter interpretation:
mysql 5.6
https://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
mysql 5.7
https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
mysql 8.0
https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
4,The above 6 parameter values, the latter contains the previous functions, such as parameter 3, which includes all the functions of parameter 1 and parameter 2. There is always one parameter here that can start the database. Note: If it cannot be started, please Clear the binary log (do not execute this command without backup):
rm -f /www/server/data/ib_*
rm -f /www/server/data/mysql-bin*
5, After restarting the database, back up the database as follows:
In the panel "Cron" set the backup database:
After setting, remember to click the "Execute" button, and pay attention to check whether the "Log" backup is successful. If the database is relatively large, the backup will take some time.
You can also back up by clicking the Backup button on the "databases" management interface.
6, When the backup is completed and there is no error, stop the database service, back up the database name, user name and corresponding password to Notepad and save it, and then uninstall the current database
7, After reinstalling MySQL, create a new database, use the database name, user name and corresponding password saved to Notepad, and pay attention to the character set used by the database
8, After restoring the latest backup database successfully, restart the web service and observe whether the website is normal.
Conclusion: The reason why the server is called a server is its strong stability and continuous service capabilities. Unless necessary, do not restart the server, usually do more data backup, and don't panic when there is a problem!
If you have trouble using the panel, you can post on the forum for help.
FAQ:
Why shut down web services (Nginx, Apache, Openlitespeed)?
A: At this time, MySQL has been unable to provide services normally. If the web service is always open, external access will keep requesting the database, and a large number of error logs will be generated. If the disk capacity is small, the disk space will soon be insufficient.
Why do snapshots or backups?
A: When processing data, MySQL may not be able to start due to special circumstances. If there is no backup or snapshot, the existing "data" will be damaged, causing secondary damage to the database.
Why copy the database name, user name and corresponding password?
A: To save time, creating a database on the panel will regenerate a random password. Currently, if the database information in your website is inconsistent with the information in the running database, you cannot connect. When creating a database, use the previous database name. , username and password to create a database, no need to modify the information in the website configuration file, so that it can be used directly after starting the web service
What should I do if the database still fails to start after I have tried everything?
Answer: MySQL's official solutions can't solve the current problem. It is recommended to find a professional data recovery company.
