Binlog日志详解

binlog基本概念

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如：更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作

binlog的特点

• binlog在MySQL的Server层实现(引擎共用)
• binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
• binlog不限制大小,追加写入,不会覆盖以前的日志
• 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看

开启Binlog日志有以下两个最重要的使用场景

• 主从复制：在主库中开启binlog功能，这样主库就可以把Binlog传递给从库，从库拿到binlog后实现数据恢复达到主从数据一致性
• 数据恢复：通过mysqlbinlog工具来恢复数据

binlog日志的三种模式

• row: 日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改

  优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题

  缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨

• statement：记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过相同的SQL再次执行。简称SQL语句复制

  优点：日志量小，减少磁盘IO，提升存储和恢复速度

  缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数
  

• mixed：以上两种模式的混合使用，一般会使用statement模式保存binlog，对于statement模式无法复制的操作时，使用row模式保存binlog，MySQL会根据执行的SQL语句选择写入模式

企业场景如何选择binlog的模式

• 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式statement
• 如果生产中使用MySQL的特殊功能较多的，可以选择mixed模式
• 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快

binlog写入机制

• binlog文件结构

  MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event , 比较常用的log event有： Query event 、 Row event 、 Xid event 等。binlog文件的内容就是各种Log event的集合
  

• binlog落盘策略

  binlog 的写入顺序: binlog cache (write) -> OS cache -> (fsync) disk

  write表示: 写入文件系统缓存,fsync表示持久化到磁盘的时机

  binlog刷数据到磁盘由参数sync_binlog进行配置

  • sync_binlog=0 的时候，表示每次提交事务都只 write,不 fsync
  • sync_binlog=1 的时候，表示每次提交事务都会执行 fsync
  • sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync

• binlog 写入流程
  

  • 根据记录模式和操作触发event事件生成log event
  • 事务执行过程中，先把日志(log event) 写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中
  • binlog cache，系统为每个线程分配了一片binlog cache内存 (每个线程都有自己的binlog cache，共用一份binlog文件)
  • 事务提交的时候，执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache

redo log 和 binlog的区别

• redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
• redo log是物理日志，记录的是“在XXX数据页上做了XXX修改”；binlog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句
• redo log是循环写的，空间一定会用完，需要write pos和check point搭配；binlog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志
• redo log作为服务器异常宕机后事务数据自动恢复使用，binlog可以作为主从复制和数据恢复使用。binlog没有自动crash-safe能力