MySQL二进制日志(binary log)总结

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

MySQL二进制日志(binary log)总结

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

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

MySQL二进制日志(binary log)总结

顺便总结一下二进制日志(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

MySQL二进制日志(binary log)总结

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

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

MySQL二进制日志(binary log)总结

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

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

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

MySQL二进制日志(binary log)总结

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

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

MySQL二进制日志(binary log)总结

5,二进制日志的清理

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

 

MySQL二进制日志(binary log)总结

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/1cb50a2861f149777b494e95e4963650.html