Mysql 数据误删除恢复演练（基于全备、及全备和binlog）

最新推荐文章于 2024-03-29 17:49:00 发布

石头-豆豆

最新推荐文章于 2024-03-29 17:49:00 发布

阅读量1.2k

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/xjjj064/article/details/120038604

版权

Mysql 专栏收录该内容

14 篇文章

订阅专栏

Mysql 数据误删除恢复演练（基于全备、及全备和binlog）

一、背景描述

①、演练方向

近期主要做一些数据库误删除然后进行恢复的演练。主要从以下几个方面进行演练。
1、在有全备或binlog的情况下误删除，数据恢复演练（生产环境建议做好每日备份）。
2、在没有做全备只开启binlog的情况下如何进行误删除数据演练。------ binlog未设置过期时间且一直保存有！
3、在没有做全备且binglog未开启情况下如何进行数据误删除演练。 -------- GG了，数据没了！

②、环境准备

通过将生产数据库Down到本地测试环境，并基于全库备份进行数据恢复到测试库（test）！演练即基于此测试库。

③、本篇文章演练环境

手工删除test库中的score表中ID小于7的数据，然后在修改ID等于8的数据值。在此时发现删除属于误删除，需要恢复删除的数据并需要保留删除后进行的数据修改。

演练恢复方式：test库和score表在半年前就建好了，而且binlog设置了过期时间15天，所以binlog建库建表的binlog早已不在！
所以这里考虑通过完整备份和binlog的方式进行恢复。

二、Mysql数据误删演练（准备数据）

①、准备测试库test、测试表score

mysql> select * from score
    -> ;
+----+------+--------+-------+
| id | name | course | grade |
+----+------+--------+-------+
|  1 | 张三 | 语文   |    89 |
|  2 | 张三 | 数学   |    77 |
|  3 | 张三 | 计算机 |    65 |
|  4 | 李四 | 语文   |    95 |
|  5 | 李四 | 数学   |    90 |
|  6 | 李四 | 计算机 |    88 |
|  7 | 王五 | 语文   |    66 |
|  8 | 王五 | 数学   |    85 |
|  9 | 王五 | 计算机 |    94 |
+----+------+--------+-------+
9 rows in set (0.10 sec)

②、备份数据库test

注：mysql安装后设置过环境变量，所以可以直接使用mysqldump工具，如果未设置环境变量，需要制定mysqldump绝对路径，密码替换为*****。

[root@localhost mysql]# mysqldump -uroot -p***** test >/usr/local/src/test.sql

查看下备份文件内容（如果数据库较大不建议cat查看）

[root@localhost mysql]# cat /usr/local/src/test.sql 
-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.31-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `score`
--

DROP TABLE IF EXISTS `score`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL COMMENT '学生姓名',
  `course` varchar(50) DEFAULT NULL COMMENT '课程名称',
  `grade` int(11) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `score`
--

LOCK TABLES `score` WRITE;
/*!40000 ALTER TABLE `score` DISABLE KEYS */;
INSERT INTO `score` VALUES (1,'张三','语文',89),(2,'张三','数学',77),(3,'张三','计算机',65),(4,'李四','语文',95),(5,'李四','数学',90),(6,'李四','计算机',88),(7,'王五','语文',66),(8,'王五','数学',85),(9,'王五','计算机',94);
/*!40000 ALTER TABLE `score` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-01 15:16:18

以上可以看出备份出来的脚本包含了，test库建库、建表、插入score表数据的所有信息。

③、模拟误删除数据

mysql> delete from score where id<7;
Query OK, 6 rows affected (0.01 sec)

mysql> select * from score;
+----+------+--------+-------+
| id | name | course | grade |
+----+------+--------+-------+
|  7 | 王五 | 语文   |    66 |
|  8 | 王五 | 数学   |    85 |
|  9 | 王五 | 计算机 |    94 |
+----+------+--------+-------+
3 rows in set (0.08 sec)

④、修改id等于8的数据将grade 修改为100

注：这一步主要考虑，误删数据后

mysql> update score set grade='100' where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from score
    -> ;
+----+------+--------+-------+
| id | name | course | grade |
+----+------+--------+-------+
|  7 | 王五 | 语文   |    66 |
|  8 | 王五 | 数学   |   100 |
|  9 | 王五 | 计算机 |    94 |
+----+------+--------+-------+
3 rows in set (0.09 sec)

以上，模拟了数据表数据误删除，误删除后表数据也进行了一些业务处理。

三、模拟：通过全备恢复误删除数据

注，此场景属于建库、建表、以及插入基础数据在做全量备份之前，全量备份之后仅做了少数数据值的修改、或者插入了新的值！

①、通过全备test.sql文件导出score表insert into语句

[root@localhost mysql]# grep 'INSERT INTO `score`' /usr/local/src/test.sql
INSERT INTO `score` VALUES (1,'张三','语文',89),(2,'张三','数学',77),(3,'张三','计算机',65),(4,'李四','语文',95),(5,'李四','数学',90),(6,'李四','计算机',88),(7,'王五','语文',66),(8,'王五','数学',85),(9,'王五','计算机',94);

②、insert into 数据到score表

insert into 报错：Duplicate entry ‘7’ for key ‘PRIMARY’

mysql> INSERT INTO `score` VALUES (1,'张三','语文',89),(2,'张三','数学',77),(3,'张三','计算机',65),(4,'李四','语文',95),(5,'李四','数学',90),(6,'李四','计算机',88),(7,'王五','语文',66),(8,'王五','数学',85),(9,'王五','计算机',94);
1062 - Duplicate entry '7' for key 'PRIMARY'

解决办法：
1.IGNORE

INSERT IGNORE INTO Table_name(…..) VALUES(1,1),(2,2),(3,3);

使用IGNORE，如果插入的记录中存在重复值会忽略重复值的该记录行，不影响其他行的插入。
不影响已存在或已修改过的数据！
2.REPLACE

REPLACE INTO Table_name() VALUES(1,1),(2,2),(3,3)

使用replace当插入的记录遇到主键或者唯一重复时先删除表中重复的记录行再插入
3.*ON DUPLICATE KEY UPDATE *NAME1=VALUES(ID)+1

INSERT TO Table_name() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;

ON DUPLICATE KEY UPDATE后面使用VALUES指的是插入记录的值，而不使用VALUES指的是表的自身值。其后执行的UPDATE更新的记录是WHERE重复的主键或唯一键的ID。

以上三种方法均支持标准的INSERT语法，包括INSERT INTO…VALUES,INSERT INTO…SET,INSERT INTO…SELECT

这里我们使用IGNORE，忽略已有的值。

再次插入数据。

mysql> INSERT IGNORE INTO `score` VALUES (1,'张三','语文',89),(2,'张三','数学',77),(3,'张三','计算机',65),(4,'李四','语文',95),(5,'李四','数学',90),(6,'李四','计算机',88),(7,'王五','语文',66),(8,'王五','数学',85),(9,'王五','计算机',94);
Query OK, 6 rows affected (0.01 sec)
Records: 9  Duplicates: 3  Warnings: 3

mysql> select * from score;
+----+------+--------+-------+
| id | name | course | grade |
+----+------+--------+-------+
|  1 | 张三 | 语文   |    89 |
|  2 | 张三 | 数学   |    77 |
|  3 | 张三 | 计算机 |    65 |
|  4 | 李四 | 语文   |    95 |
|  5 | 李四 | 数学   |    90 |
|  6 | 李四 | 计算机 |    88 |
|  7 | 王五 | 语文   |    66 |
|  8 | 王五 | 数学   |   100 |
|  9 | 王五 | 计算机 |    94 |
+----+------+--------+-------+
9 rows in set (0.11 sec)

四、模拟：基于全量备份和binlog恢复误删除数据

注，此场景主要模拟误删除的数据在做全量备份之后，所以只能基于binlog进行恢复。
恢复过程：
第一步、先恢复全量备份中score表的数据，可以参见第三步。这里就把全量备份之前的数据恢复出来了。
第二步、基于binlog恢复全量备份到误删除数据这期间的数据。

①、flush logs;

重新生成binlog日志文件，以免第三步实验影响后续操作！
查看当前记录的binlog日志文件

mysql> show master status; 
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000067 |     6353 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.07 sec)

刷新日志，确保实验环境干净。重新记录binlog日志到binlog.000068 文件

mysql> select * from score;
+----+------+--------+-------+
| id | name | course | grade |
+----+------+--------+-------+
|  1 | 张三 | 语文   |    89 |
|  2 | 张三 | 数学   |    77 |
|  3 | 张三 | 计算机 |    65 |
|  4 | 李四 | 语文   |    95 |
|  5 | 李四 | 数学   |    90 |
|  6 | 李四 | 计算机 |    88 |
|  7 | 王五 | 语文   |    66 |
|  8 | 王五 | 数学   |    85 |
|  9 | 王五 | 计算机 |    94 |
+----+------+--------+-------+
9 rows in set (0.09 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status; 
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000068 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)

②、插入新的数据（不做全量备份）

注：因为在未刷新日志前插入过一次数据，重新插入数据时没考虑主键的情况，这里就不在处理了。

mysql> INSERT INTO `score` (name,course,grade)VALUES ('孙悟空','语文',50),('孙悟空','数学',60),('孙悟空','计算机',70);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    89 |
|  2 | 张三   | 数学   |    77 |
|  3 | 张三   | 计算机 |    65 |
|  4 | 李四   | 语文   |    95 |
|  5 | 李四   | 数学   |    90 |
|  6 | 李四   | 计算机 |    88 |
|  7 | 王五   | 语文   |    66 |
|  8 | 王五   | 数学   |    85 |
|  9 | 王五   | 计算机 |    94 |
| 13 | 孙悟空 | 语文   |    50 |
| 14 | 孙悟空 | 数学   |    60 |
| 15 | 孙悟空 | 计算机 |    70 |
+----+--------+--------+-------+
12 rows in set (0.09 sec)

③、模拟误操作，删除id小于15的值

注：由于新插入的数据未做全量备份，所以只能基于binlog进行恢复。

mysql> delete from score where id< 15;
Query OK, 11 rows affected (0.01 sec)

mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
| 15 | 孙悟空 | 计算机 |    70 |
+----+--------+--------+-------+
1 row in set (0.10 sec)

④、修改id等于15的grade等于120

mysql> update score set grade=120 where id=15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
| 15 | 孙悟空 | 计算机 |   120 |
+----+--------+--------+-------+
1 row in set (0.08 sec)

⑤、通过全量备份恢复旧数据（参考第三步）

参考：《三、通过全备恢复误删除数据》，从第一次全量备份中提取非今天插入score表的数据，将提出来的Sql复制出来再执行一次。或者通过source的方式执行。

mysql> INSERT IGNORE INTO `score` VALUES (1,'张三','语文',89),(2,'张三','数学',77),(3,'张三','计算机',65),(4,'李四','语文',95),(5,'李四','数学',90),(6,'李四','计算机',88),(7,'王五','语文',66),(8,'王五','数学',85),(9,'王五','计算机',94);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    89 |
|  2 | 张三   | 数学   |    77 |
|  3 | 张三   | 计算机 |    65 |
|  4 | 李四   | 语文   |    95 |
|  5 | 李四   | 数学   |    90 |
|  6 | 李四   | 计算机 |    88 |
|  7 | 王五   | 语文   |    66 |
|  8 | 王五   | 数学   |    85 |
|  9 | 王五   | 计算机 |    94 |
| 15 | 孙悟空 | 计算机 |   120 |
+----+--------+--------+-------+
10 rows in set (0.09 sec)

注：以上可以看出score表仅恢复了全备的数据，今天插入的数据未恢复出来。

⑥、通过binlog恢复今天插入的数据

6.1 刷新binlog日志

注：在恢复前先执行第一步flush logs; 避免因恢复过程操作binlog记录不必要的日志内容。
注：默认记录的binlog日志自动加有注释，参考：https://www.php.cn/mysql-tutorials-352210.html

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; 
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000069 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.08 sec)

6.2 查看binlog 中记录的事件

mysql> show binlog events in 'binlog.000068';
+---------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+---------------+------+----------------+-----------+-------------+---------------------------------------+
| binlog.000068 |    4 | Format_desc    |        11 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| binlog.000068 |  123 | Previous_gtids |        11 |         154 |                                       |
| binlog.000068 |  154 | Anonymous_Gtid |        11 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000068 |  219 | Query          |        11 |         291 | BEGIN                                 |
| binlog.000068 |  291 | Table_map      |        11 |         346 | table_id: 821 (test.score)            |
| binlog.000068 |  346 | Write_rows     |        11 |         462 | table_id: 821 flags: STMT_END_F       |
| binlog.000068 |  462 | Xid            |        11 |         493 | COMMIT /* xid=62210 */                |
| binlog.000068 |  493 | Anonymous_Gtid |        11 |         558 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000068 |  558 | Query          |        11 |         630 | BEGIN                                 |
| binlog.000068 |  630 | Table_map      |        11 |         685 | table_id: 821 (test.score)            |
| binlog.000068 |  685 | Delete_rows    |        11 |         988 | table_id: 821 flags: STMT_END_F       |
| binlog.000068 |  988 | Xid            |        11 |        1019 | COMMIT /* xid=62214 */                |
| binlog.000068 | 1019 | Anonymous_Gtid |        11 |        1084 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000068 | 1084 | Query          |        11 |        1156 | BEGIN                                 |
| binlog.000068 | 1156 | Table_map      |        11 |        1211 | table_id: 821 (test.score)            |
| binlog.000068 | 1211 | Update_rows    |        11 |        1305 | table_id: 821 flags: STMT_END_F       |
| binlog.000068 | 1305 | Xid            |        11 |        1336 | COMMIT /* xid=62218 */                |
| binlog.000068 | 1336 | Anonymous_Gtid |        11 |        1401 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000068 | 1401 | Query          |        11 |        1473 | BEGIN                                 |
| binlog.000068 | 1473 | Table_map      |        11 |        1528 | table_id: 821 (test.score)            |
| binlog.000068 | 1528 | Write_rows     |        11 |        1779 | table_id: 821 flags: STMT_END_F       |
| binlog.000068 | 1779 | Xid            |        11 |        1810 | COMMIT /* xid=62222 */                |
+---------------+------+----------------+-----------+-------------+---------------------------------------+
22 rows in set (0.15 sec)

6.3 通过mysqlbinlog工具查看具体信息

在这里插入图片描述

[root@localhost binlog]# mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d test --start-position=219 --stop-position=493 binlog.000068 > /tmp/score.sql

–no-defaults 避免报错
-v –verbose(或-v)，将自动生成带注释的SQL语句
–base64-output=‘decode-rows’ 查看row 模式的binlog
-d test 只分析指定数据库，如果有的服务器挂了N多个数据库。可以通过-d 进行过滤
–start-position=219 mysql事务Begin 的Pos点。
–stop-position=493 通过show binlog events in ‘binlog.000068’; 查看binlog事件记录，中End_log_pos 的值，这个取Write_Row事务COMMIT的结束点。

查看导出的Score.sql 文件！

[root@localhost binlog]# cat /tmp/score.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 219
#210901 16:01:12 server id 11  end_log_pos 291 CRC32 0x77cf485d 	Query	thread_id=45	exec_time=0	error_code=0
SET TIMESTAMP=1630483272/*!*/;
SET @@session.pseudo_thread_id=45/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#210901 16:01:12 server id 11  end_log_pos 346 CRC32 0x765547de 	Table_map: `test`.`score` mapped to number 821
# at 346
#210901 16:01:12 server id 11  end_log_pos 462 CRC32 0xc28bd9fb 	Write_rows: table id 821 flags: STMT_END_F
### INSERT INTO `test`.`score`
### SET
###   @1=13
###   @2='孙悟空'
###   @3='语文'
###   @4=50
### INSERT INTO `test`.`score`
### SET
###   @1=14
###   @2='孙悟空'
###   @3='数学'
###   @4=60
### INSERT INTO `test`.`score`
### SET
###   @1=15
###   @2='孙悟空'
###   @3='计算机'
###   @4=70
# at 462
#210901 16:01:12 server id 11  end_log_pos 493 CRC32 0x8d357266 	Xid = 62210
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

6.4 恢复Inert into 数据

[root@localhost binlog]# /usr/local/mysql/bin/mysqlbinlog --start-position=219 --stop-position=493 --database=test /data/log/binlog/binlog.000068 |mysql -uroot -f -p85442791 -P3301 -v test

恢复时报主键冲突，耗好长时间才处理好。

[root@localhost binlog]# mysqlbinlog --start-position=219 --stop-position=493 /data/log/binlog/binlog.000068 |mysql -uroot -p85442791
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 36: Duplicate entry '15' for key 'PRIMARY'

报主键冲突数据一直没有重新insert into进去，因为恢复的时候会重新插入13、14、15这三行数据，而恰巧ID等于15的数据还在，不知道为什么13和14也一直没插入进去。
这里重新删除ID等于15的数据，再通过binlog重新回滚就可以了。

mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    89 |
|  2 | 张三   | 数学   |    77 |
|  3 | 张三   | 计算机 |    65 |
|  4 | 李四   | 语文   |    95 |
|  5 | 李四   | 数学   |    90 |
|  6 | 李四   | 计算机 |    88 |
|  7 | 王五   | 语文   |    66 |
|  8 | 王五   | 数学   |    85 |
|  9 | 王五   | 计算机 |    94 |
| 13 | 孙悟空 | 语文   |    50 |
| 14 | 孙悟空 | 数学   |    60 |
| 15 | 孙悟空 | 计算机 |    70 |
+----+--------+--------+-------+
12 rows in set (0.10 sec)

那这里还有一个问题，id等于15的grade值我们修改过，那恢复之后数据就变成原来的样子了。
解决方法：
继续使用binlog恢复数据只是将binlog中记录delete 数据的记录跳过就可以了。

⑦、通过binlog回滚update的数据

7.1 查看位置

在这里插入图片描述

7.2 通过binlog重新执行update

使用mysqlbinlog进行回滚

[root@localhost binlog]# /usr/local/mysql/bin/mysqlbinlog --start-position=1084 --stop-position=1336 --database=test /data/log/binlog/binlog.000068 |mysql -uroot -f -p85442791 -P3301 -v test
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

--------------
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
--------------

--------------
BINLOG '
BjMvYQ8LAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Adkq1/E=
'
--------------

--------------
SET TIMESTAMP=1630483422
--------------

--------------
SET @@session.pseudo_thread_id=45
--------------

--------------
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1
--------------

--------------
SET @@session.sql_mode=1436549152
--------------

--------------
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1
--------------

--------------
/*!*/
--------------

--------------
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33
--------------

--------------
SET @@session.lc_time_names=0
--------------

--------------
SET @@session.collation_database=DEFAULT
--------------

--------------
BEGIN
--------------

--------------
BINLOG '
3jMvYRMLAAAANwAAALsEAAAAADUDAAAAAAEABHRlc3QABXNjb3JlAAQDDw8DBFoAlgAMVXmz2A==
3jMvYR8LAAAAXgAAABkFAAAAADUDAAAAAAEAAgAE///wDwAAAAnlrZnmgp/nqboJ6K6h566X5py6
RgAAAPAPAAAACeWtmeaCn+epugnorqHnrpfmnLp4AAAAM/HSzg==
'
--------------

--------------
COMMIT
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

--------------
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
--------------

--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
--------------

7.3 数据回滚前后对比

####  回滚前
mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    89 |
|  2 | 张三   | 数学   |    77 |
|  3 | 张三   | 计算机 |    65 |
|  4 | 李四   | 语文   |    95 |
|  5 | 李四   | 数学   |    90 |
|  6 | 李四   | 计算机 |    88 |
|  7 | 王五   | 语文   |    66 |
|  8 | 王五   | 数学   |    85 |
|  9 | 王五   | 计算机 |    94 |
| 13 | 孙悟空 | 语文   |    50 |
| 14 | 孙悟空 | 数学   |    60 |
| 15 | 孙悟空 | 计算机 |    70 |
+----+--------+--------+-------+
12 rows in set (0.10 sec)
### 回滚后
mysql> select * from score;
+----+--------+--------+-------+
| id | name   | course | grade |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    89 |
|  2 | 张三   | 数学   |    77 |
|  3 | 张三   | 计算机 |    65 |
|  4 | 李四   | 语文   |    95 |
|  5 | 李四   | 数学   |    90 |
|  6 | 李四   | 计算机 |    88 |
|  7 | 王五   | 语文   |    66 |
|  8 | 王五   | 数学   |    85 |
|  9 | 王五   | 计算机 |    94 |
| 13 | 孙悟空 | 语文   |    50 |
| 14 | 孙悟空 | 数学   |    60 |
| 15 | 孙悟空 | 计算机 |   120 |
+----+--------+--------+-------+
12 rows in set (0.12 sec)

注：binlog记录的是数据库的update、delete、create等记录，如果数据库不做备份那么恢复数据需要从第一个binlog日志记录开始恢复，所以建议还是做好全备。曾经一度以为不用做全量备份，可以通过binlog恢复整个库和表的数据（也不是不可以，只要你的binlog从建库开始就一直保存的有）！