MySQL之binlog入门

简介

binlog 是 MySQL server 层维护的一种二进制日志，其记录了对 MySQL 数据更新或潜在更新的 SQL 语句，并以事务的形式保存在磁盘中

作用

• 复制：MySQL 主从复制中用到的就是 binlog，开启记录后 master 把自己的二进制日志传递给 slaves 并回放，来达到 master-slave 数据一致的目的
• 数据恢复：mysqlbinlog 工具通过 binlog 的记录恢复数据
• 增量备份

binlog 的管理

在配置文件（Windows 平台下的 my.ini）中配置开启 binlog：

log-bin=存放binlog的路径目录和基本文件名

binlog 开启后，除了可以在配置文件中查看，还可在 MySQL 命令行中查看：

show variables like '%log_bin%';

开启 binlog 后，MySQL 默认会在数据目录下产生 log-bin.n 文件（具体的 binlog 信息）以及 log-bin.index 索引文件（记录 binlog 的文件列表）

MySQL 命令行中查看 binlog 文件列表：

show binary logs;

MySQL 命令行中查看当前binlog 的状态：

show master status;

MySQL 命令行中清空 binlog 日志文件：

reset master;

binlog 的内容

默认情况下，binlog 是二进制文件，无法直接查看，可以通过两种方式查看：

• 使用 MySQL 官方提供的 binlog 工具 mysqlbinlog：
  • cmd 终端中：
    /usr/bin/mysqlbinlog mysql-bin.000007
  • 常用参数：–start-position、–stop-position 等
• 直命令行解析
  • MySQL 命令行中：
    show binlog events in 'mysql-bin.000007' from 1190 limit 2
  • 参数说明：[from] position [LIMIT [offset,] row_count]

binlog 格式

MySQL binlog 日志有 Row、Statement、Mixed 三种格式，可在配置文件中进行配置

• Statement-Based：

  • 基于 Statement 的日志记录所有更改数据或修改表结构的 SQL 语句，通过--binlog-format=STATEMENT启用
  • 某些非确定性的语句可能对于复制不安全，此时 MySQL 会提示"Statement may not be safe to log in statement format"

• Row-Based：

  • 基于 row-based 的情况下，master 会记录指示表的单行如何受到影响的事件，因此，每个表包含一个主键确保每行能被高效识别非常重要。

• Mixed-Based：

  • 默认情况下使用 statement-based 的日志记录，部分确定的情况下，MySQL 会自动切换到 row-based 模式

恢复

数据恢复是 binlog 两大主要作用之一，实质上就是让 MySQL 将保存在 binlog 指定范围内的 SQL 语句重新执行一遍，接下来通过实例演示：

1. 首先，查看下 binlog 的状态
   

2. 查看下用于演示的课程表的信息，可见表里有三条数据
   

3. 从表里删除一条记录
   

4. 再记录下当前 binlog 的位置
   

5. 根据两次 binlog 信息生成恢复文件 recover.sql
   

6. 插入一条数据，模拟数据被污染的情况
   

7. 恢复数据
   

8. 最后查看表的内容，测试表内容成功恢复到了删除数据后的状态
   

参考：

Working with MySQL Logs (Part 2): the Binary Log

大佬博客