MySQL 之 binlog 日志恢复单条被删数据

MySQL的binlog（二进制日志）是一种系统日志，记录了对MySQL数据库执行的所有更改操作，包括插入、更新、删除等。这些日志以二进制格式存储，因此被称为binlog。

binlog的主要用途有：

数据恢复：如果数据库发生故障或数据丢失，可以使用binlog进行数据恢复。通过读取binlog文件，可以重新构建出数据库在某个时间点的状态。
主从复制：在MySQL的主从复制中，主服务器上的binlog事件会被复制到从服务器上，并重新执行，从而保持主从服务器数据的一致性。
数据审计：可以读取binlog来查看数据库中的数据变更历史，用于数据审计和监控。

binlog的格式有两种：

Statement-Based Logging (SBL)：记录SQL语句本身，但不记录数据。
Row-Based Logging (RBL)：记录每一行数据的变更，而不是记录SQL语句。

另外，MySQL还有基于时间点的恢复功能，通过指定一个时间点，可以恢复到该时间点的数据状态。但这种方法的恢复精度有限，不能精确到某一行数据。

使用

查看是否是否开启Binlog日志

show variables like '%log_bin%'; // on是开启，off 是关闭

查看Binlog 的路径

show variables like '%datadir%';

什么文件是Binlog日志

如何开启MySQL的Binglog 日志

默认情况下MySQL的Binlog是关闭的。

修改mysql的配置文件my.ini

# Binary Logging.
# log-bin
log_bin = mysql-bin
# log_bin = C:\ProgramData\MySQL\MySQL Server 5.7\Data\log\bin_log
binlog_format = ROW

详细的配置文件

[mysqld]
sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

# 设置3306端口

port=3306

# 设置mysql的安装目录

basedir=C:\work\mysql-8.0.28-winx64   # 切记此处一定要用双斜杠
#        C:\work\mysql-8.0.28-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\work\mysql-8.0.28-winx64\data   # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#binlog日志保留时长
expire_logs_days=7
#启用binlog日志，并指定文件名前缀
log-bin=mysql-bin
#步进值auto_imcrement 。一般有n台主mysql就填n
auto_increment_increment=2
#起始值，一般填写第n台主机mysql.此时为第一台主  mysql                        
auto_increment_offset=1  
socket=/data/mysqldata/mysql.sock
#缓存池
innodb_buffer_pool_size=4G
# 双1模式
# 按事务刷盘，刷日志 [0:最快模式,1:安全模式,2:比0安全但比0要慢]看需求，服务器较为稳定可以选择2，自己搭建的最好选择1
innodb_flush_log_at_trx_commit=1
# 提交1次事务刷1次，可以为n
sync_binlog=1
# 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘
# 25%~50%
innodb_max_dirty_pages_pct=30
# 后台进程最大IO性能指标
# 默认200，如果SSD，调整为5000~20000
innodb_io_capacity=5000
# 默认10M。防止高并发下，数据库受影响
#innodb_data_file_path=ibdata1:256M:autoextend

# 默认2，单位s。慢查询时间。建议0.1~0.5
#long_qurey_time=0.5

# 8.0默认row。记录格式，让数据安全可靠
binlog_format=row

# 默认8小时。交互等待时间和非交互等待时间
# 建议300~500s,两参数值必须一致，且同时修改
interactive_timeout=500
wait_timeout=500

#日志大小
innodb_log_file_size=256M
#日志缓存大小
innodb_log_buffer_size=12M

innodb_flush_log_at_trx_commit=2
#innodb_flush_method
#thread_cache=8
#innodb_autoextend_increment=128M

#这里确认是否起用压缩存储功能
innodb_file_per_table=1

#决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；
#相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9
innodb_compression_level=5

#指定在每个压缩页面可以作为空闲空间的最大比例，
#该参数仅仅应用在设置了innodb_compression_failure_threshold_pct不为零情况下，并且压缩失败率通过了中断点。
#默认值50，可以设置范围是0到75
innodb_compression_pad_pct_max=50

[mysqld_safe]
log-error=/data/mysqldata/mysql.log
pid-file=/data/mysqldata/mysql.pid

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

如何定位自己删除的操作

先找到最新的binlog 文件，一般该文件较大，有1G多，我们需要再从这个文件中，定位到咱们执行删除操作的位置。mysql-bin.000050，这个文件就是我最新的。

根据时间（自己执行删除操作的时间）

C:\work\mysql-8.0.28-winx64\data> mysqlbinlog --base64-output=decode-rows -v --database=serp_online --start-datetime="2024-01-03 10:20:00" --stop-datetime="2024-01-03 10:30:00"mysql-bin.000050 > c:/1.sql

根据位置

查询台，执行该命令

show binlog events in 'mysql-bin.000050';

找对应的 Delete_rows 这关键字，再看 info 信息，从最近的BEGIN 是开始，Pos 对应的就是命令里的 start-position

C:\work\mysql-8.0.28-winx64\data> mysqlbinlog --base64-output=decode-rows -v --database=serp_online --start-position=1017777472  --stop-position=1017778117  mysql-bin.000050 > c:/1.sql

恢复被删数据

最后到生成的文件 1.sql 查看删除的数据

总结

以上就是，我自己在mysql 中恢复单条被删数据的全部内容了，欢迎大家在留言区批评指教！

【实战】MySQL 之 binlog 日志恢复单条被删数据