How to 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.

Then, use the below command to restore the 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:

Next, restart the MySQL with the command below.

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

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.

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

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

• 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

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.