SQL日志
1,binlog
1.1 特点
二进制日志binlog(服务层的日志),又称归档日志。
binlog是逻辑日志,记录的是SQL语句的原始逻辑,比如”给ID=2这一行的a字段加1 ",日志内容是二进制的,根据日记格式参数的不同,可能基于SQL语句、基于数据本身或者二者的混合
binlog主要记录数据库的变化情况(逻辑日志),内容包括数据库所有的更新操作。所有涉及数据变动的操作,都要记录进二进制日志中。
binlog可对数据进行复制和备份,常用作主从库的同步、数据回滚/恢复。
binlog有两种模式
statement 模式是记录sql语句,
row模式记录行的内容,更新前和更新后都有;
1.2 binlog 写入机制
事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 不能拆开,不论事务多大要确保一次性写入。
系统给 binlog cache 分配一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。超过参数规定大小,就暂存到磁盘。
事务提交时,执行器把 binlog cache 里完整事务写入 binlog 中,并清空 binlog cache
每个线程有自己 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 日志
1.3 binlog 三种日志格式
Statement:每一条会修改数据的sql都会记录在binlog中。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。)
缺点:statement 格式的 binlog 可能会导致