How to Recover MariaDB Database after Binary Log Corruption?

In MariaDB Server, the binary log file records all the transactions and modifications made to the database. This log file is used for the replication and recovery process in MariaDB Server. If the binary log file gets corrupted, the replication to another database and certain recovery operations may fail. You may also experience errors, like Error 1866: The binary log file is logically corrupted, Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’ and various others.

In this article, we’ll discuss the causes for corruption in the binary log file in MariaDB and the ways to recover MariaDB database after binary log corruption.

Table of Contents

Reasons for Corruption in Binary Log File

Binary log files can get corrupted due to various internal and external factors. Some of the reasons are:

Hardware failure
Sudden power outages
Virus infection on the system
Improper system shutdown
Bad sectors on the hard disk where the binary log file is stored

How to Recover Corrupted Binary Log File?

You can use the MySQLBinlog utility to restore data from binary log file. There are two options: –force-read and –force-if-open that you can use to recover the binary log files. Here’s the syntax:

mysqlbinlog [options] log_file ...

If your binary log file is encrypted, then you need to use the read-from-remote-server option. You can also use the SHOW BINARY LOGS statement to list the binary log files on the server.

If you fail to recover the binary log files, then continue below.

Methods to Recover MariaDB Database after Binary Log Corruption

You can use the two methods below to recover the MariaDB database after binary log file corruption.

Method 1: Restore Database Backup using Mariabackup

MariaDB provides an open source utility, called Mariabackup that helps take backup of database. If you’ve have taken backup of the database file, then you can use this tool to recover it. You can use the –copy-back option in Mariabackup to restore the database file. It restores backup to the data directory known as –datadir. When you execute the –copy-back command, Mariabackup copies the backup from the target directory to the data directory. So, before running this command, make sure the data directory is empty. For this, check the configuration file, which is usually located in /etc/mysql/my.cnf. Then, follow the steps below:

First, you need to stop the MariaDB Server services.

mysql.server stop

When the services are stopped, run the Mariabackup tool with the –copy-back option. Here’s how:

$ mariabackup --copy-back \ --target-dir=/var/mariadb/backup/

After this, modify the file permissions of the data directory to change the ownership of the data directory file.
Now, again start the MariaDB Server services using the below command:

mysql.server start

Method 2: Recover MariaDB Database using a Professional MariaDB/MySQL Repair Tool

If you don’t have an updated backup of your MariaDB database, then you can use an advanced MariaDB/MySQL database recovery tool, like Stellar Repair for MySQL to recover the data. It is a professional tool designed to repair corrupted MariaDB and MySQL database files. It can recover all the objects, including tables, unique keys, and partitioned tables from the corrupt database with full integrity. It even allows you to recover deleted records from the corrupted database file. The tool supports both Windows and Linux operating systems.

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

Binary log files in MariaDB can get corrupted due to several reasons. If your binary log files get corrupted, you can use the mysqlbinlog utility to recover the binary log files. However, if this doesn’t work, then you can use a specialized MariaDB database repair tool, like Stellar Repair for MySQL to recover MariaDB database after binary log corruption. The tool can recover data from the databases created using both InnoDB and MyISAM storage engines. It can recover all the data while preserving the original structure and formatting. The tool supports MariaDB versions up to 11.3.2. To evaluate the functionality of Stellar Repair for My SQL, download its trial version for free.