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

基于binlog恢复MySQL数据

一、关于MySQL的binlog日志

binlog日志特征:每当我们重启MySQL一次,会自动生成一个binlog文件,当然,我们也可以手动的来刷新binlog文件,通过 flush logs,同样会新创建一个binlog文件。实际上当服务器在重启时,也会调用flush logs操作。
记录了数据库整个生命周期数据的整个操作
关于mysql日志文件:

二进制日志(Binary Logs):
用于主从复制(Master-Slave Replication),从服务器通过读取主服务器的二进制日志来同步数据。
用于数据恢复,通过二进制日志可以回滚到某个时间点。
错误日志(Error Logs):
记录MySQL服务器的启动、运行和关闭过程中的错误信息。
有助于诊断和解决服务器问题。
通用查询日志(General Query Logs):
记录所有发送到MySQL服务器的SQL语句。
用于审计和调试。
慢查询日志(Slow Query Logs):
记录执行时间超过指定阈值的SQL语句。
用于优化查询性能。
- 查看日志开启状态 mysql variables like '%log_bin%';
- 查看所有binlog日志列表 show master logs; / show binary logs;
- 查看最新一个binlog日志的编号极其租后一个操作的结束点 show master status;
- 查看binglog事件 show binlog events limit 3;
- 导入事件并查看事件 mysqlbinlog.exe --no-defaults ../data/binlog.000231 --stop-datetime='2020-04-30 15:30:30' | mysql -f -uroot -proot
- 查找mysql配置文件所在的位置my.cnf (my.ini windows) find / -name 'my.ini'

二、基于binlog恢复mysql数据(需开启binlog日志)

show variables like '%log_bin%';   -- 查看日志开启状态(确认mysql bin_log已经开启)

在这里插入图片描述

在模拟误删数据操作前我们需先记下当前数据的偏移量Position(在binlog中所生成日志的最新位置)

show master status;  -- 查看当前主库(Master)二进制日志(Binary Log)状态的命令。该命令返回的信息对于配置和监控主从复制非常重要。

在这里插入图片描述
为了观察的更直观,已将日志切换到一个新的binlog下面(mysql-bin.000022)

 flush logs;  --刷新和重命名当前的二进制日志文件,执行此命令后,MySQL会关闭当前的日志文件并创建新的日志文件。

再执行show master status;(查看当前最新的binlog)
在这里插入图片描述
模拟线上数据事故前,先在129服务器上(数据库:orders)模拟生产数据48条:
在这里插入图片描述
模拟线上误删数据操作(删除表中id>30的18条数据)
在这里插入图片描述

然而在做删除数据后查看此时的Position仍为156,仔细查看发现只用testdb开启了binlog日志,而模拟的线上数据为pro_simulation,于是配置binlog过滤:
然而,需要注意的是,binlog是针对整个MySQL服务器实例的,而不是针对单个数据库的。这意味着不能直接为特定的数据库开启或关闭binlog。虽然不能直接为单个数据库启用binlog,但仍可以使用binlog-ignore-db和binlog-do-db选项来指定哪些数据库的更改应该被记录或忽略。
或者binlog-do-db配置项可以完全不进行配置
在MySQL中,如果binlog-do-db配置项完全不做任何配置(即不写该配置项),MySQL的binlog日志行为将遵循默认设置。
示例:

[mysqld]
# 设置MySQL服务器的唯一标识符,用于主从复制等场景
server-id = 1
# 启用binlog,并指定binlog文件的名称前缀
log_bin = /var/log/mysql/mysql-bin
# 设置binlog的格式,可以选择STATEMENT、ROW或MIXED
# STATEMENT:记录执行的SQL语句(默认)
# ROW:记录数据行的实际变化
# MIXED:混合STATEMENT和ROW,根据具体情况选择记录方式
binlog_format = ROW
# 其他与binlog相关的配置项,如binlog文件过期时间等,可以根据需要进行配置
# 例如:设置binlog文件过期时间为30天(MySQL 8.0及以上版本使用binlog_expire_logs_seconds)
binlog_expire_logs_seconds = 2592000
# 注意:未配置binlog-do-db,因此默认情况下将记录所有数据库的操作

具体来说,如果binlog-do-db未配置,binlog将会记录所有数据库的操作,而不仅仅是当前连接下的数据库操作。这是因为binlog是MySQL服务器级别的日志,它记录的是服务器上所有数据库的更改操作,而不是仅限于某个特定数据库或连接。

binlog-ignore-db = db_name:这将导致指定数据库db_name的更改不被记录在binlog中。
binlog-do-db = db_name:这将确保只有指定数据库db_name的更改被记录在binlog中
                        (注意,如果有多个binlog-do-db条目,则只有这些数据库会被记录;
                         如果有binlog-ignore-db和binlog-do-db同时存在,
                         则binlog-do-db具有更高的优先级)。

例如,如果只想记录名为my_database的数据库的更改,可以这样配置:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog-ignore-db = mysql      # 忽略系统数据库
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-do-db = db1   # 记录my_database的更改
binlog-do-db = db2   #.....可配置多个  

进行配置后(/etc/my.cnf下),再次验证配置:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_do_db';
SHOW VARIABLES LIKE 'binlog_ignore_db';

重启后重复以上误删操作:(偏移量发生变化===>成功记录了误删操作!!!
在这里插入图片描述

查看 binlog日志(mysql-bin.000022) 文件中的所有事件:
show binlog events in 'mysql-bin.000022';
在这里插入图片描述

:查看所有binlog日志文件:SHOW BINARY LOGS;

恢复删除数据:(执行命令前先退出后执行)
mysqlbinlog -v /var/lib/mysql/mysql-bin.000022 --start-position=156 --stop-position=12613 | mysql -uroot -p123456
在这里插入图片描述
操作完毕发现表数据已全部恢复为48条,至此数据恢复成功!!!


① 过滤特定时间段的日志
mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-02 00:00:00" /var/lib/mysql/mysql-bin.000022
过滤特定时间段的日志
② 查找特定事件(查找所有与 users 表相关的操作)
mysqlbinlog /var/lib/mysql/mysql-bin.000022 | grep 'users'


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

相关文章:

  • QT 如何禁止QComboBox鼠标滚轮
  • FreeType 介绍及 C# 示例
  • qml LevelAdjust详解
  • 工作记录小点
  • 嵌入式系统Linux实时化(四)Xenomai应用开发测试
  • react中hooks之useEffect 用法总结
  • 在 Navicat 17 中连接 ProxySQL 的详细教程
  • 瑞芯微 RK 系列 RK3588 使用 ffmpeg-rockchip 实现 RGA 2D 图形操作硬件加速-代码版
  • idea大小写转换快捷键,及设置快捷转换格式
  • C++实现设计模式---享元模式 (Flyweight)
  • 电控管理平台
  • [Qt] QSS | Qt Designer | 选择器
  • Linux/MacOS中如何远程调试C/C++程序
  • 无公网IP 实现外网访问本地 Docker 部署 Navidrome
  • NanoKVM简单开箱测评和拆解,让普通电脑实现BMC/IPMI远程管理功能
  • Redis基础3-主从复制
  • 得物App利用技术赋能,打造潮流消费“新玩法”
  • 全面了解 Web 前端技术:从基础到实践
  • JavaScript中的`void 0`:一个古老而安全的`undefined`获取方式
  • mac m4 安装 node
  • ResNet (Residual Network) - 残差网络:深度卷积神经网络的突破
  • Three.js+Vue3+Vite物体位移、缩放与旋转(二)
  • 【MySQL】使用C语言链接
  • R语言基础| 方差分析
  • 浅谈云计算11 | 虚拟机的主要功能
  • (RAG系列)FastGPT批量添加索引