Whether you are running MySQL on a Linux or Windows machine, forcing InnoDB recovery to restart the server might fail. This may happen due to any of the following reasons:
- Trying to run innodb_force_recovery set to the highest possible value ‘6’ before forcing recovery with values’ 0′, ‘1’, ‘2’, ‘3’, ‘4’, or ‘5’. It is a really bad move! Starting recovery with level 6 must be your last resort to restart the server, as it can make the MySQL database inaccessible. A value greater than 4 is dangerous, as it can corrupt data files.
- You forgot to reboot the system before innodb_force_recovery.
Before We Proceed
It is important to troubleshoot problems starting your MySQL Server. For this, open the MySQL error log and check the logs to see why the server fails to start. You can find the error log on a Windows PC by browsing the location- C:\Program Files\MySQL\MySQL Server nnn\data.
Here replace nnn with the version of MySQL Server you are using. For instance, if you’re using MySQL Server version 8.0, then locate the error log in – C:\Program Files\MySQL\MySQL Server 8.0\data.
For a Unix or Linux distribution, you can find the error log by browsing:
/usr/local/var
See this MySQL post for additional suggestions on troubleshooting problems starting the server.
If the problem persists, try to work around the issue as discussed in the next section.
How to Fix the ‘MySQL innodb_force_recovery is Not Working Properly’ Issue?
Following are some workarounds you can use to troubleshoot the ‘InnoDB force recovery not working’ problem:
Workaround 1 – Run InnoDB Force Recovery
If you have directly used innodb_force_recovery=6, try running it with a value 3 and increase it to values 4, 5 if InnoDB fails to start. The steps are as follows:
- Use the below commands to start MySQL server:
innodb_force_recovery=3
innodb_purge_threads=0
- Dump the database with mysqldump tool.
- Create new empty InnoDB table space and reload the dump.
Note: If MySQL doesn’t start, use MySQL Server startup script mysqld_safe. It helps in running mysqld server on a Unix machine.
mysql> #mysqld_safe --innodb_force_recovery=4
If this doesn’t work, skip to the next workaround.
Workaround 2 – Restore MySQL Database from Backup
If you cannot start the server, you can access data by restoring the MySQL database from a valid backup (mysqldump) file. You can find the detailed steps on how to restore a MySQL database from mysqldump file, click here.
If you don’t have a backup or it fails to work for you, the only solution is to use a MySQL database repair tool. Stellar Repair for MySQL helps repair severely corrupted InnoDB tables and recover their components.
The software also helps fix corrupted MyISAM tables. Thus, using the MySQL repair tool helps save time in troubleshooting tables on InnoDB and MyISAM storage engines via a single interface.
See the software working by watching this video:
End Note
You will have a problem starting MySQL Server if MySQL innodb_force_recovery is not working properly. Before resolving this issue, check the reasons discussed in this blog to understand what you may be doing wrong.
Try the suggestions to fix problems starting the server. If nothing helps, use the above-discussed workarounds. If innodb_force_recovery won’t work and you don’t have a backup, Stellar Repair for MySQL software can help repair the database and restore it to its original form.
