MySQL binlog介绍 作者:马育民 • 2022-06-12 17:23 • 阅读:10160 # 介绍 binlog是二进制日志文件,记录了所有的 DDL 和 DML 语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。 - DDL,数据库定义语言,主要的命令有create、alter、drop等,如:建表 - DML,数据操纵语言,update,insert,delete,增删改数据 ### 应用场景 - mysql主从复制,详见 [链接](https://www.malaoshi.top/show_1IX3U1Ti8d29.html "链接") - 实时数据备份:数据库崩溃、数据丢失,可以恢复数据 ### 缺点 开启binlog日志大概会有1%的性能损耗 # 日志文件分类 binlog日志包括两类文件: - 索引文件,文件名后缀为 `.index`,用于记录所有的 日志文件 - 日志文件,文件名后缀为 `.00000*`,记录数据库所有的DDL和DML语句事件 # 记录模式 有3种记录模式: - ROW:记录的是每一行被修改的数据 - STATEMENT:记录的是执行的SQL语句 - MIXED:statement和row模式的混合 |记录模式|优点|缺点| |---|---|---| |ROW|能清楚记录每一个行数据的修改细节|批量操作,会产生大量的日志,尤其是 `alter table` 会让日志文件大小暴涨| |STATEMENT|日志量小,减少磁盘IO,提升存储和恢复速度|在某些情况下会导致主从数据不一致,比如Sql语句中有 `last_insert_id()` 、 `now()` 等函数。| |MIXED|准确性强,文件大小适中|混合模式,默认使用 STATEMENT 模式,当有 `last_insert_id()` 、 `now()` 等函数时,使用 ROW 模式| ### 模式选择 1. 互联网公司,使用MySQL的功能相对少(存储过程、触发器、函数)选择默认的语句模式,Statement Level(默认) 2. 公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)则选择Mixed模式 3. 公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)又希望数据最大化一致,此时最好选择Row level模式 ### 行模式和语句模式的区别 - **Statement模式:** 100万条记录,只需1条 `delete * from test;` 就可以删除100万条记录 - **row模式:** 100万条记录,记录100万条删除命令 # 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| # 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。 参考: https://www.cnblogs.com/Presley-lpc/p/9619571.html https://zhuanlan.zhihu.com/p/227864607 原文出处:http://malaoshi.top/show_1IX3U7ikRyfw.html