How to fix abnormal startup of mysql5.7.21

A colleague reported that a MySQL instance could not be started due to a power outage. Using innodb_force_recovery=6 also didn't work, so I went to check.

Troubleshooting process:

In the earliest startup information, there is no error message, only a line of [ERROR] Aborting prompt, as follows:

Then my colleague used innodb_force_recovery=6, and the following error message appeared, but it still could not be started successfully. At this time, I decided to find out what was going on.

Filter the startup log, grep ERROR /data/mysql/3306/mysql_run.err

As you can see, all the errors are mainly as follows:

Most of the reasons why MySQL cannot be started are due to system database issues, and this one seems to be no exception.

I tried to log in to the system with --skip-grant-tables, and it worked.

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --user=mysql --skip-grant-tables &

Next, check innodb as soon as possible and execute:

innochecksum ibdata1

Then found no output.

Then execute mysqlcheck, and it indeed repairs some errors in the tables under the mysql library. After that, restart the system in the normal way and MySQL returns to normal.

mysqlcheck -u root -p --repair -A

Summarize:

1. MySQL is not that fragile. There is no need to restore it through backup and recovery when it is damaged, which is time-consuming and laborious.

2. During the startup process, you can set --skip-grant-tables or innodb_force_recovery (this parameter needs to modify the cnf file) to let MySQL skip some checks and enable the instance to start successfully;

3. After startup, you can perform data backup or export data, and try to repair the instance;

4. This problem occurs in this instance because of improper setting of real-time saving parameters.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.