Implementation of mysql using mysqlbinlog command to restore accidentally deleted data

Experimental environment:

MYSQL 5.7.22

Enable binary logging

Log format MIXED

Experimental process:

1. Execute: FLUSH LOGS;

The master-bin.000014 file is the newly generated file

The purpose of refreshing the log is to make the experimental content more intuitive and easier to observe the content of the entire experimental process.

I have seen many articles online that use REST MASTER, but they do not explain the seriousness of this command.

This command will delete all log files and reset the file names and log points to zero. In 99% of cases, this command is not needed.

To delete logs, you can use PURGE MASTER LOGS... This is safer

2. A new log file has been generated. Let's take a look at the content first. There are a few points you need to understand.

View the binary log file command: mysqlbinlog master-bin.000014

# at 4
#180903 16:19:12 server id 1 end_log_pos 123 CRC32 0xe03659b3 Start: binlog v 4, server v 5.7.22-log created 180903 16:19:12

First look at the two arrows above:

• # at 4 (the event start point)
• #180903 16:19:12 (represents the time)
• Server id 1 (When performing master-slave replication, you need to specify a unique SERVER ID for each MYSQL database. I did not configure it, and the default is 1)
• end_log_pos 123 (event end point)

Look at the two arrows below:

• # at 123 (the event start point, corresponding to the event end point above)
• end_log_pos 154 (event end point)
• The content between at 4 and at 123 is the event content

3. Simulate a business scenario, create a table, insert data, and finally delete a table. To make it realistic, I created two databases, wrote content to different databases at the same time, and finally deleted a table in one of the databases.

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

mysql> create database t1;
Query OK, 1 row affected (0.03 sec)

mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> use t2;
Database changed
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values ​​(3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values ​​(4);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values ​​(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values ​​(2);
Query OK, 1 row affected (0.01 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(20);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values(10);
Query OK, 1 row affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(222);
Query OK, 1 row affected (0.01 sec)

mysql>

Create T1 and T2 libraries, and create T1 and T2 tables.

Insert data into T1: 1, 2, 10

Insert data into T2: 3, 4, 20, 222

In the simulation scenario, table T1 is deleted, but the business of table T2 in database T2 continues to run.

Now we will restore the T1 table through the log.

First, find the log point of the deletion command:

mysqlbinlog master-bin.000014|grep -5a "DROP TABLE" 

See #AT 2439 (write down this number)

The DROP TABLE operation executed at this event point.

Since the log file contains not only the logs of the T1 database, but also the logs of the T2 database, we will only retrieve the logs of the T1 database.

And only the logs before log point 2439 are taken and reapplied

If the 2439 log is taken, the database will rebuild the database and table, insert data, and execute the table deletion statement when it is applied again.

mysqlbinlog -d t1 --stop-position=2439 master-bin.000014>test.sql (an error was reported when executing this statement)

WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
I can't figure out the reason for now, so I searched on Baidu and modified it to:

mysqlbinlog master-bin.000014 -d t1 --skip-gtids --stop-position=2439>test.sql 

-d: parameter specifies a database log

The command means to output the T1 database log in the master-bin.000014 log file, the log before event point 2439, to test.sql

# tail test.sql

Look at the last few lines of the file

Log in to the database:

mysql> use t1;
Database changed

mysql> source test.sql

An error was reported once in the middle because it contained the statement for building the database T1.

Check the table contents again

This way the data is returned.

This is the end of this article about how to use mysqlbinlog command to recover accidentally deleted data. For more information about how to recover accidentally deleted data using mysqlbinlog, please search 123WORDPRESS.COM for previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!