How to Recover a Corrupted InnoDB Database in MariaDB Using innodb_force

Introduction

InnoDB is the default storage engine used by MariaDB, and it provides features such as transactions and referential integrity. However, if the database becomes corrupt, it can be difficult to recover. InnoDB has a setting called innodb_force_recovery that can help recover a corrupted database.

In this blog post, we'll go over what innodb_force_recovery is, how it works, and how to use it. We'll also cover the potential risks associated with using this feature.

What is `innodb_force_recovery`?

innodb_force_recovery is a setting in MariaDB that allows you to force InnoDB to start up and recover from a corrupted database. It's a useful tool for fixing a corrupted database that cannot be restored using a regular backup or recovery mode.

When innodb_force_recovery is set to a non-zero value, InnoDB will start up in "forced recovery" mode. In this mode, InnoDB skips certain operations that could potentially cause problems during the recovery process.

The innodb_force_recovery setting has several levels, ranging from 1 to 6, with each level increasing in severity. Here's a brief overview of each level:

Level 1: This is the least severe level, and it's usually sufficient for most cases. It disables some background processes to help InnoDB start up.
Level 2: This level is more severe than level 1, and it also disables additional background processes.
Level 3: This level is even more severe than level 2, and it disables even more background processes. It also skips the flushing of the redo log, which can result in data loss.
Level 4: This level disables everything except for reading transactions and rebuilding the indexes. It's the last level where data can be recovered without losing anything.
Level 5: This level is similar to level 4, but it skips some checks, which can result in data loss.
Level 6: This is the most severe level, and it's only used as a last resort. It wipes out the ibdata1 and ib_logfiles, which can result in complete data loss.

How does `innodb_force_recovery` work?

When innodb_force_recovery is set to a non-zero value, InnoDB will start up in forced recovery mode. In this mode, InnoDB skips some background processes to help it start up and recover the database. These processes include the following:

Purging the binary log files
Writing the dirty pages to disk
Flushing the buffer pool
Checking the integrity of the page headers

The specific processes that are skipped depend on the value of innodb_force_recovery.

Once InnoDB has started up in forced recovery mode, it will attempt to recover the database. This involves scanning the redo log files and applying any transactions that were not yet committed at the time of the crash. InnoDB will also rebuild the indexes, which can take some time depending on the size of the database.

How to use `innodb_force_recovery`

Before using innodb_force_recovery, it's important to make a backup of your database. This is because using this feature can result in data loss.

To use innodb_force_recovery, follow these steps:

Stop the MariaDB service: First, stop the MariaDB service using the following command:
```
sudo systemctl stop mariadb
```
Edit the my.cnf or 50-server.cnf file based on your Mariadb version (You can find my.cnf or 50-server.cnf in either /etc/mysql or /etc/mysql/mariadb.conf location): Add the following line to the [mysqld] section of the file:
```
innodb_force_recovery=<level> 
```
Replace <level> with the level you want to use. Remember that a higher level can result in more data loss.
Save the my.cnf file: Save the changes to the my.cnf file and close it.
Start the MariaDB service: Start the MariaDB service using the following command.
```
sudo systemctl start mariadb
```
Check the error log: Check the error log to see if InnoDB started up successfully. The error log is usually located in the /var/log/mysql directory. Otherwise, you can use `journalctl -xe` command to check if it's showing any issues.
Perform a backup: Once InnoDB has started up successfully, perform a backup of your database.
Remove the innodb_force_recovery setting: After the backup is complete, remove the innodb_force_recovery setting from the my.cnf file.
Restart the MariaDB service: Restart the MariaDB service to remove the innodb_force_recovery setting.
```
sudo systemctl restart mariadb
```

Risks associated with using `innodb_force_recovery`

Using innodb_force_recovery can result in data loss, so it should only be used as a last resort. The higher the level, the more data loss is possible.

In addition to data loss, using innodb_force_recovery can also cause InnoDB to behave unpredictably. For example, queries may take longer to execute, or the database may crash again.

It's important to note that innodb_force_recovery is not a substitute for proper backup and recovery procedures. It should only be used in cases where all other options have been exhausted.

Conclusion

innodb_force_recovery is a useful tool for recovering a corrupted InnoDB database in MariaDB. However, it should only be used as a last resort, as it can result in data loss and unpredictable behavior. Before using this feature, be sure to make a backup of your database and understand the risks involved.

Search This Blog