MySQL二进制日志（binary log）总结

共计 3501 个字符，预计需要花费 9 分钟才能阅读完成。

今天无意中发现了一个云栖社区举行的 MySQL“第一季：挑战玄惭之 慢 SQL 性能优化赛”，在测试服务器上执行其测试脚本写入数据的时候报错提示如下，
Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage,increase this mysqld variable and try agagin

提示 max_binlog_cache_size 空间不足，因为开启了二进制日志，之前是默认设置没有大批量的事务性操作，没有遇到该问题，这一次一开始就遇到一个较大的事务性操作就失败了。
之后修改 binlog_cache_size 的大小之后，问题解决。

由于使用的是默认的 innodb 引擎，开启了二进制日志，
对于事务性的操作，是要事物完成的时候写入二进制日志，事物提交之前，执行的写入性操作会被缓存起来，直到整个事物完成，mysqld 进程会将整个事物写入二进制日志。
当事物开始的时候，会按照 binlog_cache_size 系统变量指定的值分配内容空间，如果指定的 binlog_cache_size 缓存空间不够，执行的事务性操作回滚并提示失败。

顺便总结一下二进制日志（binary log）以及其相关参数信息

什么是二进制日志？
用来记录操作 MySQL 数据库中的写入性操作（增删改，但不包括查询），相当于 sqlserver 中的完整恢复模式下的事务日志文件。
二进制日志的作用？
1，用于复制，配置了主从复制的时候，主服务器会将其产生的二进制日志发送到 slave 端，slave 端会利用这个二进制日志的信息在本地重做，实现主从同步
2，用户恢复，MySQL 可以在全备和差异备份的基础上，利用二进制日志进行基于时间点或者事物 Id 的恢复操作。原理雷同于主从复制的日志重做。
 

二进制日志（binary log）的相关参数信息

1，开启二进制日志
开启二进制日志，需要制定一个 log-bin 参数的路径，也即：log_bin=/var/lib/mysql/mysql-bin
开始二进制日志之后会自动生成一个管理二进制日志的 log_bin_index 文件。log_bin 也显示为 on

2，二进制日志文件的格式

二进制日志的格式由参数 binlog_format 控制，二进制日志有三种模式，基于语句 (statement) 的，基于行的（row），以及结合前两者的混合模式（mixed）
基于语句的二进制功能有一些硬伤（个人认为），比如同样的 update 语句中的利用当前时间的 now 更新操作，在主服务器和从服务器上，主从复制得到的结果是不一样的。
基于行的二进制解决了语句语句的一些缺陷，但是某些情况下回产生大量的日志，比如一个 update 操作更新了 100W 行数据，如果是基于行的二进制日志，结果就是产生了 100W 条日志
基于混合模式的，结合上述两种方式的优点。
可以在配置文件中设置：binlog_format = MIXED

3，二进制日志的记录时机

二进制日志记录可以使同步的，也即事物提交之后就写入二进制日志，也可以是异步的，由操作系统的磁盘缓存觉得什么时候写入磁盘。
由参数 sync_binlog= n 来控制，设置 sync_binlog = 1 的话，表示最高安全级别的写入（但也不能保证不丢失任何事物日志），相当于是一种安全写入模式，不过对性能有一定的影响。
个人觉得，如果是事务性的引擎，本身就是为了保证事物安全的，没理由不把 sync_binlog 设置为 1。

据说设置为 sync_binlog 设置为 1 也会潜在丢失一个事务日志的可能性，但是还没想明白为什么会丢失，因为既然是事务性引擎，还有一层 undo 或者 redo 日志在做后盾啊？

4，二进制日志的单个文件大小

二进制日志的大下就是单个日志文件的最大限制，正常情况下都不会超过设置的最大文件的大小限制，超过设置的最大限制之后，会发生日志滚动，也即重新生成一个二进制日志文件。
max_binlog_size  = 100M
这里显示的 104857600 单位是字节，也即 104857600/1024/1024 = 100M

5，二进制日志的清理

二进制日志滚动之后会生成新的文件来存储日志，日志文件逾期之后会自动删除，否则会产生源源不断的日志文件
比如可以设置过期时间为 2，可配值为：expire_logs_days = 2，超过两天的二进制日志会被自动删除。
可以通过命令 show master logs 查看当前的二进制日志文件个数

6，二进制日志文件的滚动

1）正常情况下，记录满之后，自动滚动，后缀名 +1
2）重启 mysql 服务之后，自动滚动，不管时候记录满
3）手动滚动，执行 flush logs 命令，如下执行 flush logs 之后，重新生成了一个二进制日志文件

4）手动删除二进制日志

可以通过命令purge binary logs to fileName 删除指定 fileName 之前的文件

可以通过命令 purge binary logs before ‘2017-03-10 10:10:00’ 删除指定时间之前的文件

删除指定日志 purge binary logs before date_sub(now(), interval 7 day);
潇湘大神是 purge master logs before date_sub(now(), interval 7 day), 应该是一个效果（binary 和 master 关键词）？

7，二进制日志的绑定（或者排除）的数据库

可以设置某些数据库开启二进制日志，或者某些数据库不开启二进制日志
# binlog_do_db: 设置 master-slave 时使用;
# binlog-ignore-db: 设置哪个数据库不记录日志;
MySQL5.7.18 中设置了（my.cnf 中配置了），但是查询的时候好像没用?

8，二进制日志的缓存以及缓存大小配置

binlog_cache_size 的大小，一开始提到的问题，当事物开始的时候，会按照 binlog_cache_size 系统变量指定的值分配内容空间，如果指定的 binlog_cache_size 缓存空间不够则会报错并回滚事物
这里显示的记录的单位同样是字节，除以两个 1024 之后就是以 MB 为单位的容量了，这里的 20971520 /1024/1024 就相当于 20MB 了。
如果有较大的事务性操作，比如在测试的时候，必须要将此缓存设置的相对较大一些，否则语句无法成功执行

max_binlog_cache_size 语 binlog_cache_size 的区别在于前者是实例级别的 cache，后者是 Session 级别的 cache，如果并发量很大，就需要考虑将 max_binlog_cache_size 设置的稍微大一些。
max_binlog_cache_size 默认是是 4GB，最大值也是 4GB，这里为了测试设置的是 100MB（104857600/1024.0/1024.0）

  max_binlog_cache_size 设置的最大内存大小为 4GB，如果服务器内容较大，比如 128GB 或者更大，max_binlog_cache_size 默认为最大也无伤大雅，因为要保证并发成功写入。
至于对于 Session 级别的 binlog_cache_size 大小，可以根据业务情况自行调整，个人觉得设置的稍微大一点也问题不大，毕竟，有一些定时作业之类的数据提取或者 merge 之类的操作可能会产生大量的日志。
据说是可以通过查看 binlog_cache_disk_use 与 binlog_cache_use 来判断 binlog_cache_size 是否需要调整。
但是在 MySQL5.7.18 中并没有发现这个参数

9，二进制日志其他参数

max_binlog_stmt_cache_size 针对非事务语句，非事务性的参数暂不关心它了
记得某次看到过某大师说过，innodb 引擎优势不仅仅在事务性的支持上，与非事物引起的 myisam 引擎相比，读取性能上差距越来越小，MySQL 因此将 innodb 设置为默认引擎。
放弃 myisam，投奔 innodb 是正道。
binlog_checksum 用作复制的主从校检。暂时没有研究过这个参数，暂不论

总结：

MySQL 二进制日志不仅仅作用于功能性（master-slave 复制）的，还作用于安全性（二进制日志）以及开启了二进制日志情况下的事务性操作，因此对于生产环境，可以认为是一个必不可少的配置。
同时，其各种参数又会影响到某些操作，因此二进制日志的参数要格外的重视，确保数据库在使用时在功能性和可用性上得到保证。