Mysql 二进制日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

一、二进制日志的参数配置

① 开启二进制日志

show variables like 'log_bin';

 ② 配置二进制日志的文件名称

在my.cnf配置以后，重启在数据库的数据目录就会有bin文件了

 ③ 二进制日志的格式

 A) binlog_format 在mysql5.7默认是row， 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨

例如：update a < 10 的数据，statement模式会记录这一条sql,而row模式的话，会记录a=1,a=2到a=9所有的记录的更改

 B) STATEMENT模式  每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题

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

二、二进制的查看 

① show master status; 查看当前二进制日志文件

②  flush binary logs; 刷新二进制文件 

执行一次，二进制文件生成一个新的

③ 查看二进制日志

show binlog events in 'bin.000003'

 二进制文件都是由event（事件）组成的。

pos和end_log_pos表示起始位置和结束位置的偏移量，end_log_pos - pos表示每个event的字节数

④ 通过mysqlbinlog查看

mysqlbinlog --base64-output=decode-rows -vv bin.000003

 如果想看到我们写入的sql语句格式，则需要设置 binlog_rows_query_log_events = 1

--start-position 可以通过偏移量进行查询

--start-datetime 通过开始时间来查询

--stop-datetime 通过结束时间查询

mysqlbinlog bin.000006 -vv --base64-output=decode-rows --start-datetime='2022-03-25 16:18:22'|less

⑤  恢复数据

 A ) 找到pos 和 End_log_pos 的位置

上面需要记住 Pos 329下面的删除rows的pos 377和497

 B)  通过mysqlbinlog 找到删除的数据

这里要记住的是--stop-position的位置是不包含的，例如--stop-position=377，是不包含pos=377的所以这里要用497

mysqlbinlog bin.000010 -vv --base64-output=decode-rows --start-position=219 --stop-position=497

 如果数据少的话 可以直接看@1 @2每列的数据进行恢复

C) 安装 binlog2sql 找回back sql

 直接在git下载 https://github.com/danfengcao/binlog2sql

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

安装好之后，运行下面的

python binlog2sql.py  --flashback  -h127.0.0.1 -P3306 -uroot -p'123456' -d test -t l2  --start-file=bin.000010 --start-pos=219  --end-pos=497 > back.sql

 -d 数据库

 -t  表名

--start-file 二进制文件

--start-pos： 上面的二进制日志的开始偏移量

--end-pos： 上面的二进制日志的结束偏移量

生成的文件，就是back sql

 上面就是我删除的逆向sql，运行sql就行了。