通过二进制日志恢复误删数据库

一、初始环境

系统版本：CentOS Linux release 7.6.1810 (Core)

数据库版本： 8.0.15 MySQL Community Server - GPL

二、环境模拟

1.恢复的必要条件

​ 开启binlog日志，通过二进制日志和全备（逻辑备份）来恢复误删库。整体环境是在一次全备后有新数据插入，误删数据库进行恢复。先恢复全备状态，然后根据二进制日志提取出相关SQL将drop语句删除。重新恢复即可。

2.创建测试环境

1）查看此时数据库是否开启二进制日志

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

2）创建测试表，在数据库test下新建一个表t1并插入数据

mysql> create table t1 (id int PRIMARY KEY,name char(20));
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t1 values (1,'zzl1');
Query OK, 1 row affected (0.00 sec)


mysql> insert into t1 values (2,'zs1');
Query OK, 1 row affected (0.00 sec)


mysql> insert into t1 values (3,'ls1');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zzl1 |
|  2 | zs1  |
|  3 | ls1  |
+----+------+
3 rows in set (0.00 sec)

3）对数据库进行全备

[root@mysql8 ~]# mysqldump -u root -p -B -F -R -x --master-data=2 -h 192.168.0.35 test|gzip > /root/backup.sql.gz
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

参数说明：

​ -B 指定数据库 -h 指定目标主机

​ -F 刷新日志

​ -R 备份存储过程等

​ -x 锁表

上述中的警告，在MySQL5.6后加入了全局事务ID（GTID）用来增强数据库的主备一致性，故障恢复和容错能力。它在一个数据库里唯一但是导入其他库时可能重复。所以给出提醒，可以使用--set-gtid-purged=OFF来关闭。

4）数据库全备完成后，再插入一条数据，然后模拟删库

mysql> insert into t1 values (4,'ww1');
Query OK, 1 row affected (0.00 sec)


mysql> drop database test;
Query OK, 5 rows affected (0.02 sec)

3.恢复过程

5）通过二进制日志提取SQL

[root@mysql8 ~]# gunzip backup.sql.gz
[root@mysql8 ~]# ls backup.sql 
backup.sql
[root@mysql8 ~]# grep CHANGE backup.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=195;
#备份时指定了参数 master-data=2所以这里可以确定这个是全备时刻的binlog位置在这个binlog的195行之前的所有数据都包含在其中
[root@mysql8 ~]# cp /data/mysql/mysql_3306/logs/mysql-bin.000002 /root 
[root@mysql8 ~]# ls mysql-bin.000002 
mysql-bin.000002

6）提取全备后新增数据内容

[root@mysql8 ~]# mysqlbinlog -d test mysql-bin.000002 > insc002.sql
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.
#这个警告可以适用 --skip-gtids 来去除
[root@mysql8 ~]# vim insc002.sql 
#把drop语句删除

7）全备恢复

[root@mysql8 ~]# mysql -u root -p < backup.sql 
Enter password: 
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
#开启了GTID需要将GTID重置或者加-f 刷新一下
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
#重新执行上面的语句
[root@mysql8 ~]# mysql -u root -p < backup.sql 
Enter password: 
[root@mysql8 ~]# 


#全备恢复完成，查看一下，发现缺少id=4的数据
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zzl1 |
|  2 | zs1  |
|  3 | ls1  |
+----+------+
3 rows in set (0.00 sec)

8）进行全备到删库期间数据恢复，注意指定数据库

[root@mysql8 ~]# mysql -u root -p test < insc002.sql 
Enter password: 
ERROR 1790 (HY000) at line 62: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns 32415cb9-d5ff-11ea-a561-000c29135205:39. Ownership is released on COMMIT or ROLLBACK.
#虽然报错但是数据已经恢复了，报错原因  设置的起始pos和结束pos点并不是一条完整的mysql事务开启事务结束语句 同上加入 -f清空所有binlog日志.重新开始记数
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zzl1 |
|  2 | zs1  |
|  3 | ls1  |
|  4 | ww1  |
+----+------+
4 rows in set (0.00 sec)

来自为知笔记(Wiz)