Repair corrupt MariaDB Database

MariaDB is a variation of MySQL database management system but with some enhanced features. It can perform queries and replication tasks quickly. Due to its high performance and ability to manage large-sized databases, many organizations choose MariaDB over MySQL. However, like other databases, MariaDB database is also prone to corruption. There are various reasons that can lead to corruption in MariaDB database. Let's take a look at the common causes for corruption in MariaDB database.

What causes corruption in MariaDB database?

Following are some factors responsible for corruption in MariaDB database:

Issues in the system, where MariaDB is saved, such as driver or hardware issues.
Abrupt system shutdown during the read/write operation.
Sudden restart of MariaDB server instance.
Bugs in MariaDB code.
Hard disk storing MariaDB database is full.
Virus or malware attacks.
MariaDB server crashes unexpectedly.

If your MariaDB database gets corrupted due to any of the reasons, you may fail to access the data. However, there are ways to repair corrupt MariaDB database. In this article, we will discuss how to repair corrupt MariaDB database.

How to Repair Corrupt MariaDB Database?

When MariaDB database gets corrupted, the first thing you can do is restore the database file from backup, if available. You can use the ?copy-back command or ?move-back options to restore the backup files. If you want to keep the original backup files, use the copy-backoption. Else, use --move-back option. The ?move-back command restores the backup to the data directory (datadir) file. Make sure the datadir directory is empty.

To use the ?move-back command, first stop the MariaDB server by using the below command.

mysql.server stop

Then, use the below command to restore the backup:

$ mariabackup --copy-back \

--target-dir=/var/mariadb/backup/

When you restore the backup, it preserves the backup file and directory privileges. So, you are required to change the ownership of the data directory (datadir) file. To change the file permissions, use this command:

$ chown -R mysql:mysql /var/lib/mysql/

Next, restart the MySQL with the command below.

mysql.server start

If the backup is not updated or not available, you need to repair the database file. You can repair the MariaDB database file or tables, based on your storage engine - InnoDB, Aria, or MyISAM.

If you're using InnoDB storage engine, then follow the below steps:

Step 1: Use the CHECK TABLE Command
You need to first check a table or tables in the corrupt MariaDB database for errors. For this, you can run the CHECK TABLE command as given below. This can also check Table View for issues.

CHECK TABLE table_name [, table_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

If the CHECK TABLE command detects corruption, the InnoDB engine will deliberately cause MariaDB to crash to prevent corruption from spreading.

Step 2: Repair the Table

First, you need to restart the MariaDB using the --innodb-force-recovery=# option. You can set it to a lower value other than zero. If MariaDB fails to start, then retry setting a higher value range, until it starts.

Once crash recovery mode is enabled, try restoring the table data. Here's how:

Dump data from the corrupt table using the SELECT command.
Then, create the table with the same structure as corrupt table. To do this, you can execute the SHOW CREATE TABLE command as given below:
SHOW CREATE TABLE table_name

Now, save the data in the created table.
Restart MariaDB using the below command:
mysqld start

Drop the table using the DROP TABLE command.

DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]

table_name [, table_name] ...

[WAIT n|NOWAIT]

[RESTRICT | CASCADE]

The DROP TABLE command will remove all the data in the table, including table definitions and triggers.

Now, recreate the table and restore the dump.

NOTE: : The REPAIR TABLE and CHECK TABLE commands do not support partitioned tables. Repairing partitioned tables using the CHECK TABLE and REPAIR TABLE options can result in error. To repair partitioned tables, you can add ALTER TABLE commandto check and repair individual tables.

If you are using MyISAM storage engine, then follow the below procedure:

You can use the myisamchk command to check, repair, or optimize non-partitioned MariaDB tables. You can run the below command:

myisamchk [OPTIONS] tables[.MYI]

If you have not selected any options, it simply checks the table in the database as the default operation.
If you want to check all the MyISAM tables in the directory, use the below command:

myisamchk *.MYI

To check all the tables by specifying the directory path, use this command:

myisamchk /path/to/database_dir/*.MYI

If the command shows any error, then follow the below steps to repair the tables:

First, stop the MySQL Server using the below command:
mysqld stop

Then, use the myisamchk command, as given below, to fix the corrupt table. This command can fix everything in the table, except the unique keys.

myisamchk ?recover TABLE

Now, again start the MySQL Server by using the below command:
mysqld start

If you're using Aria storage engine, then use the below steps:

First, check the tables by using the aria_chk command.
Then recover the tables by using the below command:
mysqld --aria-recover-options=BACKUP,FORCE

What to do if the manual procedure fails to repair corrupt MariaDB database?

If the above manual procedure fails to repair the corrupt/damaged MariaDB database, you can use a specialized tool, like Stellar Repair for MySQL. It is an advanced tool that can repair MariaDB and MySQL databases and restore all the objects, including tables, unique keys, views, indexes, triggers, etc. It allows to save the repaired data to MariaDB, MySQL, SQL Script, HTML, CSV, and XLS formats. It supports all the MariaDB versions up to 11.1.2. You can download the trial version of the tool to check its functionality. The tool supports Windows and Linux operating systems.

Steps to use Stellar Repair for MySQL to repair MariaDB database:

Download, install, and launch Stellar Repair for MySQL software.

On the dashboard, the Select Data Folder dialog box is displayed. Click Browse to select the data folder.

Select the data folder where the MariaDB database files are stored.
Select the database and database version you are using and click Repair.

When the Repair Complete message box is displayed, click OK.

The software displays a preview of the repaired tables and other recoverable objects. Select the tables you want to recover and then click Save on the File menu.
In the 'Save database window, under Save As, select the required file format to save the data. Enter the required credentials (Host name/Server ID, User name, and password) and then click Save.
When the process is finished, you will see a Save Complete message.

NOTE:To save repaired components, make sure you know below:

Root password of MariaDB server
Data folder on your system where MariaDB database is stored
License of the Stellar Repair for MySQL.

Conclusion

As mentioned above, you can manually repair the corrupted MariaDB database, created in InnoDB, MyISAM, and Aria storage engines. If the manual procedure fails to repair the corrupt MariaDB database files (.frm, .ibdata, .frm, .myi), then you can use an advanced MySQL repair database tool, like Stellar Repair for MySQL. It can repair corrupted MariaDB and MySQL databases. The tool can also repair partitioned tables in MariaDB database. It can recover all the database components, such as unique key, tables, views, foreign keys, etc. with complete integrity. The tool supports both Linux and Windows operating systems.