MySQL binlog data recovery, mysqlbinlog

MySQL binlog data recovery, mysqlbinlog

We have learned how to enable binlog of MySQL and the principles and common operations of binlog. We know that binlog has two major functions: Use binlog to restore data, the other is for master-slave replication. This document describes how to use binlog for data recovery. Of course, the Data recovered by using binlog logs can only be part of the data and cannot be used for database backup. If you want to back up the database, you still need to use our traditional backup method, binlog can be used as Incremental backup.

 

Http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e

 

Before the official start, let's talk about the complete mysql database backup and database restoration methods to back up the database:

 

First, create a database named mytest.

create database mytest;

Next we will create a table.

use mytest;create table t1(id int ,name varchar(20));

Then we insert two pieces of data.

insert into t1 values (1,'xiaoming');insert into t1 values (2,'xiaohong');

Back up the mytest database 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: Specifies the database.

 

-F: refresh the log

 

-R: backup storage process, etc.

 

-X: Lock table

 

-- Master-data: add the change master statement and binlog file and location information to the backup statement to view the backup file.

 

In this case, we have made a complete backup of the data. Next, delete the database and restore the database by backing up the data.

gzip -d bakup_xxx.gzmysql -uroot -p < bakup_xxx.sql

In this way, the data is imported to the database.

 

Continue with the above operations. We add the xiaoli and xiaozhao data and delete the xiaozhao record.

 

Before deletion, refresh the binlog and generate a new log. Then, all the operations to be performed will be recorded in the new log file. (According to the preceding binlog details, we know that a binlog log file is generated every time the service is refreshed and restarted .)

flush logs;show master status;

 

We note that the binlog file is 0009 and the location is 154. The two information is very important. Let's perform the insert and delete operations.

 

At this time, we should check the status of the binlog so that we can recover to this status later. However, we do not know the status in the real environment, therefore, this status is not checked here. The value of this status can be analyzed by viewing the binlog log file later. The following error occurs:

 

Let's delete xiaozhao.

In this way, the data will be deleted. Now let's check the binlog status.

show master status;

 

At this time, we found that the delete operation was an incorrect operation. To restore it, how should we restore it? In this case, we can use the position of binlog to restore the data.

 

Before other processing, we recommend that you immediately execute flush logs, that is, to make the error part concentrated in such a binlog log file.

 

Let's view the binlog 0009.

 

We can see that the delete_rows end point is 928, and the starting point is 755. we can delete the operation data to the content of our last backup, and then restore it by executing the binlog, of course, it is restored to the point 755.

 

For example, if I backed up the entire database last time, I can delete the entire database, restore it through the backup file, and then perform incremental recovery through binlog. In this way, the data is returned. The database will not be deleted here. We will directly demonstrate how to use binlog to restore data.

 

Syntax:

Mysqlbinlog mysql-bin.0000xx | mysql-u username-p Password Database Name

 

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

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

 

 

View the table again

 

We found that xiaozhao is back again. Of course, there is another xiali here because I didn't delete the data before the backup. Of course, we can choose to recover only xiaozhao in the recovery process. Below are some common parameters in binlog log recovery.

 

-- Start-datetime: read from binary logs the time specified to be the timestamp or later than the local computer

 

-- Stop-datetime: Read the specified time value less than the timestamp or equal to the value of the local computer from the binary log.

 

Same as above

 

-- Start-position: Read the specified position event location from the binary log as the start point. -- Stop-position: Read the specified position event location from the binary log as the event's End Time