How to Repair Corrupt MariaDB Database? Manual Method vs Stellar Repair for MySQL?
MariaDB database can get corrupted due to several reasons, like abrupt closure of MariaDB server application, large database size, etc. There are various ways to repair the corrupt MariaDB database. You can use native MySQL utilities to repair and recover the corrupt database or use a third-party MariaDB database repair tool. In this article, we’ll discuss how to repair MariaDB database using the manual methods and a third-party tool, like Stellar Repair for MySQL. We’ll also find out which method is better – using manual repair utilities or Stellar Repair for MySQL. But before proceeding, it’s important to understand the causes for corruption in the database.
What causes Corruption in the MariaDB Database?
Some possible causes for MariaDB database corruption are:
- Abrupt system shutdown
- Hardware issues or faulty memory
- The system hosting MariaDB is affected by virus or malware
- Hard disk storing MariaDB database has limited storage space
- MariaDB server crashes unexpectedly
Manual Methods to Repair Corrupt MariaDB Database
Here are the manual methods to repair and recover corrupted MariaDB database.
Method 1 – Recover Existing Data
In case of database corruption, the InnoDB engine intentionally causes MariaDB to crash to prevent corruption from spreading. If you are using InnoDB storage engine, you can try dumping, dropping, and re-creating corrupt tables. To do this, you need to first enable the crash recovery mode.
Note: If you have trouble starting your MySQL, forcing the –innodb-force-recovery=# option may help restart the MariaDB. You can set it to a lower value – ‘1’ other than non-zero. If it fails to start, then increase the value incrementally until it starts.
Once the crash recovery mode is enabled, follow the below instructions to recover the table data:
- Use the SELECT command to dump data from the corrupt table. The syntax is:
SELECT
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr …]
[ FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position} [ASC | DESC], … [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset [ROWS EXAMINED rows_limit] } |
[OFFSET start { ROW | ROWS }]
[FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }] ]
procedure|[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE ‘file_name’ [CHARACTER SET charset_name] [export_options] |
INTO DUMPFILE ‘file_name’ | INTO var_name [, var_name] ]
[FOR UPDATE lock_option | LOCK IN SHARE MODE lock_option]
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ‘string’]
[TERMINATED BY ‘string’]
]lock_option:
[WAIT n | NOWAIT | SKIP LOCKED]
- Now, create a table with the same structure as the damaged table. To create the table, you can use the SHOW CREATE TABLE command.
SHOW CREATE TABLE table_nameCopy Code
- Next, save the data in the created table.
- Now, restart MariaDB by executing the below command:
mysqld startCopy Code
- Next, use the DROP TABLE command to drop the table. Make sure you have privileges to use the DROP TABLE command. Use the below syntax to drop the table.
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
table_name [, table_name] …
[WAIT n|NOWAIT]
[RESTRICT | CASCADE]
Caution: The DROP TABLE command will remove all table data, definitions, and triggers. When the table is dropped, the user privileges on the table are not automatically dropped. You need to use the GRANT statement to grant privileges.
- Now, recreate the table and restore the dump.
Method 2 – Use myisamchk Command-Line Utility
If you are using the MyISAM storage engine, you can use the myisacheck command line utility to repair the MariaDB database. This utility helps you to check, repair, and optimize MyISAM tables. Here’s how to use this tool to check and repair MariaDB tables:
- First, check all the MyISAM tables in the directory for corruption. Here’s the command:
myisamchk *.MYI
- If it detects corruption in the table, then stop the MariaDB server using the below command:
mysqld stop
- Then, try to repair the corrupt table using the below command:
myisamchk –recover TABLE
The above command can help you fix all the data in the corrupt table, except the unique keys.
- Now, start the MariaDB server using the below command.
mysqld start
Repair MariaDB Database using Stellar Repair for MySQL
For quick and easy repair process, you can use an advanced third-party MariaDB repair tool, like Stellar Repair for MySQL. It is a reliable tool that can quickly repair corrupt MariDB database and recover all the database objects with complete integrity. The tool can even repair partitioned tables and unique keys in the affected MariaDB database. This tool supports MariaDB server versions up to 11.1.2. It can restore MariaDB databases created on Windows and Linux systems. To use this tool, you should know the Root password of the MariaDB server and the data folder where the database is stored.
Manual Repair Methods vs Stellar Repair for MySQL
Here’s a quick comparison between manual repair methods and Stellar Repair for MySQL.
| Manual Repair Methods | Stellar Repair for MySQL | |
| Ease-of-use | Require knowledge and skills to use command-line tools for successful repair process | Easy-to-use user interface that does not require any technical skills to perform the repair process |
| Supported storage engines | Supports MyISAM and InnoDB storage engines | Supports MyISAM and InnoDB storage engines |
| Time-consuming | Manually executing the commands to repair the MariaDB database can be time-consuming. | It only requires a few steps to repair the MariaDB database. |
| Compatibility with partitioned tables | Partitioned tables are not supported. Need to use ALTER TABLE. | Repairs partitioned tables, unique keys, triggers, indexes, etc., without the need for any additional commands. |
| Operating system compatibility | Supports Windows and Linux operating systems | Supports Windows and Linux operating systems |
| Cost | Manual repair commands are free to use | Paid tool with advanced features |
| Data integrity | There is no guarantee of 100% accuracy of the repaired data | Maintains complete data integrity of the recovered data |
| File saving formats | No multiple file saving options | Option to save the repaired database in various file formats, such as SQL, HTML, XLS, Script, etc. |
Conclusion
If your MariaDB database gets corrupt, you can use native repair utilities in MariaDB or a third-party repair tool, like Stellar Repair for MySQL. Above, we have compared the manual repair utilities and professional MariaDB repair tool to help you determine which is better for your requirements. If you need a quick solution or your database contains partitioned tables, triggers, and indexes, you can use Stellar Repair for MySQL. It can quickly repair and restore all corrupt database objects, including partitioned tables, triggers, and indexes. You can download the trial version of the tool to check its functionality. The tool supports both Windows and Linux operating systems.