二进制日志简介

二进制日志记录数据库的变化，如创建表操作，表数据增删修改，以及可能会造成数据修改的事件（如没有匹配行的DELETE语句，除非使用ROW-base日志格式），及更新数据消耗的时长。二进制文件不记录SELECT、SHOW等不会修改数据的语句。

二进制日志的两个重要作用：

用于复制，主库提供数据更改的日志记录将被发送到从库，从库执行同样的操作保证主从数据一致。
根据二进制日志进行数据恢复，在备份恢复后，可从备份点重新执行二进制日志恢复到最新时间。

切换二进制日志文件：

启动MySQL服务器
使用flush logs刷新日志
当前二进制日志文件大小大于max_binlog_size，当事务比较大时，同一事务只能写入到同一个文件中，不能分割文件写入，因此可能出现二进制日志文件比max_binlog_size大。

二进制日志格式

STATEMENT
默认的MySQL的二进制日志格式，基于此复制称为statement-based replication(SBR)
ROW
基于此复制称为row-based replication(RBR)
MIXED
基于此复制称为mixed-based replication(MBR)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.04 sec)

修改binlog_format需要SUPER权限
mysql> SET GLOBAL binlog_format = 'ROW';

除了手动切换日志格式，Slave也可能自动切换。当主库使用ROW格式时，备库使用STATEMENT或MIXED，
备库将临时对该事务切换到ROW格式，复制完成将重新切换回之前格式。

会话修改二进制日志格式的情况：

会话中大量更新较小的语句可能使用基于ROW的记录
执行匹配许多行的WHERE子句的更新适合使用基于STATEMENT的记录
更新语句执行时间较长，但只有少数行记录被更新适合使用基于ROW的记录

下列情况将不可运行时修改二进制日志格式，如修改将报错：

在存储函数或者触发器中修改
NDB存储引擎启用
当前会话使用 RBR 模式，并且已打开了临时表

当使用InnoDB表且事务隔离级别是READ COMMITTED 或 READ UNCOMMITTED时，只能使用ROW格式。
如果将运行时修改日志格式为STATEMENT，将会报错因为InnoDB表不能插入操作。
当存在temporary表时，运行时不建议修改复制格式，因为临时表只会当SBR是才会写入日志，RBR不会
写入，MBR也会写入。
当二进制格式为ROW时，记录大量修改的语句仍可使用STATEMENT格式,如DDL语句CREATE TABLE、ALTER
TABLE、DROP TABLE。

当使用MIXED，下列情况将会自动从STATEMENT切换ROW:

函数中包含UUID()
表中AUTO_INCREMENT列被更新，并在触发器或存储函数中调用。
当视图创建需要依赖于RBR，如创建视图语句包含UUID()函数
调用UDF
非事务表执行INSERT DELAYED语句
如果一个会话存在临时表且语句使用ROW格式，则后续执行语句都将使用ROW(除访问临时表)，除非该会话中的临时表被删除。
当FOUND_ROWS() 或 ROW_COUNT()函数使用
当USER(), CURRENT_USER() 或 CURRENT_USER使用
当语句是指一个或多个系统变量
当LOAD_FILE()函数使用

mysql库的日志格式

直接修改mysql数据库表中数据的语句的日志格式根据binlog_format设置值，如INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, TRUNCATE TABLE。
直接修改mysql数据库表中数据的语句的日志格式使用STATEMENT而忽略binlog_format设置的值。如GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms)。

CREATE TABLE ... SELECT是DDL与DML的组合。CREATE TABLE使用STATEMENT格式而SELECT部分根据binlog_format设置的值

两者模式的优缺点

SBR 的优点：

从3.23已开始存在，历史悠久，技能成熟
binlog文件较小，当DELETE或UPDATE影响多行，日志文件占用较小的存储空间。
binlog中包含了所有数据库修改信息，可以据此来审核数据库
主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

SBR中不安全的语句
并非所有更新数据的语句都会在SBR中复制，尤其是包含不确定操作的时候。
如下列DML语句：
- 调用具有不确定因素的 UDF 或存储过程，如程序的返回值取决于多种因素而不仅仅包含入参
- DELETE或UPDATE语句使用LIMIT但不使用ORDER BY也是不确定语句
- 运用以下函数的语句也不能基于SBR复制：
  - LOAD_FILE()
  - UUID(), UUID_SHORT()
  - USER()
  - FOUND_ROWS()
  - SYSDATE() (除非主从启动时均指定了 --sysdate-is-now 选项)
  - GET_LOCK()
  - IS_FREE_LOCK()
  - IS_USED_LOCK()
  - MASTER_POS_WAIT()
  - RAND()
  - RELEASE_LOCK()
  - SLEEP()
  - VERSION()

当出现如下警告时，语句不能根据SBR正确复制

 [Warning] Statement is not safe to log in statement format.

INSERT ... SELECT 会产生比 RBR 更多的行级锁
UPDATE语句全表扫描(WHERE子句没有使用索引)，比 RBR 产生更多的行级锁
对于有 AUTO_INCREMENT 列的InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
对于复杂的语句，会在从服务器上重新执行，而 RBR 模式下，只会对那个发生变化的记录产生影响
如果从库产生错误，尤其是执行复杂语句时，SBR经过一段时间逐渐会增大主从数据误差范围。
存储函数在被调用的同时也会执行一次 NOW() 函数
确定性的 UDF 也必须在从服务器上执行
表定义必须主从库保持一致才行，否则可能会导致复制出错

RBR 的优点：

所有改变都可以被复制，这对复制来说是最安全的形式
和其他大多数数据库系统的复制原理类似
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁减少，可提高并发性：
- INSERT ... SELECT
- 包含 AUTO_INCREMENT 字段的 INSERT
- WHERE子句不包含索引列或者修改很少条记录的 UPDATE 或 DELETE 语句
从库执行 INSERT，UPDATE，DELETE 语句需要较少锁资源

RBR 的缺点：

RBR容易记录更多的数据
二进制日志被锁定更长时间来写入数据，可能导致并发问题
UDFs产生大量BLOB值会导致复制变慢
不能查看执行过什么语句，也不能看到从库接收执行什么语句
可使用mysqlbinlog指定--base64-output=DECODE-ROWS 和 --verbose查看执行语句
对于MyISAM表，从库基于RBR重新执行INSERT语句时锁粒度较大。因此使用RBR不支持对MyISAM表的并发插入

整理自网络

Svoid
2014-11-25

ITPub博客

MySQL二进制日志