MySQL uses binlog logs to implement data recovery

MySQL binlog is a very important log in MySQL logs, which records all DML operations of the database. Through binlog logs, we can perform database read-write separation, data incremental backup, and data recovery when the server goes down.

Regular backups can certainly quickly restore data when the server crashes, but traditional full backups cannot be done in real time, so some data will be damaged when a crash occurs. If binlog is turned on at this time, the data lost during the period when no backup was made can be restored through binlog. Friends who are familiar with Redis may have thought that Redis has two persistence modes, namely AOF and RDB. RDB is similar to MySQL's full replication, and AOF is similar to MySQL's binlog.

I would like to say something about data recovery. Since binlog is so good, does it mean that we don’t need to do regular backups if binlog is enabled? Don’t do this. Why? Because the amount of binlog data is very large, and the performance of using binlog for data recovery will be very low. Because binlog is a record of operations, for example, at a certain moment, I first inserted a piece of data and then deleted the data. The data itself is gone, but there are two operations. If it is a full backup, there is definitely no such data. If binlog is used, an insert and a delete operation must be performed, so the performance and file size are relatively large.

After a lot of rambling, let's talk about data recovery:

Before we officially start, let's talk about how to fully backup the MySQL database and restore the database.

Back up the database:

First, let's create a database, mytest

create database mytest;

Next, let's 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');

Next, we 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: Specify the database

-F: refresh 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

View backup files

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

gzip -d bakup_xxx.gz
mysql -uroot -p < bakup_xxx.sql

Now we have imported the data into the library.

Continuing with the above operation, we add two new data, xiaoli and xiaozhao, and delete the record of xiaozhao.

Before deleting, let's refresh the binlog log and generate a new log, so that everything we do later will be recorded in the new log file. (From the detailed description of the binlog log above, 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 position is 154. These two pieces of information are very important.

Now let's do the insertion and deletion operations

At this time, we should check the status of the binlog log so that we can restore it to this state later. However, we do not know this state in the real environment, so we will not check this state here. The value of this state can be analyzed by checking the binlog log file later. Now let's start with the error:

Let's delete xiaozhao

In this way, the data is deleted. Let's check the status of binlog again.

show master status; 

At this time, we find that our deletion operation is a wrong operation and we need to recover it. So how do we recover it? At this time, we can restore through the binlog position.
Before performing other processing, we recommend that you immediately execute flush logs again, that is, to concentrate the erroneous parts in such a binlog log file.

Let's check the binlog log of 0009.

We can see that the end point of delete_rows is 928 and the start point is 755. We can delete the data to the last backup, and then restore it by executing binlog, of course, to before 755.
For example, if I backed up the entire database last time, I can delete the entire database, then restore it through the backup file, and then do incremental recovery through binlog. This way the data is returned. We will not delete the database here, we will directly demonstrate how to restore data using binlog logs

The syntax is as follows

mysqlbinlog mysql-bin.0000xx | mysql -u用戶名-p密碼數據庫名

For example, we want to restore all operations (excluding our deletion, which we know was at point 755):

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

Let's look at the table again

We found that xiaozhao is back again. Of course, there is an extra xiali here because I did not delete the data before the backup. Of course, during the recovery process, we can choose to only restore the content of xiaozhao.

The following are some commonly used parameters in binlog log recovery:

--start-datetime: Read from the binary log a time equal to or later than the local computer's timestamp

--stop-datetime: Read from the binary log a time value that is less than the timestamp or equal to the time of the local computer. The value is the same as above.

--start-position: Read the specified position event position from the binary log as the start.

--stop-position: Read the specified position event position from the binary log as the event end

This is the end of this article about how to use binlog logs for data recovery in MySQL. For more information about MySQL binlog data recovery, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!