参考：binglog
二进制日志( binary log)记录了对 MySQL数据库执行更改的所有操作,但是不包括 SELECT和sHow这类操作,因为这类操作对数据本身并没有修改。然而,若操作本身并没有导致数据库发生变化,那么该操作可能也会写入二进制日志。
例如：
从上述例子中可以看到, MySQL数据库首先进行 UPDATE操作,从返回的结果看到Changed为0,这意味着该操作并没有导致数据库的变化。但是通过命令SHOW BINLOG EVENT可以看出在二进制日志中的确进行了记录。
如果用户想记录 SELECT和SHOW操作,那只能使用查询日志,而不是二进制
志。此外,二进制日志还包括了执行数据库更改操作的时间等其他额外信息。
总的来说,二进制日志主要有以下几种作用。
- 恢复( recovery):某些数据的恢复需要二进制日志,例如,在一个数据库全备
文件恢复后,用户可以通过二进制日志进行 point-in-time的恢复。 - 复制( replication):其原理与恢复类似,通过复制和执行二进制日志使一台远程的MySαL数据库(一般称为 slave或 standby)与一台MySαL数据库(一般称为master或 prImary)进行实时同步。
- 审计( audit):用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入的攻击。
通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name,则默认二进制日志文件名为主机名,后缀名为二进制日志的序列号,所在路径为数据库所在目录( datadir),如
二进制日志文件在默认情况下并没有启动,需要手动指定参数来启动。可能有人会质疑,开启这个选项是否会对数据库整体性能有所影响。不错,开启这个选项的确会影性能,但是性能的损失十分有限。根据 MySQL官方手册中的测试表明,开启二进制志会使性能下降1%。但考虑到可以使用复制( replication)和 point-in-time的恢复,这些性能损失绝对是可以且应该被接受的。
当使用事务的表存储引擎(如lnηoDB存储引擎)时,所有未提交( uncommitted)
的二进制日志会被记录到一个缓存中去,等该事务提交( committed)时直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由 binlog_ cache size决定,默认大小为32K。此外, binlog_ cache size是基于会话( session)的,也就是说,当一个线程开始一个事务时, MySQL会自动分配一个大小为 binlog_ cache size的缓存,因此该值的设置需要相当小心,不能设置过大。当一个事务的记录大于设定的binlog_ cache size时, MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设得太小。通过 SHOW GLOBAL STATUS命令查看 binlog_ cache_use， bin log cache disk_use的状态,可以判断当前 binlog cache size的设置是否合适。Binlog_ cache_use记录了使用缓冲写二进制日志的次数, binlog_ cache disk_use记录了使用临时文件写二进制日志的次数。
现在来看一个数据库的状态：
使用缓存次数为1，临时文件使用为0，看来32KB的缓存够用，没必要设置bilog_cache_size.
在默认情况下,二进制日志并不是在每次写的时候同步到磁盘(用户可以理解为缓冲写)。因此,当数据库所在操作系统发生宕机时,可能会有最后一部分数据没有写入进制日志文件中,这会给恢复和复制带来问题。参数 sync binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1,即 sync binlog=1表示采用同步写磁盘的方式来写进制日志,这时写操作不使用操作系统的缓冲来写二进制日志。synηc_ binlog的默认值为0,如果使用 iNnodB存储引擎进行复制,并且想得到最大的高可用性,建议将该值设为ON。不过该值为oN时,确实会对数据库的|O系统带来一定的影响。
但是,即使将sync_ binlog设为1,还是会有一种情况导致问题的发生。当使用
oDB存储引擎时,在一个事务发出 COMMIT动作之前,由于 sync binlog为1,因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在 MySQL数据库下次启动时COMM|T操作并没有发生,这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。这个问题可以通过将参数 [innodb support xa设为1来解决,虽然 innodb support_xa与ⅩA事务有关,但它同时也确保了二进制日志和 iNnodB存储引擎数据文件的同步。
参数 binlog-do-db和 binlog-ignore-db表示需要写入或忽略写入哪些库的日志。默认为空,表示需要同步所有库的日志到二进制日志。
如果当前数据库是复制中的save角色,则它不会将从 master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入,要设置log- slave- update。如果需要搭建 master=> slave=> slave架构的复制,则必须设置该参数。
binlog format参数十分重要,它影响了记录二进制日志的格式。在 MySQL5.1版本之前,没有这个参数。所有二进制文件的格式都是基于SQL语句( statement)级别的,因此基于这个格式的二进制日志文件的复制( Replication)和 Oracle的逻辑Standby有点相似。同时,对于复制是有一定要求的。如在主服务器运行rand、uuid等函数,又或者使用触发器等操作,这些都可能会导致主从服务器上表中数据的不一致( not sync)。另—个影响是,会发现noDB存储引擎的默认事务隔离级别是REPEATABLE READ。这其实也是因为二进制日志文件格式的关系,如果使用READ COMMITD的事务隔离级别(大多数数据库,如 Oracle, Microsoft sQl server数据库的默认隔离级别),会出现类似丟失更新的现象,从而岀现主从数据库上的数据不一致
MySQL5.1开始引入了 binlog format参数,该参数可设的值有 STATEM
ROW和M|XED。
(1) STATEMENT格式和之前的 MySQL版本一样,二进制日志文件记录的是日志的逻辑SQL语句。
(2)在ROW格式下,二进制日志记录的不再是简单的SQL语句了,而是记录表的
行更改情况。基于ROW格式的复制类似于 Oracle的物理 Standby(当然,还是有些区别)。同时,对上述提及的 Statement格式下复制的问题予以解决。从 MySQL5.1版本开始,如果设置了 binlog_ format为ROW,可以将 InnodB的事务隔离基本设为READ COMMITD,以获得更好的并发性。
(3)在MXED格式下, MySQL默认采用 STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式,可能的情况有:
1)表的存储引擎为NDB,这时对表的DML操作都会以ROW格式记录
2)使用了UUD()、USER()、 CURRENT USER()、 FOUND ROWS(),
ROW COUNT()等不确定函数。
3)使用了 INSERT DELAY语句
4)使用了用户定义函数(UDF)
5)使用了临时表( temporary table)。
