上篇文章中我们通过一个小例子演示了mysql如何通过binlog恢复被误删的数据,这节我们重点讲下binlog的工作原理,以及其他用法
binlog是mysql用来记录数据库表结构变更以及表数据修改的的二进制日志,它只会记录表的变更操作,但不会记录select和show这种查询操作。
binlog比较常用的场景有以下3种
- 数据恢复
误删数据之后可以通过mysqlbinlog工具恢复数据
- 主从复制
主库将binlog传给从库,从库接收到之后读取内容写入从库,实现主库和从库数据一致性
- 审计
可以通过二进制日志中的信息进行审计,判断是否对数据库进行注入攻击
binlog文件包含两种类型:
- 索引文件(文件名后缀为.index)用于记录哪些日志文件正在被使用
- 日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
最有用的就是这个binlog日志文件,里边记录的是对数据库的各种操作,你一定会很好奇binlog是怎么记录数据库操作的。其实binlog了给我们提供了3种记录模式:
- ROW:记录的是每一行被修改的数据
- STATEMENT:记录的是执行的SQL语句
- MIXED:statement和row模式的混合
记录模式 | 优点 | 缺点 |
ROW | 能清楚记录每一个行数据的修改细节 | 批量操作,会产生大量的日志,尤其是alter table会让日志文件大小暴涨 |
STATEMENT | 日志量小,减少磁盘IO,提升存储和恢复速度 | 在某些情况下会导致主从数据不一致,比如Sql语句中有last_insert_id()、now()等函数。 |
MIXED | 准确性强,文件大小适中 | 当binlog format 设置为mixed时,普通复制不会有问题,但是级联复制在特殊情况下会binlog丢失。 |
记录模式 | 优点 | 缺点 |
ROW | 能清楚记录每一个行数据的修改细节 | 批量操作,会产生大量的日志,尤其是alter table会让日志文件大小暴涨 |
STATEMENT | 日志量小,减少磁盘IO,提升存储和恢复速度 | 在某些情况下会导致主从数据不一致,比如Sql语句中有last_insert_id()、now()等函数。 |
MIXED | 准确性强,文件大小适中 | 当binlog format 设置为mixed时,普通复制不会有问题,但是级联复制在特殊情况下会binlog丢失。 |
Binlog文件结构:
MySQL的binlog文件中记录的是对数据库的各种修改操作,用来记录修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有:Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
Binlog文件中Log event结构如下图所示:
event字段名 | 描述 |
timestamp | 4字节 事件开始执行时间 |
event_type | 1字节 指定该事件的类型 |
event size | 4字节 该事件的长度 |
server_id | 1字节 服务器的serverId |
next log pos | 4字节 下一个事件开始的位置 |
flag | 2字节 event flags |
event字段名 | 描述 |
timestamp | 4字节 事件开始执行时间 |
event_type | 1字节 指定该事件的类型 |
event size | 4字节 该事件的长度 |
server_id | 1字节 服务器的serverId |
next log pos | 4字节 下一个事件开始的位置 |
flag | 2字节 event flags |
event字段名 | 描述 |
timestamp | 4字节 事件开始执行时间 |
event_type | 1字节 指定该事件的类型 |
event size | 4字节 该事件的长度 |
server_id | 1字节 服务器的serverId |
next log pos | 4字节 下一个事件开始的位置 |
flag | 2字节 event flags |
fixed part | 每种event type对应的结构体固定的部分 |
variable part | 每种event type对应的结构体可变的部分 |
fixed part | 每种event type对应的结构体固定的部分 |
variable part | 每种event type对应的结构体可变的部分 |
binlog的写入机制:
- 根据设置的记录模式和操作生成相应的log event
- 事务执行过程中产生log event会先写入缓冲区,每个事务线程都有一个缓冲区,Log Event保存在一个binlog_cache_mngr数据结构中,在该结构中有两个缓冲区,一个是stmt_cache,用于存放不支持事务的信息;另一个是trx_cache用于存放支持事务的信息。
- 事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中,所以一个事务包含的log event信息在binlog文件中是连续的,中间不会插入其他事务的log event。
配置binlog参数:
在MySQL配置文件my.cnf文件中的mysqld节中添加下面的配置文件:
[mysqld]
#设置记录模式
binlog_format = mixed
# 设置日志路径,注意路经需要mysql用户有权限写
log-bin = /data/mysql/logs/mysql-bin.log
# 设置binlog清理时间
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 100m
# binlog缓存大小
binlog_cache_size = 4m
# 最大binlog缓存大小
max_binlog_cache_size = 512m
重启MySQL生效,如果不方便重启服务,也可以直接修改对应的变量即可。
binlog 相关操作命令:
使用show binlog events命令查看日志文件中的event
show binary logs; //等价于show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';
使用mysqlbinlog命令
# 查看binlog日志文件内容
mysqlbinlog "文件名"
# 把binlog日志文件转存为sql文件
mysqlbinlog "文件名" > "test.sql"
# 按指定时间恢复数据
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop-datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
# 按事件位置号恢复数据
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002| mysql -uroot -p1234
删除Binlog文件
# 删除指定文件
purge binary logs to 'mysqlbinlog.000001';
# 删除指定时间之前的文件
purge binary logs before '2020-04-28 00:00:00';
# 清除所有文件
reset master;
可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉
http://weixin.qq.com/r/zBJpcYHEZ9wYrcUS90fe (二维码自动识别)