Explain how to use Mysql binary logs to Restore database data

Explain how to use Mysql binary logs to Restore database data

Website administrators often mistakenly Delete website data due to various reasons and operations, and do not back up the website. The results are at a loss and even negatively affect website operation and profit. So this article will share with you how to restore data through the binlog of Mysql.

System Environment:

Operating System: CentOS 6.5X64 (Virtual Machine );

WEB Service: PHP + Mysql + apache;

Website: For convenience, you can directly build a DEMO site using the mochi system locally;

Procedure:

1. Enable the binlog function and basic operations;

2. Add data to the site;

3. Refresh the binlog;

4. delete data;

5. binlog log Content parsing;

6. Restore the specified data;

1. Enable binlog and basic operations

To use the binlog function of Mysql, you must first enable this function in the Mysql configuration file. Find the Mysql configuration file and add "log_bin = mysql-bin" to the file. In fact, this feature is usually enabled by default in various Mysql environments I have installed.

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

First, run the "show master logs" command in the mysql command line to view the existing binlog file.

 

2. Add data to the site

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

 

3. Refresh binlog

Previously mysql's binlog file was a mysql-bin.000001 and added three articles to the database at the website background. Now we refresh the binlog to generate a new mysql-bin.000002 file, as shown below:

flush logs;show master logs; 

4. delete data

Here I will delete all the three articles I just added.

5. binlog log Content Parsing

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

Use the mysqlbinlog command of mysql:

 mysqlbinlog /data/mysql/mysql-bin.000002

Note: Because my local mysqlbinlog cannot identify the default-character-set = utf8 in the binlog configuration, "-no-defaults" is added to the command to take effect.

 

The following is the log Content:

 

6. Restore the specified data;

When restoring data through mysql binlog, we can specify the recovery time point, which is a bit like server snapshot management. Therefore, to restore the deleted article, you can find a time point before the deletion and restore it to that time point.

For how to use the mysqlbinlog command, we can view it using the HELP command of mysqlbinlog, as follows:

mysqlbinlog –no-defaults –help

As shown in the help document, you can restore data by specifying the time or location. Here, we will take the specified time as an example for your demonstration.

Let's look at the log file mysql-bin.000001, as shown below:

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

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

The command is as follows:

Copy codeThe 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 looked at the background and found that all the three articles we just deleted have been restored, so as to achieve our goal.

Summary:

This article shares with you how to restore data through the mysql binary log file. However, we still need to remind everyone that we should back up website data at ordinary times. Some mainstream CMS website construction systems now have built-in database backup functions, such as the chanzhi system I use here, data is the lifeblood of websites. Back up data to avoid unnecessary troubles or losses in the future.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.