MySQL二进制文件(binlog)

二进制文件(binlog)记录对MySQL数据库执行更改的所有操作,但不包括SELECT和SHOW这类操作,因为这类操作没有改变数据。

为什么会有binlog?

首先 binlog 是 Server 层的日志模块,最初设计来是为了归档(备份)使用的。经过多个版本的发展,现在 binlog 主要有三个作用:

  • 恢复:提供 point-in-time 的恢复功能。通过全备+binlog,可实现恢复到指定时间的数据库数据。
  • 复制:主备复制(同步)的基础模块,主库写 binlog,再发送给备库执行,实现主备数据一致性。
  • 审计:用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入的攻击。

如何记录

下面以一条Update语句来介绍 binlog 是如何记录的。

mysql> update T set c=c+1 where ID=2;

update语句执行流程图

  1. 取得 ID=2 这一行(通过内存或磁盘读取)
  2. 这行的 c 值加1
  3. 更新到内存
  4. 写入 redo log(处于 prepare 阶段)
  5. 写 binlog
  6. 提交事务(处于 commit 状态)

这里涉及两阶段提交和 redo log 的内容,感兴趣的移步到此处

binlog 的写入时机是在数据更改后,事务提交前。

三种写入格式

binlog_format=STATEMENT

binlog 记录逻辑SQL语句。

当执行以下命令:

mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;

mysql> show binlog events in 'master.000001';

其中'master.000001'是binlog的文件名。

binlog 记录的内容如下图所示:

image

优点:省空间:只记录逻辑语句。

缺点:可能产生主备不一致。

mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;

上面的SQL,假如在主库使用索引 a,而备库使用索引 t_modified 会导致删除的记录不一样。

binlog_format=ROW

binlog 记录表的行更改情况。配合 READ COMMITED,获得更好的并发性。

查看 binlog 内容:mysql> show binlog events in 'master.000001';(其中'master.000001'是binlog的文件名)

image

解析 binlog 内容:使用 mysqlbinlog 命名解析 binlog 内容。

liang24@ubuntu:~$ mysqlbinlog -vv data/master.000001 --start-position=8900;

image

优点:记录原始数据;保证主备一致性;

缺点:

1、费空间:因为要保存原始数据;

例如:删除十万行数据,STATEMENT 格式只记录逻辑语句,而 ROW 会记录十万条日志,相当费空间。

2、费IO

例如:删除十万行数据,ROW 会记录十万条日志,而写 binlog 会耗费一定的 IO。

推荐使用 binlog_format=ROW:能记录原记录数据。误操作时,能够根据原数据进行恢复,例如 Delete 就转写成 Insert,Update 就把 Update 前的再执行一次。

binlog_format=MIXED

因为 STATEMENT 和 ROW 各有优缺点,因此 MySQL 新增了一种格式 MIXED,采用折中方案,MySQL 会判断采用 STATEMENT 还是 ROW 来记录 binlog。

如何使用

1、查看是否开启

mysql 默认是不开启 binlog 的。

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.00 sec)

从结果看到 log_bin=OFF,知道没有开启 binlog。

2、开启 binlog 日志

退出 MySQL,使用 vi 编辑器修改 MySQL 的 my.cnf 配置文件(vim /etc/my.cnf)

# server-id:表示节点名称
server-id=1
# log-bin:表示存储binlog日志的目录和binlog的文件名
log-bin=/var/lib/mysql/mysql-bin

3、重启 MySQL 及查看 binlog 开启状态

service mysql restart

登录 MySQL,输入 show variables like '%log_bin%'; 查看到 binlog=ON ;

mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| 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                            |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)

参考资料

posted @ 2020-12-04 10:02  大杂草  阅读(1178)  评论(0编辑  收藏  举报