• undo log让mysql有回滚事务的能力；
  • redo log让mysql有崩溃恢复的能力；
  • bin log让MySQL有搭建集群、数据备份、恢复数据的能力。

一、bin log

bin log是MySQL的二进制日志文件，由MySQL的Server层产生。

redolog中记录偏向物理层面的记录，如：对哪个数据页的那个记录做了什么修改。
bin log中记录偏向逻辑层面的记录：如：对xx表中的id=yy的行做了什么修改，更改后的值是什么。

bin log不会记录select 、show这类的操作。可以在query log中找到曾经执行过的诸如select、show这种仅查询的SQL。

常见的bin log有如下的作用：

  1. delete没加where条件？不慌！bin log可以帮你恢复数据。
  2. 搭建一套一主两从的MySQL集群，bin log帮你完成主从的数据同步。
  3. 审计，通过分析bin log可以排查是否存在SQL注入攻击。

默认bin log是不开启的。因为开启bin log后会稍微降低一点mysql的性能（1%）。但是开启bin log后就可以搭建MySQL集群，排查SQL注入，恢复误删的数据。所以线上的MySQL集群都是开启bin log的。

二、bin log位置和配置

bin log存放的位置由 datadir 参数控制，可通过 show variables like '%datadir%'; 参数查看。

一般关于bin log的配置都写在MySQL的配置文件中: my.cnf , 以方便启动mysql时直接让这些配置生效。作为了解，可以大概搂一眼bin log的配置项：

  1. [mysqld]
  2. # binlog相关配置
  4. # 指定binlog日志存储的位置
  5. datadir                  = /home/mysql/mysql/var
  7. # 规范binlog的命名为 mysql-bin.0000XX
  8. # 如果加这行配置，binlog文件名为主机名
  9. log-bin                  = mysql-bin
  11. # 索引当前所有的binlog
  12. log-bin-index            = mysql-bin.index
  14. # 最大的大小
  15. max_binlog_size          = 1G
  17. # binlog的sync时机
  18. sync-binlog              = 1
  20. # binlog的格式
  21. binlog-format            = ROW
  23. # 保留七天的binlog
  24. expire_logs_days = 7

三、bin log 的高速缓存

bin log的高速缓存：所有未commit的事务产生的bin log，都会被先记录到bin log的高速缓存中。等该事务被commit时，再将缓存中的数据写入bin log日志文件中。
高速缓存的大小可以由参数 bin log_cache_size 默认大小为：32768 ，并且每个session都有自己的独立的缓存。多个会话指间彼此不影响。
bin log_cache_size不能设置太大，否则大量事务打来后肯定会造成宝贵的内存资源被浪费。但也别太小，因为当一个事务产生的日志足够大超过该参数设置的值时，MySQL会将缓存中的bin log数据写到临时文件中去。
image.png

四、bin log刷盘机制

bin log写入磁盘的机制由参数 sync_binlog 控制。

  • 策略1: sync_binlog = 0：表示innodb不会主动控制将bin log落盘，innodb仅会将bin log写入到OS Cache中，至于什么时间将bin log刷入磁盘中完全依赖于操作系统。选这种策略，一旦操作系统宕机，OS Cache中的bin log就会丢失。
  • 策略2:sync_binlog = 1：表示事务commit时将bin log落盘！这样哪怕机器宕机了，也能确保bin log会被写入到磁盘中。（线上环境推荐策略）

    sync_binlog = 1 详情见事务的两阶段提交。

事务的两阶段提交

  • 策略3:sync_bin log=N：当N大于1时，表示开启组提交，也就是group commit，可以这样理解：比如N=5，那MySQL就会等收集5个bin log后再将这5个bin log一口气同步到磁盘上。好处很明显，一次IO可以往磁盘上刷入N个bin log，IO效率会有所提升。坏处也很明显，比如N=5，那当MySQL收集了4个bin log时，服务器宕机，这4个bin log就会丢失。


五、超有用的参数 sql_log_bin

分享一种线上实际存在的场景，就能知道该参数的妙用了。
比如线上使用的是一个一主两从的MySQL集群，然后有一个活动来了，你需要给线上某库的某数据表添加一列，并且这个表里面的数量非常之庞大。
添加一列是需要获取表锁的，并且庞大的数据量让你alter table异常缓慢，在获取表锁的过程中，正常的DML也会被阻塞。这时你就得考虑无损DDL，比如golang的ghost。
ghost工具的特点是，它需要预执行一些SQL，目的是先校验一下你的集群符不符合它的要求，为了不对线上产生影响，你肯定会想把这些预执行的SQL放到从库上执行。放在从库上执行固然没问题，但是你执行的SQL会产生bin log。出现新的GTID（这里只需要理解成是一个事务的唯一标识就行）更要命的是，主库中没有这些GTID。
当主从都正常运行时，主从bin log不一致没关系，但是当从库宕机的时候，从库重启会将自己的GTID集合发送给主库，由于从库多出来一部分主库没有的GTID，会导致该从库不能再次加入集群。

参数 sql_log_bin就能解决这个问题。
sql_log_bin变量控制是否为当前会话启用到二进制日志的日志记录（假设二进制日志本身已启用）。默认值为ON。要为当前会话禁用或启用二进制日志记录，请将会话sql_log_bin变量设置为 OFF或ON。
全局sql_log_bin变量是只读的，无法修改。