How to recover database data from MySQL binary logs

Often site administrators for various reasons and operations, resulting in site data mistakenly deleted, but also did not do site backup, the results are overwhelmed, and even to the site operation and profit negatively. So this article we will share with you how to recover data through the Mysql two mechanism log (Binlog) .

System Environment:

Operating system:CentOS 6.5 X64 ( virtual machine );

WEB Services:Php+mysql+apache;

Website: For convenience, directly in the local use Cicada know system to build a DEMO site;

Operation steps:

1. Open binlog function and basic operation;

2. Add data to the site;

3. Refresh the binlog log;

4. delete data;

5.binlog log content parsing;

6. restore the specified data;

1. Open binlog function and basic Operation

To use MySQL 's binlog logging feature, the first thing to do is to start this feature in the MySQL configuration file, which is simple. Locate 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.

When the Binlog function is turned on, there will be files such as mysql-bin.000001, mysql-bin.000002, and so on in the MySQL database directory, which is the binary log file of MySQL. A new binary log file will be created whenever MySQL starts or manually refreshes the log.

First, 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 website background article module, I added a few test data.

3. Refresh binlog Log

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

Flush Logs;show master logs;

4. Delete Data

Here I delete all the three posts I just added.

5.binlog Log Content parsing

mysql binary log files record the operation of MySQL , such as just delete operation, we look at the specific contents of the log file.

Use the MySQL mysqlbinlog command:

Mysqlbinlog/data/mysql/mysql-bin. 000002

Note: Because my local mysqlbinlog does not recognize the Default-character-set=utf8 in the Binlog configuration, I add "–no-defaults" to the command here, everyone lesson.

Here is the Log Content section:

6. restores the specified data;

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

For instructions on how to use the mysqlbinlog command, we can view it through the mysqlbinlog help command, as follows:

Mysqlbinlog–no-defaults–help

As shown in the Help document, you can recover data by specifying a time or a specified location, where I give you a demonstration of the time specified.

Let's take a look at the log file mysql-bin.000001, as follows:

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

We know by the preceding steps that we have generated the mysql-bin.000002 log file Before we delete the data, so we just have to revert to this point in time and I have found it.

The command is as follows:

Mysqlbinlog–no-defaults–stop-datetime= '-Geneva-All:48 '/data/mysql/mysql-bin. 000001 |mysql–uroot–p123456

At this time we are looking backstage, found that just deleted three articles have been restored back, so as to reach our desired goal.

Summary:

This article shares with you how to recover data by using MySQL binary log files. But still want to remind everyone, in peacetime to do a good job of website data backup, now some mainstream CMS station system will have built-in database backup function, such as I use the cicada know system, data is the lifeblood of the site, do a good job of data backup to avoid unnecessary trouble or loss later.

How to recover database data from MySQL binary logs