MariaDB, similar to MySQL, is an open source database that is used to store and organize data. MariaDB database, just like other databases, is also susceptible to corruption. The database can get corrupted due to several reasons, like system issues, sudden crashing of application or system hosting database, virus or malware attacks, hardware problems, etc. In this article, we will mention some effective methods to repair the MariaDB database.
How to Check if the MariaDB Database is Corrupted?
Before proceeding, first check if the MariaDB database is corrupted. For this, you can use the CHECK TABLE command. This command checks the MariaDB tables for any errors and issues. This command supports tables created in Archive, Aria, CSV, InnoDB, and MyISAM storage engines. Here’s the syntax of the CHECK TABLE command:
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
The options, used in the above CHECK TABLE command, may vary according to the storage engines. Let’s take a look at them:
FOR UPGRADE: This option is used to check the database after upgrading the MariaDB's version. It checks incompatible table formats.
FAST: This option only checks the tables that were not closed properly or marked as corrupt. It only supports Aria and MyISAM engines.
QUICK: This option supports MyISAM and Aria storage engines. It does not check the delete link chain, thus, performs the checks quickly.
EXTENDED: This option performs a complete check on tables. It checks each row and its keys, and also index keys and its primary clustered keys. This option supports InnoDB engine on MariaDB 10.6.11 and later versions.
CHANGED: This option checks tables, which were modified since the last CHECK/REPAIR in MyISAM and Aria engines.
MEDIUM: This option is used to check and find errors in MariaDB tables. It scans the data files and checks integrity between the data and the ‘index files’ with checksum.
If the CHECK TABLE command finds any issue in the tables, it will directly report the error with description.
Methods to Repair MariaDB Database
If the MariaDB database is corrupted, then you can try to restore the database from backup. If you have been using mariadb-dump client to take backups of your data regularly, you can use the dump files to restore the data. If the backup file (dump file) is not available or updated, then follow the below methods to repair the MariaDB database.
1. Use REPAIR TABLE Command
You can use the REPAIR TABLE command to repair the tables in MariaDB database. You can directly run the REPAIR TABLE command on tables created in Archive, Aria, CSV, and MyISAM Tables.
Here is the syntax:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]
Note: Before using the above command, make sure you have the SELECT and INSERT privileges on the table. To repair the partition tables, you need to add the ALTER option to the REPAIR TABLE command.
If you’re using InnoDB engine, then the engine will cause the MariaDB to crash if it detects corruption in table. To repair the InnoDB table, you need to first restart the MariaDB by using the --innodb-force-recovery=# option. Then, to recover the existing data,
- Dump the data from corrupted table.
- Save the table structure with the SHOW CREATE TABLE command. Restart MariaDB.
- Next, drop the table with the DROP TABLE command.
- Now, rebuild the table and restore the dump.
2. Use a Professional MariaDB Database Repair Tool
If the REPAIR TABLE command fails to fix the corrupted MariaDB database, then you can take the help of a professional MariaDB database repair tool to repair the database. Stellar Repair for MySQL is one such tool that can quickly repair the MariaDB database without any data loss. It can repair tables created in MyISAM and InnoDB storage engines. It also supports repairing of partition tables in the database. It can restore all the database objects, including tables, triggers, and indexes with complete precision.
Some Key features of Stellar Repair for MySQL:
- Repairs InnoDB and MyISAM tables of the MariaDB database
- Restores all the data from damaged MariaDB database with complete integrity
- Supports repairing MariaDB database on both Windows and Linux
- Supports MariaDB server up to 11.3.2
- Allows to save the repaired file in multiple formats, like MariaDB, MySQL, CSV, etc.
- Helps to resolve complex corruption-related errors.
Conclusion
If you’re having trouble opening MariaDB database tables or accessing the records, you can use the CHECK TABLE command to check corruption in MariaDB tables. If corruption is detected, then use the REPAIR TABLE command to repair the tables. If this didn't work, you can use a MariaDB database repair tool, like Stellar Repair for MySQL to repair the corrupt MariaDB database. This tool can restore all the data from the corrupt MariaDB database with 100% integrity. It can repair both InnoDB and MyISAM tables.
Top comments (0)