当前位置： 首页 > article >正文

如何通过BinLog日志恢复被删除的数据

article 2025/3/29 15:32:12

https://blog.csdn.net/TGQlovemm/article/details/144693018

一次惨痛的经历，实习期间不小心删除了生产上的角色菜单，通过备份紧急恢复了部分数据，但是还是漏了很多，最后导致客户使用的时候权限出现了问题。对此我抽时间了解了一下关于数据库恢复的相关知识，为了以后出现这种错误之后能以最小损失的代价恢复数据。

binlog文件实际上是对于数据库的正向操作
插入数据insert,binlog中保存的也是insert语句
删除数据delete,binlog中保存的也是delete语句


由此恢复数据的两种方式：
1.找到数据的插入位置，重新执行数据的插入操作
优点：方便，不需要生成逆向操作，直接执行sql脚本重新插入数据即可，对binlog的模式没有限制，row模式，statement模式都能找到具体的数据
缺点：如果插入之后还有更新操作，插入的数据不是最新的，会有问题。如果被删除的数据比较多，插入的位置比较多，找到插入的位置比较困难。
2.找到数据被删除的位置，生成逆向操作，重新执行插入操作
优点：只要找到数据被删除的位置，即可找到所有被删除的数据，比较方便。
缺点：需要通过脚本生成逆向操作，才能将数据恢复，需要保证binlog是row模式，才能找到被删除的数据，否则，statement模式不会找到具体的数据。

通用操作：
检查是否开启了binlog：
SHOW VARIABLES LIKE 'log_bin';
ON 开启 OFF关闭
开启binlog
修改配置文件（my.cnf/my.ini）
linux下MySql的配置文件目录一般是/etc/mysql中的[mysqld]部分来开启binlog
[mysqld]
log-bin=mysql-bin
server-id=1

binlog模式：
SHOW VARIABLES LIKE 'binlog_format';
ROW:行模式，因为它提供了更好的数据一致性。
STATEMENT:表示使用语句模式，这种模式可能会丢失一些数据，因为它仅记录执行的sql语句
MIXED:表示混合模式，在这种模式下，mysql会根据需要自动切换模式和语句模式
配置binlog模式：
同上
[mysqld]
binlog_format=ROW


binlog信息查询：
查询当前使用的binlog文件
show master status; 可以找到当前正在使用的binlog文件
show master logs;可以找到所有binlog文件名
show VARIABLES LIKE 'log_bin_basename';可以找到binlog文件保存的目录位置
 

下面才是重点：

定位binlog中sql的位置

逆向操作得到SQL语句

·获取insert语句

1.定位binlog中sql的位置

前往binlog文件所在的目录查看所有binlog文件

bash-4.2# ls /var/lib/mysql/mysql-bin* -alh
-rw-r----- 1 mysql mysql 1.1G Sep  9 02:28 /var/lib/mysql/mysql-bin.000200
-rw-r----- 1 mysql mysql 1.1G Sep  9 02:32 /var/lib/mysql/mysql-bin.000201
-rw-r----- 1 mysql mysql 1.1G Sep  9 02:39 /var/lib/mysql/mysql-bin.000202
-rw-r----- 1 mysql mysql 1.1G Sep  9 02:45 /var/lib/mysql/mysql-bin.000203
-rw-r----- 1 mysql mysql 1.1G Sep  9 07:52 /var/lib/mysql/mysql-bin.000204
-rw-r----- 1 mysql mysql 1.1G Sep  9 12:10 /var/lib/mysql/mysql-bin.000205
-rw-r----- 1 mysql mysql 1.1G Sep 10 04:40 /var/lib/mysql/mysql-bin.000206
-rw-r----- 1 mysql mysql 1.2G Sep 10 07:00 /var/lib/mysql/mysql-bin.000207
-rw-r----- 1 mysql mysql 1.1G Sep 11 07:54 /var/lib/mysql/mysql-bin.000208
-rw-r----- 1 mysql mysql 1.1G Sep 12 03:03 /var/lib/mysql/mysql-bin.000209
-rw-r--r-- 1 root  root   24M Sep 11 09:06 /var/lib/mysql/mysql-bin.000209.event.log
-rw-r----- 1 mysql mysql 1.1G Sep 12 03:30 /var/lib/mysql/mysql-bin.000210
-rw-r----- 1 mysql mysql 1.1G Sep 12 08:33 /var/lib/mysql/mysql-bin.000211
-rw-r----- 1 mysql mysql 1.1G Sep 12 08:35 /var/lib/mysql/mysql-bin.000212
-rw-r----- 1 mysql mysql 1.1G Sep 12 22:00 /var/lib/mysql/mysql-bin.000213
-rw-r----- 1 mysql mysql 1.1G Sep 13 10:26 /var/lib/mysql/mysql-bin.000214
-rw-r----- 1 mysql mysql 1.1G Sep 13 10:29 /var/lib/mysql/mysql-bin.000215
-rw-r----- 1 mysql mysql 1.1G Sep 14 01:42 /var/lib/mysql/mysql-bin.000216
-rw-r----- 1 mysql mysql 637M Sep 14 06:11 /var/lib/mysql/mysql-bin.000217
-rw-r----- 1 mysql mysql 4.1K Sep 14 01:42 /var/lib/mysql/mysql-bin.index
2.估计数据的插入时间，选取对应的文件，获取对应的binlog片段

选取对应的binlog文件进行解析成可读的sql文件

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-09-12 11:59:00" --stop-datetime="2024-09-12 12:01:00" mysql-bin.000213 > binlog.sql
3.statement模式确认binlog的位置

# at 219
#240914 17:14:26 server id 1  end_log_pos 300 CRC32 0xb8159bc1     Query    thread_id=1267    exec_time=0    error_code=0
SET TIMESTAMP=1726305266/*!*/;
SET @@session.pseudo_thread_id=1267/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 300
#240914 17:14:26 server id 1  end_log_pos 414 CRC32 0xb7e0263b     Query    thread_id=1267    exec_time=0    error_code=0
use `tests`/*!*/;
SET TIMESTAMP=1726305266/*!*/;
insert into person values (1, 'first')
/*!*/;
# at 414
#240914 17:14:26 server id 1  end_log_pos 445 CRC32 0x9345e6ca     Xid = 30535
COMMIT/*!*/;
# at 445
我们可以找到insert into person values (1, 'first'), 并且分别在前后的BEGIN和COMMIT找到position。

BEGIN往前找有一个position at 219, COMMIT往后找有一个postion at 445, 这就是插入语句的实际binlog范围。

4.row模式确认binlog位置

row模式的格式和statement模式可能会有少许差别，但方法是一致的。

# at 219
#240914 17:16:36 server id 1  end_log_pos 292 CRC32 0xe9082d52     Query    thread_id=20    exec_time=0    error_code=0
SET TIMESTAMP=1726305396/*!*/;
SET @@session.pseudo_thread_id=20/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 292
#240914 17:16:36 server id 1  end_log_pos 345 CRC32 0x1832ced4     Table_map: `tests`.`person` mapped to number 111
# at 345
#240914 17:16:36 server id 1  end_log_pos 395 CRC32 0x32d6a21b     Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `tests`.`person`
### SET
###   @1=1
###   @2='first'
# at 395
#240914 17:16:36 server id 1  end_log_pos 426 CRC32 0x07619928     Xid = 149
COMMIT/*!*/;
# at 426
我们可以找到###开头的几行，包含INSERT INTO 语句。并且分别在前后的BEGIN 和 COMMIT找到position。

BEGIN往前找有一个position at 219 ,COMMIT往后找有一个position at 426,这就是插入语句的实际binlog范围。

5.根据binlog位置解析binlog文件

mysqlbinlog --start-position=219 --stop-position=426 mysql-bin.000213 > binlog.sql
7.重新写回数据

这样

mysql -uroot -proot < binlog.sql
或者

source binlog.sql;
·获取delete语句，逆向操作

# at 1574
#240914 17:16:38 server id 1  end_log_pos 1642 CRC32 0x944b1b94     Query    thread_id=20    exec_time=1260    error_code=0
SET TIMESTAMP=1726305398/*!*/;
BEGIN
/*!*/;
# at 1642
#240914 17:16:38 server id 1  end_log_pos 1695 CRC32 0x435282e2     Table_map: `tests`.`person` mapped to number 111
# at 1695
#240914 17:16:38 server id 1  end_log_pos 1745 CRC32 0x3063bf8c     Delete_rows: table id 111 flags: STMT_END_F
### DELETE FROM `tests`.`person`
### WHERE
###   @1=1
###   @2='first'
# at 1745
#240914 17:16:38 server id 1  end_log_pos 1776 CRC32 0x086c2270     Xid = 3391
COMMIT/*!*/;
逆向操作生成sql

insert into person values (1, 'first');
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/TGQlovemm/article/details/144693018
 

mysql -h 127.0.0.1 -P 5577 -u root -p tent000001 <D:\yqtj\mysql-8.0.15-winx64\inctent000001.sql --force

mysqlbinlog --no-defaults --database=tent000001   ../mysql-bin.000265 >>inctent000001.sql


http://www.kler.cn/a/601007.html

相关文章：

  • stable diffusion本地安装
  • 如何在Windows上下载并配置GO语言环境变量
  • Linux--进程控制
  • ETL：数据清洗、规范化和聚合的重要性
  • redis 学习笔记
  • 至此，他19岁青春烙印上了苦涩的烧痕。
  • 【鸿蒙开发】Hi3861学习笔记- TCP客户端
  • 通过国内源在Ubuntu20.0.4安装repo
  • Mininet-topo.py源码解析
  • linux中防火墙的配置
  • 【Docker系列八】使用 Docker run 命令部署 Nginx
  • Windows系统安装Node.js和npm教程【成功】
  • 使用 fn_dblog手动恢复误操作的 update（单列数值型数据恢复）
  • 基于SpringBoot + Vue 的汽车租赁管理系统
  • C++类与对象的的第三个简单的实战练习-3.25笔记
  • 【Qt 01】cmake搭建Qt VS2019开发环境
  • uniapp 微信小程序图片下载保存功能
  • 贪心算法 力扣hot100热门面试算法题 面试基础 核心思路 背题
  • Micropython RPI-PICO 随记-双PICO串口传数据
  • Excel多级联动下拉菜单的自动化设置（使用Python中的openpyxl模块）