MySQL使用binlog完成数据恢复

1.准备数据

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

mysql> use t1;
Database changed
mysql> create table cool(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

2.备份数据库

# mysqldump -uroot -p --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full.sql			//数据库进行全备

3.新建一个数据和表

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

mysql> use t1;
Database changed
mysql> create table cool(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

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

mysql> use t2;		//进入表中
Database changed
mysql> create table t2(id int,name varchar(30));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t2(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu'); 		//向表中插入信息
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;     //保存操作
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;    	//查看表信息
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.03 sec)

4. 模拟误删操作

mysql> drop database t2;
Query OK, 1 row affected (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxy                |
| mysql              |
| performance_schema |
| sys                |
| t1                 |
+--------------------+
6 rows in set (0.02 sec)

5. 查找binlog的起点及终点

# vi full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000004', MASTER_LOG_POS=524;		//查看起点=524

mysql> show master status;			//查看master数据库当前正在使用的二进制日志
+----------------------+----------+--------------+------------------+------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------------+----------+--------------+------------------+------------------------------------------+
| master-binlog.000004 |     1286 |              |                  | 29aa17ea-b911-11eb-8655-000c2971ea85:1-9 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> show binlog events in 'master-binlog.000004';			//查看到终点位置为=1200
+----------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name             | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+----------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| master-binlog.000004 |    4 | Format_desc    |        45 |         123 | Server ver: 5.7.34-log, Binlog ver: 4                             |
| master-binlog.000004 |  123 | Previous_gtids |        45 |         194 | 29aa17ea-b911-11eb-8655-000c2971ea85:1-3                          |
| master-binlog.000004 |  194 | Gtid           |        45 |         259 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:4' |
| master-binlog.000004 |  259 | Query          |        45 |         347 | create database t1                                                |
| master-binlog.000004 |  347 | Gtid           |        45 |         412 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:5' |
| master-binlog.000004 |  412 | Query          |        45 |         524 | use `t1`; create table cool(id int,name varchar(20))              |
| master-binlog.000004 |  524 | Gtid           |        45 |         589 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:6' |
| master-binlog.000004 |  589 | Query          |        45 |         677 | create database t2                                                |
| master-binlog.000004 |  677 | Gtid           |        45 |         742 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:7' |
| master-binlog.000004 |  742 | Query          |        45 |         852 | use `t2`; create table t2(id int,name varchar(30))                |
| master-binlog.000004 |  852 | Gtid           |        45 |         917 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:8' |
| master-binlog.000004 |  917 | Query          |        45 |         987 | BEGIN                                                             |
| master-binlog.000004 |  987 | Table_map      |        45 |        1033 | table_id: 141 (t2.t2)                                             |
| master-binlog.000004 | 1033 | Write_rows     |        45 |        1104 | table_id: 141 flags: STMT_END_F                                   |
| master-binlog.000004 | 1104 | Xid            |        45 |        1135 | COMMIT /* xid=481 */                                              |
| master-binlog.000004 | 1135 | Gtid           |        45 |        1200 | SET @@SESSION.GTID_NEXT= '29aa17ea-b911-11eb-8655-000c2971ea85:9' |
| master-binlog.000004 | 1200 | Query          |        45 |        1286 | drop database t2                                                  |
+----------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.02 sec)

6. 截取binlog起点及终点的日志

# mysqlbinlog --skip-gtids --start-position=524 --stop-position=1200 /data/mysql/data/master-binlog.000004 > /opt/binlog.sql			//将MySQL中的数据导入到该文件中

7.恢复数据

mysql> source /opt/binlog.sql     //导入数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxy                |
| mysql              |
| performance_schema |
| sys                |
| t1                 |
| t2                 |
+--------------------+
7 rows in set (0.00 sec)