MySQL binlog日志介绍

binlog日志的内容

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据库本身并没有修改。然而，若UPDATE操作本身并没有导致数据库发生变化，那么该操作也可能会写入二进制日志。例如：

mysql> update test set id=3 where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0




mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000020
         Position: 2049
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 
1 row in set (0.00 sec)




mysql> show binlog events in 'mysql-bin.000020'\G;
*************************** 26. row ***************************
   Log_name: mysql-bin.000020
        Pos: 1868
 Event_type: Query
  Server_id: 1
End_log_pos: 1971
       Info: use `yyc`; update test set id=3 where id=2

上述例子可以看到，MySQL数据库首先进行了update操作，返回结果是changed 0，该操作并未导致数据库的数据改变。但通过命令show binlog events 可以看出在binlog中的确进行了记录。

如果用户想记录select和show操作，只能使用查询日志（一般不建议打开，只打开慢查询日志）。

binlog日志的作用

      二进制日志还包括执行数据库更改操作的时间等其他额外信息，binlog主要有以下作用。

      恢复（recovery）

      某些数据的恢复需要binlog。在一个数据库全备文件恢复后，可以通过binlog日志进行point-in-time恢复。

      复制（replication）

      原理和恢复类似，通过复制和执行binlog日志使一台远程的MySQL数据库（slave）与一台MySQL数据库（master）进行同步。

      审计（audit）

      可以通过binlog日志中的信息来进行审计，判断是否有对数据库进行攻击和非法操作。

binlog日志的格式

binlog_format是执行binlog格式的重要参数，MySQL5.1版本以前并没有这个参数。所有的binlog格式都基于SQL语句（STATEMENT）级别的，基于这个格式的binlog文件的复制和Oracle的Dataguard逻辑standby有点相似。

MySQL5.1开始引入binlog_format参数，参数可设置值有STATEMENT、ROW和MIXED。binlog日志录入格式的各自优缺点如下。

1. STATEMENT：每一条会修改数据的SQL都会记录在binlog中，记录是日志的逻辑SQL语句。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比ROW能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入ROW格式所产生的日志量还小于STATEMENT产生的日志量，但是考虑到如果带条 件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所 产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制，像一些特定函数功能，slave与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2.ROW:不再简单的记录SQL语句上下文相关信息，而是记录表的数据行更改情况，同Oracle的Dataguard物理standby有点相似。

优点：binlog中可以不记录执行的SQL语句的上下文相关的信息，仅需要记录那一条记录被修改成什么。所以ROW级别的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

一般的语句修改使用STATEMENT格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用ROW格式保存binlog,MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在STATEMENT和ROW之间选择一种。新版本的MySQL中对ROW模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以STATEMENT模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

使用ROW格式的可能情况有：

1）表存储引擎为NDB，这是对表的DML操作都会使用ROW格式；

2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()、LOAD_FILE()等不确定函数；

3）使用了 INSERT DELAY语句；

4）使用了用户定义函数（UDF）；

5）使用了临时表（temporary table）。