The MariaDB Server may unexpectedly crash or stops responding due to various reasons, like hardware failure, bugs in the server code, sudden power failure, etc. After an unexpected server crash, you may face inconsistency issues in the MariaDB database or corruption in tables.
When the tables are corrupted, you may fail to open or access the tables or encounter errors when trying to open the tables. So, you need to repair the MariaDB tables as soon as possible to prevent any further harm or data loss. In this article, we will discuss the methods to repair the corrupt tables in the MariaDB Server.
Methods to Repair MariaDB Database
When MariaDB tables get corrupted, you can use native MySQL utilities to repair and recover the corrupt database tables. Here’s how:
Method 1 - Restore the Database from Backup
If you have taken backup of the MariaDB database file, then you can use the Mariabackup utility to restore the file from backup. You can use the –copy-back command to restore the database file. This command restores the backup to the data directory. So, before executing the –copy-back command, make sure the data directory (--datadir) is empty. You can confirm this by checking the configuration file on your system, which is usually located in /etc/mysql/my. cnf. Then, follow the below instructions:
• First, stop the MariaDB Server services by using the below command.
mysql.server stop
• Next, run the Mariabackup tool with the –copy-back option (see the below example):
$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/
• Now, change the ownership of the data directory file.
• After this, run the following command to restart the MariaDB Server services:
mysql.server start
If the backup file (dump file) is not available or updated, then follow the next methods to repair the MariaDB tables.
Method 2 - Dump and Reload the Table Data
If you’re using InnoDB search engine, then you can use the Dump and Reload method to repair the tables in the MariaDB database. The InnoDB engine causes the MariaDB Server to crash if it detects corruption in tables. If the MariaDB Server crashes, then you need to restart the MariaDB by using the –innodb-force-recovery=# option. Once you are able to restart the MariaDB, then follow the below steps:
• Run the Select command as given below to dump data from the corrupt MariaDB table:
SELECT * FROM customers
• After this, you can run the SHOW CREATE TABLE command to know the declaration of the corrupt table.
SHOW CREATE TABLE table_name
• Next, create a table with the same structure as the corrupt table. To do this, execute the CREATE TABLE command as given below:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
);
• After this, save the data in the newly created table.
• Now, use the below command to restart MariaDB:
mysqld start
mysqld start
• Drop the table by executing the DROP TABLE command as given below.
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
table_name [, table_name] ...
[WAIT n|NOWAIT]
[RESTRICT | CASCADE]
• Now, recreate the table and restore the dump.
Method 3 - Use REPAIR TABLE Command
You can also use the REPAIR TABLE command to repair the corrupted tables in MariaDB database. But, before using this command, ensure that you have all the permissions and privileges on the tables, like SELECT and INSERT privileges. To check this, you can run the SHOW GRANTS command as given below:
SHOW GRANTS [FOR user|role]
Now, run the REPAIR TABLE command as given below:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]
If you want to repair partitioned tables using the REPAIR TABLE command, then you need to use the ALTER statement in it.
Method 4 - Use myisamchk Command
You can use the myisamchk command to repair MariaDB tables. It can help you recover all data from the tables, except unique keys. Here’s how to use this command:
• First, use the below command to stop the server.
mysqld stop
• Then, recover the tables by executing the below command:
myisamchk –recover TABLE
myisamchk –recover TABLE
• After this, restart the server:
mysqld start
An Alternative Solution - Use a Professional MariaDB Repair Tool
If you want to repair the MariaDB database quickly and without any data loss, then you can use an advanced MariaDB repair tool. Stellar Repair for MySQL is one such tool that can repair database created in both InnoDB and MyISAM storage engines. It can recover partitioned tables, indexes, unique keys, foreign keys, and other objects from corrupted MariaDB database with complete precision.
Some key features of Stellar Repair for MySQL:
• Repairs MySQL and MariaDB databases created using InnoDB and MyISAM storage engines
• Batch repairs multiple MariaDB databases in a single process
• Recovers all the database objects, including primary keys, foreign keys, tables, table properties, data types, views, and triggers
• Previews recoverable database objects before saving
• Supports repairing of partition tables
• Supports all MariaDB versions up to 11.3.2
Conclusion
The unexpected MariaDB crash can lead to MariaDB table corruption. In this article, we have discussed the methods to repair and restore corrupt tables after MariaDB Server crash. You can restore the database from backup. If the backup is not up-to-date, you can use the REPAIR TABLE and myisamchk commands to repair the tables. To save time and effort, you can use a professional MariaDB repair tool, like Stellar Repair for MySQL, to repair the damaged or corrupt MariaDB tables with complete integrity.
Top comments (0)