Detailed explanation of how to restore database data through Mysql binary log (picture and text)

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 do website backup. As a result, they are at a loss, and even bring negative consequences to website operation 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 specified data;

1. Enable binlog function and basic operations

Touse MysqlThe binlog log function must first be turned on in the configuration file of Mysql. 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 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 have deleted all 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 am using the command It only works after adding "-no-defaults", 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, you can specify To restore data at a specific time or specified location, here I will demonstrate to you using the specified time as an example.

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

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

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

The commands are as follows:

复制代码 代码如下:

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

这时我们在看后台，发现刚才删除的三篇文章都已恢复回来了，从而到达我们期望的目的。

 总结：

本文和大家分享了如何通过mysql的二进制日志文件恢复数据。但还是要提醒大家，在平时要做好网站数据备份，现在的一些主流CMS建站系统都会内置数据库备份功能，比如这里我用的蝉知系统，数据是网站的命脉，做好数据备份以避免后期不必要的麻烦或损失。

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