SoFunction
Updated on 2025-04-25

Implementation of recovery based on binlog log in MySQL

1. Introduction

In database management, the security and integrity of data are crucial. MySQL's binlog (binary log) function provides strong support for database recovery and troubleshooting. This article will introduce how to troubleshoot MySQL's binlog log problems and how to recover data based on binlog logs.

2. Troubleshoot MySQL's binlog log problem

(I) Confirm whether binlog is enabled

Log in to the MySQL database and execute the following command to see if binlog is enabled:

SHOW VARIABLES LIKE 'log_bin';

likelog_binThe value ofON, then binlog is enabled.

(II) Find binlog file location and file name pattern

Run the following command to view the base name and storage location of the binlog file:

SHOW VARIABLES LIKE 'log_bin_basename';

You can also use the following command to view the list of binlog files that exist on the current server:

SHOW BINARY LOGS;

(III) Check the binlog format

The format of binlog affects its recording content and recovery method. View binlog format:

SHOW VARIABLES LIKE 'binlog_format';

Common formats areSTATEMENT、ROWandMIXED。

3. Recover according to binlog log

(I) Find the most recent complete backup file

Recovering data requires combining backup files and binlog logs. Find the most recent full backup file and make sure it is generated before the database is deleted or corrupted.

(II) Restore backup files

Use the backup file to restore the database to the status of the backup (fake device file name isrule_backup.sql）：

mysql -u root -p rule < rule_backup.sql

(III) Analyze binlog files

  • Find the binlog file containing the delete operation: According to the timestamp of the binlog log, find the binlog file containing the delete operation.
  • Parsing binlog files:usemysqlbinlogThe tool parses the binlog file and finds the end position of the last transaction before the deletion operation (end_log_pos）。
  • Generate recovery scripts: parses the part of the binlog file from the backup point in time to before the deletion operation into a SQL script. For example, the transaction ending position before the deletion operation is310327, execute the command:
mysqlbinlog --stop-position=310327 /var/lib/mysql/binlog/binlog.000013 > restore_script.sql

(IV) Execute the recovery script

Apply the generated SQL script to the restored backup database:

mysql -u root -p rule < restore_script.sql

(V) Verify recovery

Log in to MySQL, view the database and data tables, and verify that the recovery is successful:

SHOW DATABASES;
USE rule;
SHOW TABLES;

4. Summary

binlog logs play a key role in database recovery. Regularly back up the database and ensure binlog is enabled and configured correctly, allowing quick recovery of data when data is lost or corrupted. By rationally utilizing binlog logs and combining backup files, the security and integrity of the database can be effectively guaranteed.

Notice: It is recommended to back up data before operating the database to avoid data loss. If you encounter problems during the recovery process, you can seek help from a professional database administrator.

This is the end of this article about the implementation of recovery based on binlog logs in MySQL. For more related content on MySQL binlog log recovery, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!