🪴 Obsidian Publish

Binlog 日志

6分钟阅读

Binlog 概念

binlog  是  逻辑日志，用于记录数据库执行的写入操作（查询不记录）信息，并且是顺序写。

binlog  属于  MySQL Server  层记录，和引擎层无关。

逻辑日志，属于 MySQL Server 层记录；binlog 记录语句的原始逻辑。
物理日志，属于 InnoDB 存储引擎产生的；redo log 记录某个数据页上做了什么修改。

当 MySQL 创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL 服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按 1 递增。即日志文件的个数与 MySQL 服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是 1GB），也会创建一个新的日志文件。

Binlog 作用

MySQL 主备从库&分库分表 和 MySQL 数据恢复。

Binlog 存储格式

binlog  日志有三种格式，可以通过  binlog_format  参数指定，具体参见 Binlog 存储格式。

Binlog 存储时机

binlog  的写入时机也非常简单，事务执行过程中，先把日志写到  binlog cache，事务提交的时候，再把  binlog cache  写到  binlog  文件中。

因为一个事务的  binlog  不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为  binlog cache。

系统给  binlog cache  分配了一片内存，每个线程一个。我们可以通过  binlog_cache_size 参数控制单个线程  binlog cache  大小。如果存储内容超过了这个参数，就要暂存到磁盘（ Swap ）。

Binlog 刷盘时机

write  和  fsync  的时机，可以由参数  sync_binlog  控制，默认是  0。

sync_binlog = 0

  • 每次提交事务都只  write，由系统自行判断什么时候执行  fsync。
  • 机器宕机，page cache  里面的 binglog 会丢失。

sync_binlog = 1

  • 每次提交事务进行  write + fsync。

sync_binlog = N

  • 每次提交事务都  write，但累积  N  个事务后才  fsync。
  • 在出现  IO  瓶颈的场景里，将  sync_binlog  设置成一个比较大的值，可以提升性能。
  • 机器宕机，会丢失最近  N  个事务的  binlog  日志。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

Binlog 配置、内容查看、恢复数据

查看与修改配置

查看配置 binlog 参数配置

show variables like '%log_bin%';

Binlog 日志.png|600

查看 binlog 格式

show variables like 'binlog_format';

修改 MySQL 的  my.cnf  或  my.ini  文件可以设置二进制日志的相关参数：

log-bin= # binlog文件位置
binlog_expire_logs_seconds=600
max_binlog_size=100M

命令行(只能)修改 session 级别

SET sql_log_bin=0;

查看当前二进制日志文件列表及大小

SHOW BINARY LOGS;

300|600

查看 binlog 日志内容

将行事件以伪 SQL 的形式表现出来

mysqlbinlog -v "/var/lib/mysql/binlog/xxx-bin.000002"

将行事件以伪 SQL 的形式表现出来，其中关闭 binlog 格式的语句。

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"

可查看参数帮助

mysqlbinlog --no-defaults --help

更方面的查询命令

mysql> show 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 : 查询总条数（不指定就是所有行）

还可以通过 MySQL Binlog 日志查看 来了解下。

删除二进制日志

MySQL 的二进制文件可以配置自动删除，同时 MySQL 也提供了安全的手动删除二进制文件的方法。
PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件。具体如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

反向链接