Detailed explanation of how to restore database data through MySQL binary log

Website administrators often accidentally delete website data due to various reasons and operations, and fail to back up the website. As a result, they are at a loss and even have a negative impact on website operations and profits. So in this article we will share with you how to recover data through MySQL's binary log (binlog).

System environment:

Operating system: CentOS 6.5 X64 (virtual machine);

WEB service: PHP+Mysql+apache;

Website: For convenience, directly build a DEMO site locally using the Chanzhi system;

Steps:

1. Enable binlog function and basic operations;

2. Add data to the site;

3. Refresh binlog log;

4. Delete data;

5. Binlog log content analysis;

6. Restore specified data;

1. Enable binlog function and basic operations

To use the binlog logging function of MySQL, you must first enable this function in the MySQL configuration file. The operation is very simple. Find the MySQL configuration file and add a line "log_bin = mysql-bin" to the file. In fact, in the various Mysql environments I installed, this feature is usually turned on by default.

After turning on the binlog function, there will be files such as mysql-bin.000001, mysql-bin.000002, etc. in the mysql database directory. These are the binary log files of mysql. A new binary log file is created every time MySQL is started or the log is manually refreshed.

First, we use the "show master logs" command in the mysql command line to view the existing binlog files.

2. Add data to the site

In the article module in the website backend, I added some test data.

3. Refresh binlog log

Previously, the mysql binlog file was mysql-bin.000001, and three articles were added to the database in the background of the website. Now we refresh the binlog log, and a new mysql-bin.000002 file will be generated, as follows:

flush logs;

show master logs; 

4. Deleting Data

Here I delete the three articles I just added.

5. Binlog log content analysis

Mysql's binary log file records mysql operations, such as the deletion operation just now. Let's take a look at the specific contents of the log file.

Use mysqlbinlog command of mysql:

 mysqlbinlog /data/mysql/mysql-bin.000002

Note: Because my local mysqlbinlog cannot recognize the default-character-set=utf8 in the binlog configuration, I added "--no-defaults" to the command here to make it work. Please take this as a lesson.

The following is a partial screenshot of the log content:

6. Restore specified data;

When restoring data through MySQL's binlog log, we can specify the recovery to a specific point in time, which is a bit like server snapshot management. So now if we want to restore the article we just deleted, we can find a time point before deletion and restore it to that time point.

Regarding the usage of the mysqlbinlog command, we can view it through the mysqlbinlog help command, as follows:

mysqlbinlog –no-defaults –help 

As shown in the help documentation, you can restore data by specifying a time or a location. Here I will use specifying a time as an example to demonstrate.

Let's check the log file mysql-bin.000001, as follows:

mysqlbinlog -no--defaults /data/mysql/mysql-bin.000001 

From the previous steps, we know that before deleting the data, we generated the mysql-bin.000002 log file, so we only need to restore to this point in time. I have found this time in the figure above.

The command is as follows:

At this time, we looked at the background and found that the three articles that had just been deleted had been restored, thus achieving our desired goal.

Summarize:

This article shares with you how to recover data through MySQL binary log files. But I still want to remind everyone to do a good job of backing up website data in normal times. Some mainstream CMS website building systems now have built-in database backup functions, such as the Chanzhi system I use here. Data is the lifeblood of the website. Do a good job of backing up data to avoid unnecessary trouble or loss later.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.