binlog

binlog
- binlog_format
  - statement
  - row
  - mixed
- my.cnf
  - 配置binlog
  - 相关参数
- lock
  - 配置1
  - 配置2

mysql的binlog日志作用是用来记录mysql内部增删改等对mysql数据库有更新内容的记录（对数据库进行改动的操作; 对数据库查询的语句如show，select开头的语句，不会被binlog日志记录，最大的作用是用来数据增量恢复和主从库复制;

binlog的三种格式

raw(行)
statement(语句)
mixed

binlog_format

statement

基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数,last_insert_id()，以及user-defined functions(udf)等会出现问题)...

=>因为这些函数执行是依赖context/环境变量,由于row是基于每一行的变化来记录的，所以不会出现类似问题

row

不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

ROW格式会记录每行记录修改的记录，这样可能会产生大量的日志内容,比如一条update语句修改了100条记录，那么这100条记录的修改都会被记录在binlog日志中，这样造成binlog日志量会很大，这种日志格式会占用大量的系统资源,myslq8.0安装后默认就是这种格式。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题.

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨

mixed

实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

my.cnf

配置binlog

[mysqld]

log-bin= /data/binlog/mysql-bin.log  #binlog位置及名称
log-bin-index=/data/binlog/mysql-bin.index  #自定义binlog路径时需要加上的选项
binlog-format="STATEMENT"  #binlog日志格式
expire_logs_days = 20      #binlog过期清理时间
max_binlog_size = 200M     #单个binlog日志文件大小
sync-binlog = 1            #开启binlog同步，将二进制日志同步到磁盘，保证数据完整性，在主从复制中也用到。
binlog-do-db=db1  # 需要记录binlog的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=db2  # 不需要记录binlog的数据库库名，如果备份多个数据库，重复设置这个选项即可

相关参数

show variables like '%binlog%';

binlog_cache_size：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的binlogcache空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。
max_binlog_cache_size：和”binlog_cache_size”相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan’max_binlog_cache_size’bytesofstorage”的错误。
max_binlog_size：Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务结束。这一点和Oracle的Redo日志有点不一样，因为Oracle的Redo日志所记录的是数据文件的物理位置的变化，而且里面同时记录了Redo和Undo相关的信息，所以同一个事务是否在一个日志中对Oracle来说并不关键。而MySQL在Binlog中所记录的是数据库逻辑变化信息，MySQL称之为Event，实际上就是带来数据库变化的DML之类的Query语句。
sync_binlog：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行 n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多

sync_binlog=0（写入内容后持久化操作由操作系统来做）
sync_binlog=1（写入内容后立即执行fsync操作同步到磁盘上）
sync_binlog=N（写入N个事务才执行一次fsync操作）

lock

间隙锁是在可重复读隔离级别下才会生效的; 用来解决幻读的问题.

record_lock
gap_lock
next_key_lock

配置1

RC(read commited) + binlog_format=row;

read-commit锁级别低; 但是只有行锁,不支持gaplock(间隙锁);可能存在不可重复读和幻读的问题
数据和日志可能存在不一致问题,所以使用binlog_format=row; 便于日志相关操作

配置2

RR(repeadted read) + binlog_format=mixed

mysql推荐的配置2:

能解决所有并发问题
记录日志详细

mysql-binlog-1