MySQL binlog Log Recovery data

We understand how MySQL's Binlog log is opened and the principles and common operations of binlog logs, and we know that Binlog has two major roles, one is to use Binlog to recover data, and the other is to make the decision to copy from. This note is to record how to use the Binlog log for data recovery. Of course, the data recovered using the Binlog log can only be part of the data, not be able to use the Binlog log for database backup, if you want to do a database backup, still use our traditional backup method, and Binlog can be used as an incremental backup.

Video link: http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e

Before the official start, say a MySQL full backup database, and a way to back up the database:

Let's start by creating a database, MyTest

Create Database mytest;

Then we'll create a table.

int , name varchar (20));

Then we insert two data

INSERT into T1 values (1, ' xiaoming ') and insert into T1 values (2, ' Xiaohong ');

Below we back up the MyTest database, back up to/root/bakup/

Mysqldump-uroot-p-b-f-r-x--master-data=2 mytest | gzip >/root/backup/bak_$ (Date +%f). sql.gz

Parameter description:

-B: Specify the database

-F: Refresh Log

-r: Backup stored procedures, etc.

-X: Lock table

--master-data: Add change master statement in BACKUP statement and Binlog file and location point information to view backup file

So, we make a full backup of the data. Below to delete the database and then restore the database by backing up the data.

Gzip--uroot-p < Bakup_xxx.sql

So we can import the data into the library.

Continuing with the above, we added the two data of Xiaoli and Xiaozhao, and deleted the Xiaozhao record.

Before we delete, we first refresh the Binlog log, generate a new log, and then all of the things we want to do will be recorded in the new log file. (with a detailed description of the previous Binlog log, we know that a binlog log file will be generated each time the refresh and service restarts.) ）

Flush Logs;show Master Status;

We note that the Binlog file is 0009, the location is at 154, the two information is important below we do insert and delete operations

This time we should be to check the status of the Binlog log so that we can revert to this state, but in the real environment we do not know this state, so we do not look at this state, the value of this state can be viewed later through the Binlog log file for analysis. Let's start by mistake:

Let's remove the Xiaozhao.

So the data is deleted, and we'll look at the status of the Binlog.

Show master status;

This time we found that my delete operation is a wrong operation, to recover, then how to recover it? At this time we can recover by Binlog's position.

Before we do any other processing, we recommend that you execute flush logs again immediately, that is, let the error section focus on such a binlog log file.

Let us look at the Binlog logs for 0009.

We see that the delete_rows end point is 928 this point, the starting point is at 755 this point, we can delete the operation of this data to our last backup of the content, and then through the execution of Binlog to recover, of course, restore to 755 before this point.

For example, the last time I backed up the entire database, I was able to delete the entire database and then restore it by backing up the file and then doing the incremental recovery via Binlog. So the data is back. We're not going to delete the library anymore, we'll show you how to recover data using Binlog logs.

The syntax is as follows

Mysqlbinlog mysql-bin.0000xx | Mysql-u User name-p password database name

For example we have to restore all operations (excluding our deletion, we know that the deletion is at 755 points):

Mysqlbinlog mysql-bin.000009--start-position 154--stop-position 755 | Mysql-uroot-p mytest

Check the table again.

We found that Xiaozhao back, of course, there is a xiali, because I did not delete the data before the backup. Of course, in the process of recovery we can choose to recover only xiaozhao such a piece of content the following are some common parameters in Binlog log recovery

--start-datetime: Reads from the binary log specify a time equal to the timestamp or later than the local computer

--stop-datetime: Reads a value from the binary log that specifies a time stamp that is less than the timestamp or equal to the local computer

The same as above

--start-position: Starts by reading the specified position event location from the binary log. --stop-position: Reads the specified position event location from the binary log as an event as of

MySQL binlog Log Recovery data