MySql的binlog

1.什么是binlog

二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中

2.binlog的作用

有两个主要作用：复制（Master-Slave 主从同步）、恢复和审计

3.binlog相关变量

变量名称	变量含义	查看变量语句
log_bin	binlog开关	show variables like 'log_bin%';
log_bin_basename	binlog文件基础名字和所在路径	show variables like 'log_bin%'；
binlog_format	binlog日志的格式	show variables like 'binlog_format';

4.binlog三种日志格式

格式类型	特点
ROW	不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨
STATEMENT	每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
MIXED	以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式

5.查看binlog日志

# 查看所有binlog日志 
mysql> show binary logs;
# 查看某个binlog文件内容
mysql> show binlog events in '路径\binlog文件名'\G;

6.binlog记录的内容

一个事务的 binlog 是有完整格式的

statement 格式的 binlog，会记录所有会修改数据的sql

statement格式的binlog.jpg
row 格式的 binlog

row格式binlog示例的详细信息.jpg

可以看到，row 格式与 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一样的。但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows
①Table_map event，用于说明接下来要操作的表是 test 库的表 t
②Delete_rows event，用于定义删除的行为
其实，我们通过图上图是看不到详细信息的，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。因为图 5 中的信息显示，这个事务的 binlog 是从 8900 这个位置开始的，所以可以用 start-position 参数来指定从这个位置的日志开始解析

mysqlbinlog  -vv data/master.000001 --start-position=8900;

row格式binlog示例的详细信息.jpg

在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 是有办法验证事务 binlog 的完整性的

7.my.cnf配置binlog

binlog_format           = MIXED                         // binlog日志格式，建议使用mixed                                                             
log-bin                 = /data/mysql/mysql-bin.log     // binlog日志文件
expire_logs_days        = 7                             // binlog过期清理时间
max_binlog_size         = 100m                          // binlog每个日志文件大小
binlog_cache_size       = 4m                            // binlog缓存大小
max_binlog_cache_size   = 512m                          // 最大binlog缓存大小

8.binlog写入机制

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中
一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

binlog写盘状态.jpg
可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS
write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync
在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值
将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志