一、mysqlbinlog简介

binlog又叫二进制日志文件，它会将mysql中所有修改数据库数据的Query以二进制的形式记录到日志文件中,如：create,insert,drop,update等;（对于select操作则不会被记录到binlog里，因为它并没有修改数据库的数据）。binlog一般存储在数据目录下，并且命名为：mysql-bin.（这个可以在配置文件中修改my.cnf：log-bin=mysql-bin,就是文件名的前缀；mysqld在每个 binlog 名后面添加一个数字扩展名。每次启动服务器或刷新日志时增加文件的大小大于max_binlog_size，一个事务不会被拆分开）。

binlog主要是用于保证数据完整的，如主从备份，通过从binlog文件中读取操作来在salve机上进行同样的操作，保证主从备份，当然不可能每次都从开始的地方redo，所以每条记录都有一个时间截TIMESTAMP。

二、binglog的类型

binlog的格式有三种，这也反应了mysql的复制技术：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

 1mysql>showvariables like 'binlog_format'    #查看binlog的格式
 2使用mysqlbinlog解析的binlog：
 3
 4MIXED（STATEMENT）：
 5# at 193（开始位置）
 6#110708 10:03:06（时间截） server id（产生该事件的服务id） 1  end_log_pos（日志的结束位置） 280  Query（事件类型）  thread_id=10    exec_time=0     error_code=0
 7SETTIMESTAMP=1310090586/*!*/;
 8insert into tvalues(17)
 9/*!*/;
10
11ROW模式：
12BEGIN
13/*!*/;
14# at 174
15# at 214
16#110708 10:49:22server id 1  end_log_pos 214   Table_map: `test`.`t` mapped to number 14
17#110708 10:49:22server id 1  end_log_pos 248   Write_rows: table id 14 flags: STMT_END_F
18BINLOG '
19MnAWThMBAAAAKAAAANYAAAAAAA4AAAAAAAEABHRlc3QAAXQAAQMAAQ
20MnAWThcBAAAAIgAAAPgAAAAAAA4AAAAAAAEAAf/+MgAAAA
21'/*!*/;
22# at 248
23#110708 10:49:22server id 1  end_log_pos 317   Query  thread_id=1     exec_time=0     error_code=0
24SETTIMESTAMP=1310093362/*!*/;
25COMMIT
  • STATEMENT是基于sql语句级别的binlog,每一条修改数据的sql都会被保存到binlog里；
  • ROW是基于行级别的,他会记录每一行记录的变化,就是将每一行的修改都记录到binlog里面,记录的非常详细，但sql语句并没有在binlog里,在replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致的问题,但是有个致命的缺点日志量比较大.由于要记录每一行的数据变化,当执行update语句后面不加where条件的时候或alter table的时候,产生的日志量是相当的大。
  • MIXED:在默认情况下是statement,但是在某些情况下会切换到row状态，如当一个DML更新一个ndb引擎表，或者是与时间用户相关的函数等。在主从的情况下，在主机上如果是STATEMENT模式，那么binlog就是直接写now()，然而如果这样的话，那么从机进行操作的时间，也执行now()，但明显这两个时间不会是一样的，所以对于这种情况就必须把STATEMENT模式更改为ROW模式，因为ROW模式会直接写值而不是写语句（该案例是错误的，即使是STATEMENT模式也可以使用now()函数，具体原因以后再分析）。同样ROW模式还可以减少从机的相关计算，如在主机中存在统计写入等操作时，从机就可以免掉该计算把值直接写入从机。

以上类型的优缺点：

SBR 的优点：

  • 历史悠久，技能成熟
  • binlog文件较小
  • binlog中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况
  • binlog可以用于实时的还原，而不仅仅用于复制
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：
不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。调用具有不确定因素的 UDF 时复制也可能出疑问。运用以下函数的语句也不能被复制：

  • LOAD_FILE()
  • UUID()
  • USER()
  • FOUND_ROWS()
  • SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
  1. INSERT … SELECT 会产生比 RBR 更多的行级锁
  2. 复制须要执行 全表扫描(WHERE 语句中没有运用到索引)的 UPDATE 时，须要比 RBR 请求更多的行级锁
  3. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
  4. 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
  5. 存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事
  6. 确定了的 UDF 也须要在从服务器上执行
  7. 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
  8. 执行复杂语句如果出错的话，会消耗更多资源

RBR 的优点：
1. 任何情况都可以被复制，这对复制来说是最安全可靠的
2. 和其他大多数数据库系统的复制技能一样
1. 执行 INSERT，UPDATE，DELETE 语句时锁更少
2. 从服务器上采用多线程来执行复制成为可能
3. 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句

RBR 的缺点：
1. binlog 大了很多
2. 复杂的回滚时 binlog 中会包含大量的数据
3. 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写疑问
4. UDF 产生的大 BLOB 值会导致复制变慢
5. 不能从 binlog 中看到都复制了写什么语句(加密过的)
6. 当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生
7. 另外，针对系统库 mysql 里面的表发生变化时的处理准则如下：
8. 如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
9. 如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录。

MBR融合了两种记录模式的优点。故个人比较推荐使用MBR模式。

三、简单的使用binlog及查看修改其类型

1show binary logs;    #显示binlog文件
2purge binary logsto 'mysql-bin.**'  #删除到文件
3bin/mysqlbinlog binlogfile    #解析binlog文件

具体使用可以查看帮助文件，也可以参考官方用法：http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html#mysqlbinlog

利用binlog恢复数据：

1bin/mysqlbinlog  --start-datetime='2011-7-7 18:0:0'--stop-datetime='2011-7-7 20:07:13' data/mysql-bin.000008 |mysql -u root

查看及修改binlog的类型可以通过查看my.cnf文件，如混合型的设置如下：

1log-bin=mysql-bin
2#binlog_format=STATEMENT
3#binlog_format=ROW
4binlog_format=mixed

另外也可以通过查看binlog日志文件的内容，具体上面已进行讲解。 也可以通过查看sql状态来查看:

1mysql> show variables like '%log%';
2+-----------------------------------------+--------------------------------+
3| Variable_name                           | Value                          |
4+-----------------------------------------+--------------------------------+
5| back_log                                | 50                             |
6| binlog_cache_size                       | 32768                          |
7| binlog_direct_non_transactional_updates | OFF                            |
8| binlog_format                           | MIXED

不复启mysql重新加载my.cnf文件的情况下，修改mysqlbinlog类型的方法：

1mysql> SET SESSION binlog_format = 'STATEMENT';
2mysql> SET SESSION binlog_format = 'ROW';
3mysql> SET SESSION binlog_format = 'MIXED';
4mysql> SET GLOBAL binlog_format = 'STATEMENT';
5mysql> SET GLOBAL binlog_format = 'ROW';
6mysql> SET GLOBAL binlog_format = 'MIXED';

四、binlog记录的格式

每个binlog的开始都是由4个字节：fe 62 69 6e，后面三个字节就是bin。然后接下来的就是一条记录的内容它包括：Common-Header，这部分不同版本的大小不一样，4.0以上的都是19个字节。在这个之后就是BODY。

Common-Header格式：（单位：字节）

Timestamp(4) Type(1) Server_id(4) Total_size(4) End_log_pos(4) Flag(2)
  • Timestamp:从1970开始
  • Type:此log event type如FORMAT_DESCRIPTION_EVENT、QUERY、LOAD_EVENT等，其中每个binlog的第一条记录的类型都是FORMAT_DESCRIPTION_EVENT，它记录了该binlog的相关信息，如版本，这些信息对于后序分析binlog记录是有用的，所以对于任务要读取binlog的内容的工具都必须先读取第一条记录。QUERY包括我们经常操作的如：create,drop,update,insert等。
  • Server_id：创建这个事件的server id。防止循环主从导致的主机被从写。The master’s server id (is preserved in therelay log; used to prevent from infinite loops in circular replication).
  • Total_size：该记录的大小，包括common_header及body。
  • End_log_pos：此下一条记录的开始位置。也是此条记录结束位置的上一个字节。
  • Flag：标志位。

QUERY类型的记录：QUERY类型的记录除了开始的common-header之外，在body的开头是一个Post-header，然后之后才是真正的body内容。

Query Post-Header：（单位字节）

Thread_id(4) Exec_time(4) Db_len(1) Error_code(2) Status_var_len(2)
  • Thread_id：is used to distinguish temporary tables that belong to differentclients.
  • Exec_time：The time from whenthe query started to when it was logged in the binlog, in seconds.QUERY到达到这个binlog事件生成的时间间隔。
  • Db_len：当前数据库的名称长度。
  • Error_code：执行出错的错误号。