mysql binlog详解

最新推荐文章于 2024-09-13 09:47:57 发布

胖虎·

最新推荐文章于 2024-09-13 09:47:57 发布

阅读量948

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_38871362/article/details/125703423

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的二进制日志（binlog），包括其工作原理、记录内容、常见应用场景（主从复制与数据恢复）、关键配置变量（如max_binlog_size、binlog_cache_size和sync_binlog）以及binlog_format的选择。重点讲解了binlog与重做日志的区别，以及如何理解和管理这些日志以确保数据一致性与安全性。

摘要由CSDN通过智能技术生成

一、初步了解binlog

二进制日志（binnary log）以事件形式记录了mysql对数据库执行更改的所有操作。
binlog记录的是所有数据库表结构的变更（create，alter table）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
需要注意的一点是，即便update操作没有造成数据变化，也是会记入binlog。
binlog有两个常用的使用场景：

主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

数据恢复：通过mysqlbinlog工具来恢复数据。

show variables like 'log_bin'

请添加图片描述

参数max_binlog_size指定了单个二进制日志文件的最大值:

show variables like 'max_binlog_size'

请添加图片描述
InnoDB会将所有未提交的binLog写到一个缓存中，等事务提交后再将缓存刷新到文件。缓存大小有参数binlog_cache_size控制。

show variables like 'binlog_cache_size'

请添加图片描述
需要注意的是，该值是基于session的，每个事务都会分配一个大小为binlog_cache_size的缓存。当一个事务的记录大于该值，MySQL会把缓冲中的日志写入一个临时文

参数sync_binlog表示每写缓冲多少次就同步到磁盘：

show variables like 'sync_binlog'

请添加图片描述

N=1：表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志，每次事务提交都会写入文件。

N=0：表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

但是，即使将sync_binlog设为1，还是会有一种情况会导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数innodb_support_xa设为1来解决，虽然innodb_support_xa与XA事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

binlog_format是一个非常重要的参数，决定了记录二进制日志的格式：

show variables like 'binlog_format'

请添加图片描述
可选值有：

STATEMENT

记录SQL语句。日志文件小，节约IO，但是对一些系统函数不能准确复制或不能复制，如now()、uuid()等

ROW

记录表的行更改情况，可以为数据库的恢复、复制带来更好的可靠性，但是二进制文件的大小相较于STATEMENT会有所增加

MIXED

STATEMENT和ROW模式的混合。默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式。

二进制日志与重做日志的区别

二进制日志中也记录了InnoDB表的很多操作，也能实现重做的功能，但是它与重做日志们之间有很大区别。

二进制日志是MySQL Server层的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是InnoDB存储引擎层的，只记录该存储引擎中表的修改。

二进制日志记录的是逻辑日志。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。

二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件"(对于非事务表的操作，则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。

因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。而且redo log是并发写入的，不同事务之间的不同版本的记录会穿插写入到redo log文件中，例如可能redo log的记录方式如下： T1-1,T1-2,T2-1,T2-2,T2,T1-3,T1 。

事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。