mysql误删除数据基于binlog日志文件进行恢复

注意：本教程基于windows 10，mysql5.7.32 环境进行操作

1. 查看binlog日志是否开启

show variables like 'log_%';

如果为OFF未开启，首先mysql安装目录下找到my.ini文件，增加如下配置

# Binary Logging
server_id =12580
log_bin =C:\ProgramData\MySQL\mysql-bin
binlog_format =ROW

注意：配置完毕后重启数据库

查看binlog全部日志信息

show master logs;

查看binlog日志模式

show variables like '%binlog_format%';

1. 刷新binlog日志

flush logs;

show master status;

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

2. 查看binlog日志信息

或者导出binlog文件进行查看

mysqlbinlog C:\ProgramData\MySQL\mysql-bin.000001 > D:\20210819.txt

• 当bin-log的模式设置为 row时
• binlog_format=row

mysqlbinlog C:\ProgramData\MySQL\mysql-bin.000004 > D:\20210819.txt

• 直接mysqlbinlog出来的文件,里面执行sql部分的sql显示为base64编码格式
• 生成sql的编码需要解码 需要加上相应的参数才能显示出sql语句
• –base64-output=decode-rows -v

mysqlbinlog C:\ProgramData\MySQL\mysql-bin.000004 --base64-output=decode-rows -v > D:\20210819.txt

3.恢复数据的几种方法

a. 基于位置点恢复

mysqlbinlog C:\ProgramData\MySQL\mysql-bin.000001 --start-position 154 --stop-position 2392 | mysql -uroot -p123456 datatest

b. 恢复指定库的数据 -d

mysqlbinlog -d datatest C:\ProgramData\MySQL\mysql-bin.000001 | mysql -uroot -p123456

c. 基于时间点恢复

mysqlbinlog -–start-datetime="2021-08-19 11:13:36" -–stop-datetime="2021-08-19 11:13:45" C:\ProgramData\MySQL\mysql-bin.000001 | mysql -uroot -p123456 datatest

---

Mysql binlog日志有三种格式，分别为Statement,MiXED,ROW

ROW（row-based replication, RBR）

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。 缺点：批量操作，会产生大量的日志，尤其是alter
table会让日志暴涨。

STATMENT（statement-based replication, SBR）

每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点：日志量小，减少磁盘IO，提升存储和恢复速度
缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

MIXED（mixed-based replication, MBR）

以上两种模式的混合使用，一般会使用 STATEMENT 模式保存binlog，对于STATEMENT模式无法复制的操作使用 ROW 模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。