Analyze how to restore database data through Mysql binary log (detailed graphic and text explanation)

This article mainly introduces in detail how to restore database data through the binary log of Mysql. It has certain reference value. Those who are interested can learn more.

Frequently, website administrators accidentally delete website data due to various reasons and operations, and do not make website backups. As a result, they are at a loss, and even bring negative consequences to website operations and profitability. Negative impact. So in this article, we will share with you how to recover data through Mysql's second mechanism log (binlog).

System environment:

Operating system: CentOS 6.5 X64 (virtual machine);

WEB service: PHP+Mysql+apache;

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

Operation steps:

1. Turn on the binlog function and basic operations;

2. Add data to the site;

3. Refresh the binlog log;

4. Delete data;

5. Binlog log content analysis;

6. Restore the specified data;

1. Enable the binlog function and basic operations

To use the binlog log function of Mysql, you must first open the binlog function of Mysql Enable this function in the configuration file and 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 have installed, this function is usually turned on by default.

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

First of all, in our mysql command line, use the "show master logs" command to view the existing binlog file.

2. Add data to the site

In the background article module of the website, I added several pieces of test data.

3. Refresh the binlog log

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

flush logs;

show master logs;

4. Delete data

Here I delete all 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 the 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 , so here I added "-no-defaults" to the command to make it work, and everyone can learn from it.

The following is a partial screenshot of the log content:

6. Restore the specified data;

When restoring data through the binlog log of mysql, we can specify the recovery to a specific point in time, which is a bit like server snapshot management. So now 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 how to use the mysqlbinlog command, we can check it through the mysqlbinlog help command, as follows:

mysqlbinlog –no-defaults –help

As shown in the help document, data can be restored by specifying time or location. Here I will use the specified time as an example to demonstrate to you.

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

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

Through the previous Operation steps We know that before deleting 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 picture above.

The command is as follows:

Copy code The code is as follows:

mysqlbinlog –no-defaults – stop-datetime='2017-04-11 09:48:48'/data/mysql/mysql-bin.000001 |mysql –uroot –p123456

At this time we were looking at the background and found that just now The three deleted articles have all been restored, thus achieving the desired purpose.

Summary:

This article shares with you how to recover data through the mysql binary log file. But I still want to remind everyone that you should back up your website data in normal times. Some of the current mainstream CMS website building systems 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 in backing up data to avoid later problems. unnecessary trouble or loss.

The above is the detailed content of Analyze how to restore database data through Mysql binary log (detailed graphic and text explanation). For more information, please follow other related articles on the PHP Chinese website!