Fix MySQL/MariaDB corrupt database

We will fix/restore a MySQL/MariaDB corrupt database. Before going to the details, we will make sure to take backup of the data files.

So the other day I got a call from my client, that he is seeing “Internal server error” when he access the app. My instant reaction was, what happened? He told me there was a power failure during a thunderstorm. So long story short, a sudden power failure while the database was busy writing crashed the server and caused a corrupted database. Once the power was back, the database server never started.

You may have your own reasons, but the conclusion is to fix a corrupt database on Ubuntu 20.04 server. Let’s get started.

I assume the database server when trying to start does not start at all and systemctl status mariadb shows failed messages.

Note: Words highlighted bold in the code snippets are to be changed.

We will backup /var/lib/mysql to make sure we have backup of the data.

cp -r /var/lib/mysql /home/USER/backup

Next important part is to start the database in recovery mode, in Ubuntu 20.04 the configuration is in /etc/mysql/mariadb.conf.d/. On different systems you can use find command to locate the file for you find /etc/ -name "my.cnf".

Under Arch/Manjaro the same cnf file is /etc/my.cnf.d/server.cnf

vim /etc/mysql/mariadb.conf.d/50-server.cnf

And the following line to it:

innodb_force_recovery = 3

Check out the different recovery modes here.

Restart the database server:

systemctl start mariadb

systemctl status mariadb

Now we will have to find out which database has corrupted data. If you have one or two databases, it will be easy though. The following command check the database tables for corruption.

mysqlcheck -u USER -p DATABASE

Enter password when asked. In most cases you will see OK for the database tables, if you see any table which is corrupted, there you have it.

Backup and drop database

Next we will backup our database.

mysqldump --lock-tables -u USER -p DATABASE > /home/USER/backup/db-backup.sql

Login to the database and drop the database. Stop the database server afterwards.

drop database DATABASE;

systemctl stop mariadb

Remove the data files and comment out innodb_force_recovery = 3 in 50-server.cnf.

rm /var/lib/mysql/ib*

Time to restart database server. If everything goes well, database server will restart normally this time.

systemctl start mariadb

Restore database

Last part is to restore our database. Create database 1st by logging in to database server.

create database DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

mysql -u USER -p DATABASE < /home/USER/backup/db-backup.sql

Check your database now.

mysqlcheck -u USER -p DATABASE

Hope this article was useful and helpful in some way to fix your problem.