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 replication and recovery processes, including point-in-time recovery and replication synchronization in MariaDB Server. If the binary log file gets corrupted, 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', which typically indicates missing or mismatched binary log files, as well as other related errors.

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

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
• Disk full conditions during binary log write operations
• Improper configuration of settings like sync_binlog
• File system inconsistencies or crashes during log rotation
• Incompatible upgrades or version mismatches in MariaDB

How to Recover Corrupted Binary Log File?

You can use the MySQLBinlog utility to restore data from binary log file. It provides several options such as --force-read, --start-position, --stop-position, --start-datetime, and --stop-datetime that you can use to recover the binary log files. Here’s the syntax:

mysqlbinlog [options] log_file ...

The --force-read option can help skip corrupted events, though it may result in partial data loss. You can also extract valid events and pipe them directly into the MariaDB server:

mysqlbinlog binlog.000001 | mysql -u root -p

If your binary log file is encrypted, then you need to use the --read-from-remote-server option. Note that encrypted binary logs require proper keyring configuration, and recovery may not be possible without access to the encryption keys. You can also use the SHOW BINARY LOGS statement to list the binary log files on the server.

In modern MariaDB setups using Global Transaction IDs (GTIDs), binary log corruption can also affect replication consistency. In such cases, you may need to skip problematic transactions or rebuild replication from a fresh backup.

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 databases. If you’ve taken a backup of the database, 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.

Before restoring, you must prepare the backup using the following command:

mariabackup --prepare --target-dir=/var/mariadb/backup/

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.

Important: Always take a backup of the existing data directory before removing any files to avoid permanent data loss. You can verify the data directory location in the configuration file (commonly /etc/mysql/my.cnf).

Then, follow the steps below:

First, you need to stop the MariaDB Server services.

~~mysql.server stop~~
systemctl stop mariadb

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:

chown -R mysql:mysql /var/lib/mysql

Now, again start the MariaDB Server services using the below command:

~~mysql.server start~~
systemctl start mariadb

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. Such tools are typically used as a last resort when manual recovery and backups are not available. 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 recent MariaDB versions (check the vendor’s site for the latest compatibility details)

Additional Recovery Approaches

In some cases, you may also consider the following recovery techniques:

• Restoring from a replica instead of a backup if replication is still functional
• Using mysqldump to export accessible data before attempting repairs
• Rebuilding replication using a fresh snapshot and reinitializing slaves
• Using relay log recovery options like --relay-log-recovery for replicas

How to Prevent Binary Log Corruption

To minimize the risk of binary log corruption, consider the following best practices:

• Set sync_binlog=1 to ensure logs are safely written to disk
• Use reliable storage systems with power-loss protection
• Monitor disk space to avoid interruptions during writes
• Maintain regular full and incremental backups
• Use RAID or cloud-based redundancy for critical systems

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. For best results, always combine recovery strategies with preventive measures such as regular backups and proper server configuration.