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;

    img

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

    show master status;

    img

  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;