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