Mysql之binlog

ゝ一纸荒年。 2022-02-02 00:37 242阅读 0赞

[https://yq.aliyun.com/articles/669842][https_yq.aliyun.com_articles_669842]

## binlog的相关概念 ##

> binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。  
> binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。  
> binlog二进制日志包括两类文件：

*  索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
 *  日志文件（文件名后缀为.00000\*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

假设文件my.cnf中有这么三条配置

> log\_bin: on 打开binlog日志  
> log\_bin\_basename: bin文件路径及名前缀（/var/log/mysql-bin）  
>   
> log\_bin\_index：bin文件index（/var/log/mysql/mysql-bin.index）  
>

我们会在文件目录/var/log/mysql/下面发现两个文件mysql-bin.000001和mysql-bin.index

mysql-bin.index就是我们所说的索引文件。记录哪些文件是日志文件，内容如下：

./mysql-bin.000001

日志文件在innodb里分为两部分，一部分在缓存中，一部分在磁盘上。业界有个词叫刷盘，就是指将缓存中的日志刷到磁盘上。跟刷盘有关的参数有两个：sync\_binlog和binlog\_cache\_size. 这两个参数作用如下：

binlog\_cache\_size: 二进制日志缓存部分的大小，默认值32k  
  
sync\_binlog=\[N\]:表示写缓冲多少次，刷一次盘，默认值为0

注意两点：

*  1)binlog\_cache\_size设置过大，会造成内存浪费。binlog\_cache\_size设置过下，会频繁将缓冲日志写入临时文件。
 *  2)sync\_binlog=0:表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘，这个性能最好。  
      
    sync\_binlog=1,代表每次事务提交时就会刷新binlog到磁盘。sync\_binlog=N,代表每N个事务提交会进行一次binlog刷新。sync\_binlog=N，数据库在操作系统宕机的时候，可能数据并没有同步到磁盘，于是再次重启数据库，会带来数据丢失问题。  
      
    \-当sync\_binlog=1，事务在Commit的时候，数据写入binlog，但是还没写入事务日志(redo log和undo log)。此时宕机，重启数据库，数据被回滚。但是binlog里已经记录，这里存在不一致问题。这个事务日志和binlog一致性的问题，大家可以查询mysql的内部**XA协议**，该协议就是解决这个一致性问题的。

binlog是以事件形式记录的。Innodb和mysiam最显著的区别就是一个支持事务，一个不支持事务。binlog不仅记录innodb日志，在myisam中，也一样存在binlog.

## 三个用途：恢复、复制、审计 ##

恢复：  
  
复制：

graph LR
    write-->Master
    Master-->Slave

主库有一个log dump线程，将binlog传个从库。从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log, SQL线程从relay log里面读取内容，写入从库的数据库  
  
审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

## 四个常识 ##

常识一：binlog常见格式

<table> 
 <thead> 
  <tr> 
   <th>format</th> 
   <th>定义</th> 
   <th>优点</th> 
   <th>缺点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>statement</td> 
   <td>记录的是修改SQL语句</td> 
   <td>日志文件小，节约IO, 提高性能</td> 
   <td>准确性差，对一些系统函数不能转却复制或不能复制，如now()、uuid()等</td> 
  </tr> 
  <tr> 
   <td>row</td> 
   <td>记录的是每行实际数据的变更</td> 
   <td>准确性强，能准确复制数据的变更</td> 
   <td>日志文件大，较大的网络IO和磁盘IO</td> 
  </tr> 
  <tr> 
   <td>mixed</td> 
   <td>statement和row模式的混合</td> 
   <td>准确性强，文件大小适中</td> 
   <td>有可能发生主从不一致问题</td> 
  </tr> 
 </tbody> 
</table>

目前业界推崇的是row模式

常识二：怎么查看binlog  
  
binlog本身是一类二进制文件。二进制文件更省空间，写入速度更快，是无法直接打开来查看的。  
  
因此mysql提供了命令mysqlbinlog进行查看。  
  
一般的statement格式的二进制文件，用下面命令就可以

mysqlbinlog mysql-bin.000001

如果是row格式，加上-v或者-vv参数就行，如

mysqlbinlog -vv mysql-bin.000001

常识三:怎么删binlog

1.  使用reset master, 该命令将会删除所有日志，并让日志文件重新从000001开始。
2.  使用命令

PUGER {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

例如

purge master logs to "binlog_name.00000X"

将会清除00000X之前的所有日志文件  
3) 使用expire\_logs\_days=N选项指定过了多少天日志自动过期清空

常识四：binlog常见参数

<table> 
 <thead> 
  <tr> 
   <th>参数名</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>log_bin={on，off，base_name}</td> 
   <td>指定是否启用记录二进制日志或者指定一个日志路径</td> 
  </tr> 
  <tr> 
   <td>sql_log_bin={on，off}</td> 
   <td>指定是否启用记录二进制日志</td> 
  </tr> 
  <tr> 
   <td>expire_logs_days</td> 
   <td>指定自动删除二进制日志的时间，即日志过期时间</td> 
  </tr> 
  <tr> 
   <td>log_bin_index</td> 
   <td>指定mysql-bin.index文件的路径</td> 
  </tr> 
  <tr> 
   <td>binlog_format={mixed，row，statement}</td> 
   <td>指定二进制日志基于什么模式记录</td> 
  </tr> 
  <tr> 
   <td>max_binlog_size</td> 
   <td>指定二进制日志文件最大值</td> 
  </tr> 
  <tr> 
   <td>binlog_cache_size</td> 
   <td>指定事务日志缓存区大小</td> 
  </tr> 
  <tr> 
   <td>max_binlog_cache_size</td> 
   <td>指定二进制日志缓存最大大小</td> 
  </tr> 
  <tr> 
   <td>sync_binlog = {0/n}</td> 
   <td>指定写缓冲多少次，刷一次盘</td> 
  </tr> 
 </tbody> 
</table>

[https_yq.aliyun.com_articles_669842]: https://yq.aliyun.com/articles/669842