9、mysql binlog

binlog 二进制日志文件，mysql实现，开启后会记录数据库的修改操作，每次事务提交成功，都会记录一次。

主要用于数据恢复， 主从复制。

开启配置 my.ini/cnf

[mysqld]
# binlog的存放位置，默认在data下，会生成2个文件my-binlog.index索引文件，my-binlog.000001数据文件
log-bin=my-binlog
# 服务器id，单独部署没什么用，集群模式下必须唯一
server-id=1
# 日志文件格式
# 有3种格式
# statement 基于sql语句的备份，日志写入开销最小，但是若sql存在函数，执行时刻不同结果也会不同
# row 基于行的复制，记录每一行全部的数据，体积较大，但可以解决sql函数问题
# mixed 混合模式，mysql根据sql判断，如果存在函数，就选择row，否则就用statement
binlog_format = mixed
# 日志文件过期天数
expire_logs_days = 15
# binlog文件大小限制
max_binlog_size = 100M

写入磁盘策略

show variables like '%sync_binlog%'

sync_binlog有3中策略

0 默认值，提交事务都只写到系统文件缓存page cache，性能有提升，但操作系统宕机后会丢失数据。

1 提交事务会执行fsync写入到磁盘，安全性最高。

n n>1，提交事务后先写文件缓存page cache， 等积累n个事务后再写磁盘，折中方案。

binlog文件递增

当发生以下情况时，mysq会重新生成一个文件写binlog，文件名是递增的

• 服务器重启
• 执行flush logs刷新日志
• 日志文件达到max_binlog_size

删除binlog

-- 删除当前文件
reset master;
-- 删除指定文件之前的文件（不包含）
purge master logs to 'file name';
-- 按指定日期删除
purge master logs before  'yyyy-MM-dd HH:mm:ss';

查看biglog

mysqlbinlog --no-defaults -v --base64-output=decode-rows binlog文件名
-- 按起始时间或起始位置查看
mysqlbinlog --no-defaults -v --base64-output=decode-rows binlog文件名 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

数据恢复

先查找要恢复的sql语句，然后记住起始位置，通过命令重新执行

-- 通过上面的方法找到目标起始位置
-- 执行sql片段
mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test binlog文件名 | mysql -uroot -proot -v test
-- 或是通过时间起始位置恢复
--start-datetime --stop-datetime

这只是增量恢复，必须得有全量的备份

全量备份配合binlog可以完全恢复数据

一般要定时做全量备份， binlog的有效期要大于定时备份的间隔时间。