MySQL binlog解析和使用

binlog：二进制日志，记录数据更改的语句。因此记录了所有DML（数据操作语言，即增删改查）和DDL（数据定义语言，即create、alter、drop等），但是不记录select、show。也不记录DCL（数据库控制语言，即授权、取消权限等）

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog使用场景

• 主从复制：MySQL Replication在Master端开启binlog，Master把它的binlog传递给slaves来达到master-slave数据一致的目的

• 数据恢复：通过mysqlbinlog工具恢复数据

写Binlog的时机

对于支持事务的引擎如InnoDB，必须提交了事务才会去记录binlog。binlog什么时候刷新到磁盘和参数sync_binlog有关

• sync_binlog=0，则mysql不控制binlog的刷新，由文件系统控制它缓存的刷新
• sync_binlog≠0，则每sync_binlog次事务刷新binlog到磁盘
• sync_binlog=1，则意味着每一条事务刷新一次binlog到磁盘，最安全，因为系统故障时最多丢失一个事务，但影响性能。

binlog格式

有三种不同的格式来记录日志：

• STATEMENT：基于SQL语句的复制。每一条修改的数据的SQL都会记录在binlog中。
  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。
  • 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外一些特定函数的功能在slave和master要保持一致会有很多相关问题。
• ROW：基于行的复制，不记录sql语句上下文相关信息，仅保存哪条记录被修改。
  • 优点：ROW的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
  • 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
• MIXED：混合模式复制，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

打开binlog

开启binlog日志大概有1%性能损耗

在my.cnf文件中配置log-bin选项。

• 在[mysqld]区块添加： log-bin=mysql-bin（表示启动binlog。如果没有给定值，写成log-bin=，则默认名称为主机名；也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin） binlog_format=MIXED（设置binlog格式，如此时是MIXED格式）

• 重启mysqld服务

• 查看binlog日志是否开启

  mysql> show variables like 'log_%'

两种文件

binlog日志包含两类文件

• 二进制日志索引文件，后缀为.index，内容为当前的binlog文件列表
• 二进制日志文件，后缀为.00000*，记录所有DDL和DML语句（除了select、show查询语句）

常用操作命令

1. 查看所有binlog日志列表：

   show master logs;

   

2. 查看最后一个binlog日志文件名称，和最后一个操作事件的pos点

   show master status;

   

3. 刷新日志，会导致产生一个新编号的binlog日志文件

   flush logs;

   注：每当mysqld服务重启时，会自动执行此命令

4. 清空所有binlog日志

   reset master;

查看binlog日志

注意：

• binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开

• binlog日志与数据库文件在同目录中

• 在Mysql5.5以下版本使用mysqlbinlog命令时如果报错，就加上"--no-defaults"选项

1. mysqlbinlog命令（用Shell方式）

• 格式：

  mysqlbinlog [options] log_file ...

• 例：

  mysqlbinlog mysql-bin.000002

  解释：

  • position：位于文件的位置，即第一行的”at 624“，说明该事件记录从文件的624字节开始
  • timestamp：事件发生的时间戳，即第二行的”160925 21:29:53“
  • server id：服务器标识（1）
  • end_log_pos：表示下一个事件记录开始的位置（即当前事件记录的结束位置+1）
  • type：事件类型（Query）
  • thread_id：执行该事件的线程id，即”thread_id=3“
  • exec_time：事件执行花费的事件（0）
  • error_code：错误码，0代表没有发生错误

2. 用show binlog events方式查看（用MySQL方式）

• 格式：

  mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

  • IN 'log_name'：指定要查询的binlog文件名（不指定就是第一个binlog文件）

  • FROM pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）

  • LIMIT[offset]：偏移量(不指定就是0)

  • row_count :查询总条数（不指定就是所有行）

• 例：

  mysql> show binlog events in 'mysql-bin.000002'\G

注：第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式，其余的Event按照第一个Event的格式版本写入

• show binlog events的其他使用实例

  • 查询第一个最早的binlog日志：

    show binlog events\G;

  • 指定查询mysql-bin.000002这个文件

    show binlog events in 'mysql-bin.000002'\G;

  • 指定查询mysql-bin.000002这个文件，从pos点:624开始查起：

    show binlog events in 'mysql-bin.000002' from 624\G;

  • 指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

    show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

  • 指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。

    show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;