bin log是什么?bin log 全称binary log ,二进制日志文件,它记录了数据库所有执行的 DDL 和 DML 等数据库更新的语句,但是不包含select 或者show 等没有修改任何数据的语句。它是MySQL级别的日志,也就是说所有的存储引擎都会产生bin log ,而redo log 或者undo log 事务日志只有innoDB 存储引擎才有。
那bin log 有什么用呢? - 数据恢复,如果MySQL数据库意外挂了,可以利用
bin log 进行数据恢复,因为该日志记录所有数据库所有的变更,保证数据的安全性。 - 数据复制,利用一定的机制将主节点MySQL的日志数据传递给从节点,实现数据的一致性,实现架构的高可用和高性能。
所以bin log 对于数据备份、主从、主主等都都起到了关键作用。 bin log和redo log区别?看了上面的bin log 介绍,是不是感觉和事务日志redo log 特别像呢?也是在事务执行的时候记录日志,但是他们还是有区别的。 你知道redo log吗, 如果不了解的话请参考这篇文章:详解MySQL事务日志redo log_Mysql_脚本之家 (jb51.net)
我们现在从多个角度对比下他们俩究竟有什么不一样? 从使用场景角度来说: redo log 主要实现故障情况下的数据恢复,保证事务的持久性bin log 主要用于数据灾备、同步
从数据内容角度来说: redo log 是"物理日志", 记录的是具体数据页上做了什么修改bin log 是"逻辑日志", 记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 name 改为alvin”
从生成范围角度来说: redo log 是InnoDB 存储引擎生成的事务日志,其他存储引擎没有bin log 是MySQL Server生成的日志,所有的存储引擎都有
从生成时机角度来说: redo log 是在事务执行过程中就会writebin log 是在事务提交的时候write
bin log怎么写的?那bin log 是什么时候写的,写入的机制又是怎么样的呢? bin log 写入的整体流程如下图所示:
- 为了保证写的效率,会将事务的
bin log 先写到binlog cache 中,注意,这个cache 位于事务线程的内存中,主要是一个事务的bin log 不能被拆开,是一个整体 - 在提交事务的时候,将
binlog cache 中的数据统一写道文件系统缓存page cache 中,这个过程速度也很快 - 然后根据不同的策略,将文件系统缓存中的
bin log fsync刷到磁盘中,这里的策略后面详细讲解。
3种刷盘策略: bin log 和 redo log 类似,都有3种刷盘策略, bin log 的write和fsync时机是由参数 sync_binlog 控制,默认是 0 。
为0的时候,表示每次提交事务都只 write ,由系统自行判断什么时候执行fsync 。虽然性能得到提升,但是机器宕机,page cache 里面的 binglog 会丢失。 - 表示每次提交事务都会执行
fsync ,更加安全 - sync_binlog = N
- 可以设置为N(N>1),表示每次提交事务都write,但累积N个事务后才fsync
我们已经知道,事务执行时会同时记录redo log 和bin log 两种日志,那会有日志出错不一致问题吗? redo log 在事务执行过程中可以不断写入bin log 只有在提交事务时才写入
假如事务执行sql ,在写完redo log 日志后,bin log 日志写期间发生了异常,会出现什么情况呢? 由于bin log 没写完就异常,这时候bin log 里面没有对应的修改记录。因此,之后用bin log 日志恢复数据时,就会少这一次更新,恢复出来的这一行c值为0,而原库因为redo log 日志恢复,这一行c的值是1,最终数据不一致。 那有什么解决方案吗?二阶段提交方案。 为了解决两份日志之间的一致性问题,InnoDB存储引擎使用两阶段提交方案。将redo log 的写入拆成了两个步骤prepare 和commit 。 - 假如现在写入
bin log 时MySQL发生异常,这时候的redo log 还处于prepare 阶段,重启MySQL后,根据redo log 记录中的事务ID,发现没有对应的bin log 日志,回滚前面已写入的数据。 - 如果
redo log 在commit 阶段发生移除,但是能通过事务id找到对应的bin log 日志,所以MySQL认为是完整的,就会提交事务恢复数据。
bin log写到哪了?前面讲解了bin log 写入的过程,那么它写到了哪里去了呢? 可以通过命令show variables like '%log_bin%'; 查看bin log 最终输出的位置。 log_bin_basename : 是bin log 日志的基本文件名,后面会追加标识来表示每一个文件log_bin_index : 是binlog文件的索引文件,这个文件管理了所有的binlog文件的目录
通过 SHOW BINARY LOGS; 查看当前的二进制日志文件列表及大小,如下图: 修改MySQL的my.cfg 或my.ini 配置 1234#启用二进制日志log-bin=cxw-binbinlog_expire_logs_seconds=600max_binlog_size=100M log-bin : bin log 日志保存的位置binlog_expire_logs_seconds : bin log 日志保存的时间,单位是秒max_binlog_size : 单个bin log 日志的容量
bin log内容长啥样?我们已经知道了bin log 的位置了,那它里面的内容长什么样呢? 我们可以用show binlog events 命令工具查看bin log 日志中的内容。 1show 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 :查询总条数(不指定就是所有行)
bin log 格式 实际上bin log输出的格式类型有3种,默认是ROW类型,就是上面例子中的格式 - Statement格式:每一条会修改数据的sql都会记录在
bin log 中 - 优点:不需要记录每一行的变化,减少了
bin log 日志量,节约了IO,提高性能。 - 缺点:比如sql中存在函数如now()等,依赖环境的函数,会导致主从同步、恢复数据不一致
- ROW格式:为了解决Statement缺点,记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了
- 优点:清楚的记录下每一行数据修改的细节,不会出现某些特定情况下 的存储过程,或function无法被正确复制的问题。
- 缺点:比如对
ID<600 的所有数据进行了修改操作,那么意味着很多数据发生变化,最终导致同步的log很多,那么磁盘IO、网络带宽开销会很高。 - Mixed格式: 混合模式,即Statment、Row的结合版
- 对于可以复制的SQL采用Statment模式记录,对于无法复制的SQL采用Row记录。
|