介绍

binlog 记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:

  • 其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
  • 其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。

二进制日志包括两类文件:

  • 二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
  • 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

binlog 日志的开启

首先检查有么有开启。在 MySQL 命令行中直接输入 show variables like 'log_bin';,可以看到如下的结果:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

如上,笔者当前的服务是没有开启的。

笔者使用的是 Ubuntu 18.04,从 apt 仓库直接安装的 mysql 5.7 版,所以配置文件在 /etc/mysql/my.cnf,在该配置文件内加入以下内容:

[mysqld]
server-id=2020
log_bin=mysql-bin
binlog_format=ROW

然后重启 mysql 服务:

service mysql restart

再次查看 binlog 状态:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

发现已经开启了。

常用 binlog 日志操作命令

查看所有 binlog 日志列表

show master logs;

执行效果:

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

查看 master 状态

即最后(最新)一个 binlog 日志的编号名称,及其最后一个操作事件 pos 结束点(Position)值

show master status;

执行效果:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

刷新 log 日志

自此刻开始产生一个新编号的 binlog 日志文件。

flush logs;

注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

执行效果:

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

重置(清空)所有 binlog 日志

reset master;

执行效果:

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

binlog 查看

可以使用 mysqlbinlog 自带的命令查看:

mysqlbinlog binlog文件路径

不知道 binlog 文件路径可以在 mysql 命令行中使用 show variables like 'log_bin_%'; 找到:

mysql> show  variables like 'log_bin_%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
4 rows in set (0.00 sec)

如上,笔者的 binlog 日志就存储在 /var/lib/mysql/ 目录下,文件名前缀为 mysql-bin

$ ls -alh /var/lib/mysql/ | grep mysql-bin
-rw-r-----  1 mysql mysql 2.0K Mar 27 01:08 mysql-bin.000001
-rw-r-----  1 mysql mysql   19 Mar 27 01:02 mysql-bin.index

执行 mysqlbinlog 命令查看内容:

mysqlbinlog /var/lib/mysql/mysql-bin.000001

不过这种直接的方法输出的日志内容较多,难以查看,一般会在 mysql 命令行中用下面这个指令更多一点:

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> show binlog events IN 'mysql-bin.000001' LIMIT 1;
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |      2020 |         123 | Server ver: 5.7.29-0ubuntu0.18.04.1-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
1 row in set (0.00 sec)

看起来很稳。

这条语句可以将指定的 binlog 日志文件,分成有效事件行的方式返回,并可使用 limit 指定 pos 点的起始偏移,查询条数;

# A.查询第一个(最早)的binlog日志:
mysql> show binlog events; 

# B.指定查询 mysql-bin.000021 这个文件:
mysql> show binlog events in 'mysql-bin.000021';

# C.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起:
mysql> show binlog events in 'mysql-bin.000021' from 8224;

# D.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;

# E.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,偏移2行,查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10;

恢复 binlog

未完待续。

标签: mysql, binlog

添加新评论