First, create a simulation environment.

mysql> create database testdb;
mysql> use testdb;
mysql> CREATE TABLE `people` (
  `name` varchar(20) DEFAULT NULL,
  `age` varchar(20) DEFAULT NULL,
  `birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
mysql> CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
mysql> INSERT INTO pet VALUES ('Fluffy','Harold','cat','f','1993-02-04',NULL);

2, Database full backup

[root@bogon /]# mysqldump -uroot -proot --single-transaction --flush-logs --master-data=2 --databases testdb --events --routines --triggers > /testdb.sql

See this article for a detailed description of parameters

Three, analog delete and pre and post data manipulation.

mysql>  use testdb;

mysql> INSERT INTO pet VALUES ('Claws','Gwen','cat','m','1994-03-17',NULL);
	   
mysql> drop table pet;

mysql> INSERT INTO `testdb`.`people`(`name`, `age`, `birth`) VALUES ('yuantu', '12', '2022-02-26');

mysql> exit 

4, Start data recovery

1. Refresh the binlog file first

mysql> flush logs; -- Execution creates a new log file
Query OK, 0 rows affected (0.03 sec)

mysql> show binary logs; --flush logs The refreshed log file is mysql-bin.000003
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      1831 | No        |
| mysql-bin.000002 |       711 | No        |
| mysql-bin.000003 |       157 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> exit

1. View the contents of the full backup file and obtain the newly created log information during backup

[root@bogon /]# more /testdb.sql 

The binlog log file refreshed by the full backup is MySQL bin 000002
Log location: 157
The flush logs refreshed log file is MySQL bin 000003
It can be seen that deleting tables and other data operations are in MySQL bin 000002

1. Determine the location of binlog log events according to the approximate time range of deleting the table

[root@bogon /]# mysqlbinlog --start-datetime="2022-02-26 20:50:00" --stop-datetime="2022-02-26 23:55:00" --verbose /usr/local/mysql/data/mysql-bin.000002 | grep -C 15 "DROP TABLE"

You can see that the log position recorded by DROP TABLE is between 543 and 675

1. Restore the full backup first

mysql> source /testdb.sql
mysql> use testdb;
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| people           |
| pet              |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from pet;
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
+--------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> select * from people;
Empty set (0.00 sec)
exit

Steps for simulating fault deletion
First insert a piece of data into the table to be deleted
Then delete the table pet (know the time range of the table deletion operation)
Then insert a piece of data in the people table

Summarize the information obtained after starting fault recovery

The binlog log file refreshed by the full backup is MySQL bin 000002
Log location: 157
The log file refreshed by flush logs is MySQL bin 000003
The log location of table deletion operation is between 543 and 675

Then the next recovery step is
According to MySQL bin 00000 2 first recover the database operations recorded before deleting the table in the log
Then skip the operation record of deleting the table and recover the database operation recorded in the log log after deleting the table

1. Database operation recovery before deleting table

[root@bogon /]# mysqlbinlog --start-position=157 --stop-position=543 /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -proot

1. Database operation recovery after deleting tables

[root@bogon /]# mysqlbinlog --start-position=675 /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -proot

1. View data recovery status

mysql> use testdb;
mysql>  select * from pet;
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from people;
+--------+------+------------+
| name   | age  | birth      |
+--------+------+------------+
| yuantu | 12   | 2022-02-26 |
+--------+------+------------+
1 row in set (0.00 sec)

So far, the binlog log after full backup has been completely restored except for the operation of deleting tables.

tips

hypothesis

1. The binlog log file refreshed during full backup is MySQL bin 000011

2. The binlog log file manually refreshed by flush log before full backup recovery is MySQL bin 000015

3. It is found that the log file recording the deletion table is MySQL bin 000014

After the full backup is restored, we need to restore MySQL bin 000011 mysql-bin. 000012 mysql-bin. 000013 entire binlog log recovery

[root@bogon /]# mysqlbinlog  mysql-bin.000011 mysql-bin.000012 mysql-bin.000013 | mysql -uroot -proot

Then for MySQL bin 000014 according to MySQL bin 00000 2

Solution steps for deleting Library

1. Restore full backup
2. Check which log file the database deletion operation is in, and record the log location before the database deletion operation (assumed to be 790)
3. Restore all log files generated after full backup (except log files recording database deletion operations)
4. Restore the log file recording the database deletion operation to 790

Refer to the MySQL official website document - backup and recovery section