【MySQL】binlog数据恢复

binlog概述

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。binlog 是逻辑日志，记录的是这个语句的原始逻辑。

查看binlog相关参数

show variables like '%log_bin%';

在这里插入图片描述

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

开启binlog

在配置文件中的[mysqld]部分增加如下配置：

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

binlog_format 可以设置binlog日志的记录格式

  • STATEMENT： 基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
  • ROW： 基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
  • MIXED： 混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

重启数据库后我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

在这里插入图片描述

再次运行show variables like '%log_bin%';

在这里插入图片描述

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

biglog数据恢复

示例表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, 'zhangsan', 25, '2023-11-10 10:42:14');
INSERT INTO `user` VALUES (2, 'wanglei', 23, '2023-11-10 10:42:32');

第一步： user表中再插入2条数据

INSERT INTO `user` VALUES (3, 'ceshi', 26, '2023-11-27 15:24:46');
INSERT INTO `user` VALUES (4, 'ceshi2', 20, '2023-11-27 15:24:59');

第二步： 删除ceshi、ceshi2这两条数据

DELETE FROM `user` WHERE id in (3,4);

在这里插入图片描述

第三步： 可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容。

首先进入MySQL的data目录，执行以下命令可以查询到binlog日志内容（我这里的日志文件是mysql-binlog.000001）。

mysqlbinlog --no-defaults -v --base64-output=decode-rows mysql-binlog.000001

查出来的binlog日志文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#231127 15:15:04 server id 1  end_log_pos 123 CRC32 0xe2386638  Start: binlog v 4, server v 5.7.35-log created 231127 15:15:04 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#231127 15:15:04 server id 1  end_log_pos 154 CRC32 0x8fa78a64  Previous-GTIDs
# [empty]
# at 154
#231127 15:24:44 server id 1  end_log_pos 219 CRC32 0x4fbeb3af  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#231127 15:24:44 server id 1  end_log_pos 291 CRC32 0x6624a049  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069884/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
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
#231127 15:24:44 server id 1  end_log_pos 344 CRC32 0x2f918688  Table_map: `test`.`user` mapped to number 111
# at 344
#231127 15:24:44 server id 1  end_log_pos 394 CRC32 0x315b331e  Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4=NULL
# at 394
#231127 15:24:44 server id 1  end_log_pos 425 CRC32 0xb8321e42  Xid = 18
COMMIT/*!*/;
# at 425
#231127 15:24:48 server id 1  end_log_pos 490 CRC32 0x58c8ae2b  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 490
#231127 15:24:48 server id 1  end_log_pos 562 CRC32 0x0c7ca1e3  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069888/*!*/;
BEGIN
/*!*/;
# at 562
#231127 15:24:48 server id 1  end_log_pos 615 CRC32 0xfc294813  Table_map: `test`.`user` mapped to number 111
# at 615
#231127 15:24:48 server id 1  end_log_pos 686 CRC32 0x4ae8d2b3  Update_rows: table id 111 flags: STMT_END_F
### UPDATE `test`.`user`
### WHERE
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4=NULL
### SET
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4='2023-11-27 15:24:46'
# at 686
#231127 15:24:48 server id 1  end_log_pos 717 CRC32 0x79b19082  Xid = 21
COMMIT/*!*/;
# at 717
#231127 15:25:01 server id 1  end_log_pos 782 CRC32 0xfee1225a  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 782
#231127 15:25:01 server id 1  end_log_pos 854 CRC32 0x46e269a7  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069901/*!*/;
BEGIN
/*!*/;
# at 854
#231127 15:25:01 server id 1  end_log_pos 907 CRC32 0x48d00e0f  Table_map: `test`.`user` mapped to number 111
# at 907
#231127 15:25:01 server id 1  end_log_pos 963 CRC32 0x606fa283  Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
###   @1=4
###   @2='ceshi2'
###   @3=20
###   @4='2023-11-27 15:24:59'
# at 963
#231127 15:25:01 server id 1  end_log_pos 994 CRC32 0x699d3676  Xid = 24
COMMIT/*!*/;
# at 994
#231127 15:30:37 server id 1  end_log_pos 1059 CRC32 0xe3e1102e         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1059
#231127 15:30:37 server id 1  end_log_pos 1131 CRC32 0x77864565         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1701070237/*!*/;
BEGIN
/*!*/;
# at 1131
#231127 15:30:37 server id 1  end_log_pos 1184 CRC32 0xf290d665         Table_map: `test`.`user` mapped to number 111
# at 1184
#231127 15:30:37 server id 1  end_log_pos 1260 CRC32 0xfe6635ec         Delete_rows: table id 111 flags: STMT_END_F
### DELETE FROM `test`.`user`
### WHERE
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4='2023-11-27 15:24:46'
### DELETE FROM `test`.`user`
### WHERE
###   @1=4
###   @2='ceshi2'
###   @3=20
###   @4='2023-11-27 15:24:59'
# at 1260
#231127 15:30:37 server id 1  end_log_pos 1291 CRC32 0xb85a735a         Xid = 72
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*/;

第四步： 数据恢复

上面的内容，明显可以看出sql语句的执行情况。

找到我们之前插入的数据，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql，BEGIN后的第一个文件位置标识 at 291(这是文件的位置标识)，再找到第二条sql，COMMIT后面第一个文件位置标识 at 994

我们可以根据文件位置标识来恢复数据，首先要切换到MySQL的data目录下，执行如下命令：

# -u数据库账号 -p数据库密码  -v 要恢复的数据库名称（我这里是test数据库）
mysqlbinlog  --no-defaults --start-position=291 --stop-position=994 --database=test mysql-binlog.000001 | mysql -uroot -p123456 -v test

执行上面的命令后，就可以查询到被删除的数据了

在这里插入图片描述

补充数据恢复命令

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test mysql-binlog.000001 | mysql -uroot -p123456 -v test

如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/204915.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

基于web宠颐生宠物医院系统设计与实现

基于web宠颐生宠物医院系统设计与实现

基于web宠颐生医院系统开发与实现 摘要&#xff1a;时代飞速发展&#xff0c;网络也飞速发展&#xff0c;互联网许多的行业都可以用互联网实现了&#xff0c;互联网已经成为了人们生活中重要的一部分&#xff0c;或多或少的影响着我们的生活&#xff0c;互联网在给我带了方便的…
阅读更多...
【UE】透视效果

【UE】透视效果

效果 步骤 1. 新建一个空白工程 2. 添加一个第三人称游戏和初学者内容包到内容浏览器 3. 新建一个材质&#xff0c;这里命名为“M_Perspective” 打开“M_Perspective”&#xff0c;设置材质域为后期处理 添加三个“SceneTexture”节点&#xff0c;场景纹理ID选项分别设置为“…
阅读更多...
netcore 获取应用程序或者站点根路径的一点知识和教训

netcore 获取应用程序或者站点根路径的一点知识和教训

最近在用abpvnext做报表导出&#xff0c;涉及到要在站点根目录生成pdf文件提供下载。于是就要获取站点根路径。 开头搜索资料提示用IWebHostEnvironment.ContentRootPath&#xff0c;来实现获取站点根目录。这个其实是正解。.netcore的通用规则&#xff0c;使用任何借口都是依…
阅读更多...
Oracle（2-8）Configuring the Database Archiving Mode

Oracle（2-8）Configuring the Database Archiving Mode

文章目录 一、基础知识1、Redo Log History2、NOARCHIVELOG Mode 非归档模式3、ARCHIVELOG Mode 归档模式4、Changing the Archiving Mode 更改归档模式![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/d6a09f9a6de24de7bbcdad90b8d6b9ca.png)5、Auto and Manual Ar…
阅读更多...
ZZULIOJ 2466: 楼上瞎说，楼下才是，Java

ZZULIOJ 2466: 楼上瞎说，楼下才是，Java

2466: 楼上瞎说&#xff0c;楼下才是 题目描述 《九章算术》的内容十分丰富&#xff0c;全书采用问题集的形式&#xff0c;收有246个与生产、生活实践有联系的应用问题&#xff0c;其中每道题有问&#xff08;题目&#xff09;、答&#xff08;答案&#xff09;、术&#xff…
阅读更多...
剑指offer(C++)-JZ43：整数中1出现的次数(算法-其他)

剑指offer(C++)-JZ43：整数中1出现的次数(算法-其他)

作者&#xff1a;翟天保Steven 版权声明&#xff1a;著作权归作者所有&#xff0c;商业转载请联系作者获得授权&#xff0c;非商业转载请注明出处 题目描述&#xff1a; 输入一个整数 n &#xff0c;求 1&#xff5e;n 这 n 个整数的十进制表示中 1 出现的次数 例如&#xff0…
阅读更多...
字符串的旋转

字符串的旋转

字符串的旋转 左旋&#xff08;逆时针&#xff09; 示例&#xff1a;abcd------>bcda 右旋&#xff08;顺时针&#xff09; 示例&#xff1a;abcd------>dabc 例&#xff1a; 输入若干个字符串&#xff08;1≤长度≤1000&#xff09;右旋转串后的n&#xff08;-长度…
阅读更多...
性能测试：系统架构性能优化

性能测试：系统架构性能优化

今天谈下业务系统性能问题分析诊断和性能优化方面的内容。这篇文章重点还是谈已经上线的业务系统后续出现性能问题后的问题诊断和优化重点。 系统性能问题分析流程 我们首先来分析下如果一个业务系统上线前没有性能问题&#xff0c;而在上线后出现了比较严重的性能问题&#x…
阅读更多...
【人工智能Ⅰ】实验6：回归预测实验

【人工智能Ⅰ】实验6：回归预测实验

实验6 回归预测实验 一、实验目的 1&#xff1a;了解机器学习中数据集的常用划分方法以及划分比例&#xff0c;并学习数据集划分后训练集、验证集及测试集的作用。 2&#xff1a;了解降维方法和回归模型的应用。 二、实验要求 数据集&#xff08;LUCAS.SOIL_corr-实验6数据…
阅读更多...
（六）基于高尔夫优化算法GOA求解无人机三维路径规划研究（MATLAB代码）

（六）基于高尔夫优化算法GOA求解无人机三维路径规划研究（MATLAB代码）

一、无人机模型简介&#xff1a; 单个无人机三维路径规划问题及其建模_IT猿手的博客-CSDN博客 参考文献&#xff1a; [1]胡观凯,钟建华,李永正,黎万洪.基于IPSO-GA算法的无人机三维路径规划[J].现代电子技术,2023,46(07):115-120 二、高尔夫优化算法GOA简介 高尔夫优化算法…
阅读更多...
防火墙补充NAT

防火墙补充NAT

目录 1.iptables保存规则 2.自定义链 3.NAT NAT的实现分为下面类型&#xff1a; SNAT实验操作 DNAT实验操作 1.iptables保存规则 永久保存方法一&#xff1a; iptables -save > /data/iptables_rule //输出重定向备份 iptables -restore < /data/iptables_r…
阅读更多...
​[Oracle]编写程序，键盘输入n,计算1+前n项之和。测试案例：输入：10 输出：22.47​

​[Oracle]编写程序，键盘输入n,计算1+前n项之和。测试案例：输入：10 输出：22.47​

编写程序&#xff0c;键盘输入n,计算1前n项之和。 测试案例&#xff1a; 输入&#xff1a;10 输出&#xff1a;22.47 代码如下&#xff1a; set serveroutput on declare v_sum number:0;v_n number;beginv_n:&n;for i in 1..v_n loopv_sum:v_sumsqrt(i); end loop; d…
阅读更多...
View绘制

View绘制

onDraw 绘制 canvas 画布 paint 画笔 坐标系 x y x 0 y 0 则屏幕左上角 y从上往下值增加 像素转换 dp2px 画线line drawLine 圆circle drawCircle drawPath: 在onSizeChanged 时候初始化 addCircle 添加圆 CW顺时针 CCW 逆时针 CW CCW填充规则不同 填充规则: 默认 …
阅读更多...
关于项目时间与数据库中的时间不一致问题(少8个小时)

关于项目时间与数据库中的时间不一致问题(少8个小时)

关于项目情况: 1.springboot项目 2.数据库为MySQL 3.数据库时间正常,与实际时间一致. 4.项目获取到的时间比数据库的时间少八个小时 原因是没有给日期格式设置时区,导致其变为世界时,比北京时间少八个小时 在application.yml 配置文件中添加时区属性; 配置文件路径 spri…
阅读更多...
【复位与释放（亚稳态）模为60的BCD码计数器_2023.11.22】

【复位与释放（亚稳态）模为60的BCD码计数器_2023.11.22】

复位与释放&#xff08;异步复位&#xff0c;同步释放&#xff09; 同步复位rst、同步置数load&#xff08;置数信号只有在时钟上升沿到来时才能生效&#xff09;、同步清零clr 同步复位&#xff1a; always(posedge clk) if(!rst_n) b<1’b0; else b<a; 同步复位信号rs…
阅读更多...
〔005〕虚幻 UE5 像素流多用户部署

〔005〕虚幻 UE5 像素流多用户部署

✨ 目录 ▷ 为什么要部署多用户▷ 开启分发服务器▷ 配置启动多个信令服务器▷ 配置启动客户端▷ 多用户启动整体流程和预览▷ 注意事项 ▷ 为什么要部署多用户 之前的像素流部署&#xff0c;属于单用户&#xff0c;是有很大的弊端的打开多个窗口访问&#xff0c;可以看到当一…
阅读更多...
Linux 命令pwd

Linux 命令pwd

命令作用 pwd是Linux中一个非常有用而又十分简单的命令&#xff0c;pwd是词组print working directory的首字母缩写&#xff0c;即打印工作目录&#xff1b;工作目录就是你当前所处于的那个目录。 pwd始终以绝对路径的方式打印工作目录&#xff0c;即从根目录&#xff08;/&am…
阅读更多...
百度地图JavaScript API GL获取经纬度，标记，添加文本标注，点击事件，封装

百度地图JavaScript API GL获取经纬度，标记，添加文本标注，点击事件，封装

百度地图JavaScript API GL常用方法封装 引入百度js库 <script type"text/javascript" src"https://api.map.baidu.com/api?v1.0&typewebgl&ak自己的百度应用ak"></script>封装方法 <template><div class"map"&…
阅读更多...
Vue3生命周期函数（简述题）

Vue3生命周期函数（简述题）

1.图示 2.说明 3.补充 1.在vue3组合式API中&#xff0c;我们需要将生命周期函数先导入&#xff0c;然后才能使用。 import {onMounted} from vue2.beforeCreate和created被setup()方法所代替
阅读更多...
CTO对生活和工作一点感悟

CTO对生活和工作一点感悟

陌生人&#xff0c;你好啊。 感谢CSDN平台让我们有了隔空认识&#xff0c;交流的机会。 我是谁&#xff1f; 我呢&#xff0c;毕业快11年&#xff0c;在网易做了几年云计算&#xff0c;后来追风赶上了大数据的浪潮&#xff0c;再到后来混迹在AI、智能推荐等领域。 因为有一颗…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站