23.binlog的作用和刷盘

binglog介绍

binlog在MySQL的server层产生，不属于任何引擎，主要记录用户对数据库操作的SQL语句(除了查询语句)。

之所以将binlog称为归档日志，是因为binlog不会像redo log一样擦掉之前的记录循环写，而是一直记录(超过有效期才会被清理)，如果超过单日志的最大值(默认1G，可以通过变量 maxbinlogsize 设置)，则会新起一个文件继续记录。

但由于日志可能是基于事务来记录的(如InnoDB表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好binlog日志文件达到了最大值但事务还没有提交则不会切换新的文件记录，而是继续增大日志，所以 maxbinlogsize 指定的值和实际的binlog日志大小不一定相等。

binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

binlog作用:主从同步和数据库的还原。

正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原。 redolog是循环写的，不持久保存，binlog的“基于时间点的备份还原”这个功能，redolog是不具备的。

那么回到刚才的问题，binlog可以简化掉吗？这里需要分场景来看：

如果是主从模式下，binlog是必须的，因为从库的数据同步依赖的就是binlog；

如果是单机模式，并且不考虑数据库基于时间点的还原，binlog就不是必须的,因为有redo log就可以保证crash-safe能力了。

但如果万一需要回滚到某个时间点的状态，这时候就无能为力，所以建议binlog还是一直开启。

根据上面对三个日志的详解，我们可以对这个问题进行解答：

在主从模式下，三个日志都是必须的；在单机模式下，binlog可以视情况而定，保险起见最好开启。

binlog的写入机制

其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数 binlogcachesize用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。状态如图1所示。

图1 binlog写盘状态

可以看到，每个线程有自己binlog cache，但是共用同一份binlog文件。

• 图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
• 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

binlog刷盘时机:sync_binlog

事务执行时，会给每个线程在内存中分配一块地方叫binlog cache，binlog文件就记录在这里，事务提交的时候，再把binlog cache写到binlog文件中。注意，一个事务的binlog不能被拆开提交，无论这个事务多大，也要确保一次性写入,对于 InnoDB 存储引擎而言，只有在事务提交时才会记录binlog ，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？

write 和fsync的时机，是由参数sync_binlog控制,该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、N：

0：默认值,事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作(fsync())，此时只是写入操作系统缓冲，若操作系统宕机则会丢失部分二进制日志

即不去强制要求，由系统自行判断何时写入磁盘。

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。

即:每次 commit 的时候都要将 binlog 写入磁盘；

N：每写N次操作系统缓冲就执行一次刷新操作。

即:每N个事务，才会将 binlog 写入磁盘。

从上面可以看出，sync_binlog 最安全的是设置是1 ，将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。

二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。

因此，在出现IO瓶颈的场景里，将syncbinlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。将syncbinlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

MySQL 5.7.7之后版本默认值是1。设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

流行的配置是双1,并发度太低了。

也就是说设置为1,一致性越强,但是并发度低,设置为N并发度高,但是会导致一致性问题。因此如果没有主从同步的要求,这个参数完全可以设置更大一些。

0:从缓冲写入磁盘，但是不进行刷新操作

0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作(fsync())，此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1:同步写入磁盘

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。

N:每写N次操作系统缓冲就执行一次刷新

N：每写N次操作系统缓冲就执行一次刷新操作。

将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。

数据恢复:整库备份 + binlog

为什么必须有“两阶段提交”呢？

这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？

前面我们说过了，binlog会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，前提是系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

· 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

· 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

主要还是整库备份 + binlog

比如现在删除了account表。那么只需要找到昨天九点之前的全量备份记录,先恢复昨天九点之前的全量备份记录到数据库,然后依次执行昨天九点之后的binlog。

总结

redo log用于保证crash-safe能力。innodbflushlogattrx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

这就是常说的双1。

参考:https://time.geekbang.org/column/intro/139