If the MariaDB Server or system is improperly shutdown, it can interrupt the read/write process leading to corruption in the database file. When the database is corrupt, it prevents you from dumping the data and throws an error, like mysqldump: Error 1034: Index for table 'statistics' is corrupt; try to repair it when dump, MariaDB [mysql]> DROP FUNCTION IF EXISTS civicrm_strip_non_numeric; ERROR 1728 (HY000): Cannot load from MySQL.proc. The table is probably corrupted, etc. Sometimes, you even fail to open or access the database. In this article, we will learn how to repair and restore corrupt MariaDB database.
How to Detect Corruption in MariaDB Database?
First, you need to check the MariaDB database tables for corruption issues using the CHECK TABLE command. This command checks views, foreign keys, and other referenced tables in the MariaDB. It supports Aria, Archive, CSV, InnoDB, and MyISAM storage engines. Here is how to use the CHECK TABLE command:
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
The command also supports partitioned tables. But to check partitioned tables, you need to add an ALTER statement to the CHECK TABLE command.
If the CHECK TABLE command detects any issue in the tables, the InnoDB engine will intentionally cause the MariaDB server to crash to prevent additional damage.
Resolve Data Corruption in MariaDB after Improper Shutdown
You can follow the below methods to repair and restore the corrupt MariaDB database.
Method 1: Restore MariaDB Database from Backup
If you have a valid, recent backup of the MariaDB database file, a simple option is to restore the backup file. For this, you can use the Mariabackup - an open source utility that restores databases in MariaDB. You can use the –copy-back command to restore the backup file. It restores the entire backup to the data directory. So, it is necessary to empty the data directory (--datadir) before performing the action. The configuration file of your system for the data directory is located at /etc/mysql/my. cnf. Follow the below steps to restore the backup:
• First, you need to stop the MariaDB server services. For this, run the below command:
mysql.server stop
• Then, run the below command to use the Mariabackup utility with the –copy-back option:
$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/
• Next, change the ownership of the data directory file using the below command:
$ chown -R mysql:mysql /var/lib/mysql/
• Now, run the below command to restart the services of MariaDB Server:
mysql.server start
Method 2: Repair Corrupt Tables
If the backup file is not available, then you can use the myisamchk command to recover all the data from corrupt tables, except unique keys. Before using this command, first stop the server using mysqld stop and then recover the data by executing the following command:
myisamchk –recover TABLE
Next, use the below command to restart the server:
mysqld start
Alternatively, you can use the REPAIR TABLE command to repair the corrupt data in MariaDB MyISAM tables. Here’s how:
• First, run the SHOW GRANTS command as given below to check the permissions on the table.
SHOW GRANTS [FOR user|role]
• Check the permissions and ensure that you have all the privileges on the tables, especially SELECT and INSERT privileges.
• Then, run the REPAIR TABLE command as given below:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]
Method 3: Use the Dump and Reload Method
If you’re trying to repair the InnoDB tables in MariaDB, then you can use the Dump and Reload method. The InnoDB engine deliberately causes the MariaDB Server to crash if it finds corruption in tables. If the server crashes, then first restart the server using the –innodb-force-recovery=# option. Next, follow the below steps:
• Dump data from the affected MariaDB table using the SELECT command.
• Run the SHOW CREATE TABLE command (see the below example) to check the declaration and structure of the corrupt table.
SHOW CREATE TABLE table_name
• After this, use the same structure to create a new table by using the below command.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
);
• Save the data in the newly created table.
• Restart MariaDB by using the below command:
mysqld start
• Drop the table by using 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.
Use Professional MariaDB Repair Tool
To quickly recover data from corrupt MariaDB database with complete precision, you can use an advanced MariaDB repair tool, like Stellar Repair for MySQL. This tool can repair MariaDB tables created in both InnoDB and MyISAM engines. It is compatible with Windows and Linux operating systems.
Key features of this powerful software are:
• Repairs corrupt MariaDB and MySQL database
• Recovers all the data, including unique keys, foreign keys, tables, etc., from corrupt MariaDB database
• Fixes all types of MariaDB corruption errors
• Intuitive and user-friendly GUI
• Saves repaired files in several formats –MariaDB, MySQL, SQL Script, CSV, HTML, and XLS
• Supports MariaDB database up to 11.6
To Conclude
MariaDB database may get corrupted after improper server or system shutdown and various other reasons. You can follow the methods mentioned in this post to repair and recover the corrupt MariaDB database. However, the easiest method is to use an advanced MySQL database repair tool, such as Stellar Repair for MySQL. This tool can quickly repair severely corrupt, large-sized MariaDB database with complete integrity and precision. It can recover all the data from the database and save it in a new MariaDB file. It supports database created in both InnoDB and MyISAM storage engines.
Top comments (0)