MySQL binlog日志记录格式写入机制及相关参数讲解_数据库_开发者

基本概念

二进制日志（binnary log）以事件形式记录了对mysql数据库执行更改的所有操作。

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这php类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

需要注意的一点是，即便update操作没有造成数据变化，也是会记入binlog。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

binlog有两个常用的使用场景：

主从复制：mysql replication在master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
数据恢复：通过mysqlbinlog工具来恢复数据。

MySQL binlog日志记录格式写入机制及相关参数讲解

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

binlog 日志有三种格式，可以通过binlog_format参数指定。

指定statement，记录的是逻辑SQL语句，比如执行一条update T set update_time=now() where id=1，记录的内容如下。优点是日志文件小，节约IO，提高性能。

MySQL binlog日志记录格式写入机制及相关参数讲解

同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

row 记录表的行更改情况，准确性强，可以为数据库的恢复、复制带来更好的可靠性；但是二进制文件的大小相较于STATEMENT会有所增加，较大的网络IO和磁盘IO。

mixed：STATEMENT和ROW模式的混合。默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式。

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Sw编程客栈ap）。

binlog日志刷盘流程如下

MySQL binlog日志记录格式写入机制及相关参数讲解

write和fsync的时机，可以由参数sync_binlog控制，默认是1。

N=1：表示采用同步写磁盘的方式php来写二进制日志，这时写操作不使XZMgVNlBCy用操作系统的缓冲来写二进制日志，每次事务提交都会写入文件。
N=0：为0的时候，表示每次提交事务都只writeXZMgVNlBCy，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

binlog_cache_size是基于会话(session)的。每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘（临时文件中）。