win10中mysql数据库binlog恢复

昨天有朋友江湖救急，说测试库里的表不小心删除更新了数据。这里也复习下binlog数据恢复，当然需要一定的条件：首先mysql开启binlog，然后每天需要备份对应的数据库

1 单库单表准备

在恢复数据前，先场景复现

1.1 查看二进制日志是否已开启

show variables like 'log_%'; 

-- 常用binlog日志操作命令
--   1.查看所有binlog日志列表
     show master logs;
--   2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
     show master status;
--   3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
     flush logs;
--   	注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
--   4.重置(清空)所有binlog日志
--      reset master;
--   5.查看具体的恢复点
    show binlog events in 'binlog.000118';

在这里插入图片描述

1.2 数据库准备

create database db10;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `search_count` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `user` VALUES (1,'张三',23,25),(2,'李四',24,5),(3,'王五',25,3),(4,'赵六',26,7),(5,'张三丰',93,20),(6,'张无忌',18,23),(7,'张强',33,21),(8,'张果老',65,6);

1.3 备份数据库

在cmd中的mysql执行目录中，运行备份命令

E:\Hadoop_win\Mysql_8\mysql-8.0.29-winx64\bin>mysqldump -uroot -p123456 -B db10 > E:\Hadoop_win\Mysql_8\data\BAK.db10.sql

1.4 更新数据库表

在备份数据库后，进行库表user的新增更新操作

-- 新增
INSERT INTO db10.`user`
(id, NAME, age, search_count)
VALUES(9, 'TOM', 28, 3);
INSERT INTO db10.`user`
(id, NAME, age, search_count)
VALUES(10, 'Jerry', 24, 6);
-- 更新
update db10.`user`
set NAME = 'Joes'  where id = 2;
update db10.`user`
set NAME = 'Tim'  where id = 3;
select * from db10.`user`;
-- 删除
delete from db10.`user` where id = 5;

2 单库单表恢复

单库单表恢复实现：先从备份恢复数据库，然后从binlog的位置点后续更新恢复数据

2.1 数据库每日备份恢复

在cmd中的mysql执行目录中，运行备份命令恢复

E:\Hadoop_win\Mysql_8\mysql-8.0.29-winx64\bin>mysql -uroot -p123456 -v < E:\Hadoop_win\Mysql_8\data\BAK.db10.sql

2.2 从binlog的位置点后续更新恢复数据

查看所有binlog日志列表

show master logs;

在这里插入图片描述

使用mysql语句查看binlog操作记录点

show binlog events in 'binlog.000118';

在这里插入图片描述

在cmd中的mysql执行目录中，运行命令恢复

E:\Hadoop_win\Mysql_8\mysql-8.0.29-winx64\bin>mysqlbinlog --no-defaults  --stop-position=2029 --database=db10 E:\Hadoop_win\Mysql_8\data\binlog.000118 | E:\Hadoop_win\Mysql_8\mysql-8.0.29-winx64\bin\mysql -uroot -p123456 -v db10