特性

  • 属于Mysql Server的日志

  • 逻辑日志

    作用

  • MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。

  • MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

    写入逻辑

  • 事务执行过程中，先将binlog写入到binlog_cache中；事务提交后，写入到binlog 文件

  • 一个事务的binlog不能拆分

    binlog cache

  • 每个线程都会分配一块binlog cache，参数binlog_cache_size控制其大小

    binlog刷盘策略

    参数：sync_binlog

  • 值为0

    • 此时写入binlog文件，其实是写入到 OS cache内存缓存中
    • 此时mysql宕机，内存中的binlog也会丢失
  • 值为1
    • 此时写入binlog，会直接写入到磁盘文件中
    • 宕机不会丢失

  • 值为N（N>1）

    • 事务提交binlog 写入到OS cache中
    • 累积N个事务的日志后，才进行fsync，进行落盘

      生产使用

  • 比较常见的是将sync_binlog设置为100～1000

    binlog格式

    statement

    binlog statement.jpg

  • binlog_format=statement

  • binlog_statement.png
  • statement格式的binlog记录的是SQL语句原文
  • show warnings;上述的SQL语句是会提示警告的
    • 因为使用了limit，所以如果主从库使用的索引不一致，可能会导致删除的不是同一行记录

  • 缺点：有些SQL可能会导致主备库数据不一致

    • 不可用在上下文数据恢复：有些语句的执行结果是依赖上下文的
      • 比如insert 中使用now()，则statement日志会通过 set timestamp,来设置时间

        row

        binlog row.jpg

  • binlog_format=row

  • binlog_row.jpg
  • 和statement格式相比，row格式没有记录sql原文，而是记录了两个event: table_map、delete_rows
    • table_map: 说明接下来操作的表
    • delete_rows: 定义删除行为
  • row格式的binlog详细信息：借助mysqlbinlog工具
    • mysqlbinlog -w data/master.000001 —start-position=8900;
      • -w ：内容解析
    • binlog_row2.jpg
    • server id 表示日志来自哪个库
    • 记录了真是删除行的id
  • 缺点：要记录操作的具体行信息，占用空间大，也相对会更加消耗IO资源，影响执行速度

  • 优点：可以基于记录的日志回滚数据。MariaDB的Flashback工具就是基于这个原理

    mixed

  • 为何会有Mixed格式的binlog

    • 因为如果statement格式，有些SQL的binlog日志可能会导致主从库数据不一致
    • 如果使用row格式，占据空间又比较大。比如要用一个delete语句删除十万行数据，statement只记录sql，可能就十几个字节；row格式则需要把这十万行数据都记录到binlog中。
    • mixed格式是一个折中方案：mysql会判断如果SQL的statement格式日志会影响主从数据一致性，就使用row格式记录，否则就使用statement

      双M架构循环复制问题

      产生原因

  • 双M，即会有两个Master，互为主备

  • 循环复制：当MasterA生成的binlog发送给MasterB后，MasterB 解析执行SQL后也会生成binlog发送给A

    解决

  • 首先，规定两个主库的server id不一样

  • 一个备库在收到binlog并在重放的过程中，生成与原binlog相同的server id
  • 每个库在收到同步的binlog后，首先判断server id是不是自己的，如果是自己的，就丢弃不执行