二进制日志(bin log)

💡 binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 **`DDL`** 和 **`DML`** 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。

它以**事件形式记录并保存在二进制文件**中。通过这些信息，我们可以再现数据更新操作的全过程。

二进制日志的作用

💡

• 数据恢复。
• 数据复制。

二进制日志格式

💡

• Statement

  每一条会修改数据的sql都会记录在binlog中。

  statement模式下的 binlog 记录的是数据库执行的原生SQL语句。采用该格式的二进制日志文件最小，性能最高，主从复制网络带宽小，但很容易出现主从不一致的问题。

  优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

• Row

  5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

  binlog 记录的是数据行的更改情况，即数据行在更改前、更改后的变化情况，所以row模式下的二进制日志文件最大，对性能会有一定影响，主从复制网络带宽高，但在主从复制模式下可靠性最好。

  优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下 的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

• Mixed

  从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

  mixed实际上就是前两种模式的结合，在mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。

  MySQL 会在一些特定的情况下自动从 statement 格式切换到 row格式，默认采用statement格式。在以下情况下MySQL将会采用row格式：

  a、表的存储引擎为NDB，这时对表的DML操作都会以row格式记录 ； b、使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数 ； c、使用了insert delay语句 ； d、使用了用户自定义函数UDF ； e、使用了临时表；

二进制日志写入机制

💡 inlog的写入时机也非常简单，事务执行过程中，先把日志写到 **`binlog cache`** ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过**binlog_cache_size**参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。binlog日志刷盘流程如下：

💡 wirte：写入内存cache。 fsync：操作系统写入磁盘。

write与fsync写入时机

💡 write和fsync的时机，可以由参数 **`sync_binlog`** 控制：

• 默认是 0 ：为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binglog 会丢失。
• 为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样
• 最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

bin log 与 redo log对比

💡 第一：记录内容的不同

• binlog是逻辑日志，记录所有数据的改变信息。
• redo log是物理日志，记录所有InnoDB表数据的变化。

第二：记录内容的时间不同。

• binlog记录commit完毕之后的DML和DDL SQL语句。
• redolog记录事务发起之后的DML和DDLSQL语句。

第三:文件使用方式的不同。

• binlog不是循环使用，在写满或者实例重启之后，会生成新的binlog文件。
• redo log是循环使用，最后-个文件写满之后，会重新写第-一个文件。

第四:作用不同。

• binlog可以作为恢复数据使用，主从复制搭建。
• redo log作为异常宕机或者介质故障后的数据恢复使用。

两阶段提交

💡 **两个问题**：

先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。 但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。 然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

1. 客户端发送执行更新语句的命令
2. 将语句执行
3. 把修改的语句更新到内存中
4. 记录redo log，并将记录状态设置为prepare
5. 通知Server，已经修改好了，可以提交事务了
6. 将更新的内容写入binlog
7. commit，提交事务
8. 将redo log里这个事务相关的记录状态设置为commited

说白了就是执行完更新语句后，先将redo log状态设置为prepared，等更新内容写入binlog后，在将redo log状态设置为commited。

参考

• juejin.cn/post/684490…