基于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'