乐趣区

关于java:666MySQL-的-binlog-的三种格式这么好玩

jiezi

MySQL 中的日志比拟重要的有 binlog（归档日志）、redo log（重做日志）以及 undo log，那么跟咱们本文相干的次要是 binlog，另外两个日志松哥未来有空了再和大家具体介绍。

1. binlog

binlog 咱们中文个别称作归档日志，如果大家看过松哥之前发的 MySQL 主从搭建，应该对这个日志有印象，当咱们搭建 MySQL 主从的时候就离不开 binlog（传送门：MySQL8 主从复制踩坑指南）。

binlog 是 MySQL Server 层的日志，而不是存储引擎自带的日志，它记录了所有的 DDL 和 DML(不蕴含数据查问语句) 语句，而且是以事件模式记录，还蕴含语句所执行的耗费的工夫等，须要留神的是：

  • binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，留神这个区别于 redo log 的物理日志（在某个数据页上做了什么批改）。
  • binlog 文件写满后，会主动切换到下一个日志文件持续写，而不会笼罩以前的日志，这个也区别于 redo log，redo log 是循环写入的，即前面写入的可能会笼罩后面写入的。
  • 一般来说，咱们在配置 binlog 的时候，能够指定 binlog 文件的有效期，这样在到期后，日志文件会主动删除，这样防止占用较多存储空间。

依据 MySQL 官网文档的介绍，开启 binlog 之后，大略会有 1% 的性能损耗，不过这还是能够承受的，一般来说，binlog 有两个重要的应用场景：

  • MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。
  • MySQL 数据恢复，通过应用 mysqlbinlog 工具再联合 binlog 文件，能够将数据恢复到过来的某一时刻。

2. 配置 binlog

为了演示不便，松哥这里在 Docker 中装置了 MySQL，咱们以此为例来开始明天的演示。如果小伙伴们还不懂 docker 的应用，能够在公众号后盾回复 docker，有松哥写的教程。

首先咱们在 docker 中装置好 MySQL，而后进入到容器中，通过如下命令能够查看 binlog 是否开启：

这个 OFF 就示意 binlog 是一个敞开状态，没有开启。

通过以下命令能够查看 binlog 日志的格局，如下：

能够看到，这个 binlog 的格局为 ROW。

这里就波及到一个问题，binlog 的格局。

2.1 binlog 的格局

binlog 有三种格局：

  • Statement（Statement-Based Replication,SBR）：每一条会批改数据的 SQL 都会记录在 binlog 中。
  • Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保留哪条记录被批改。
  • Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

2.1.1 Statement

Statement 模式只记录执行的 SQL，不须要记录每一行数据的变动，因而极大的缩小了 binlog 的日志量，防止了大量的 IO 操作，晋升了零碎的性能。

然而，正是因为 Statement 模式只记录 SQL，而如果一些 SQL 中蕴含了函数，那么可能会呈现执行后果不统一的状况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个后果了。

所以应用 Statement 格局会呈现一些数据一致性问题。

2.2.2 Row

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格局，Row 格局不记录 SQL 语句上下文相干信息，仅仅只须要记录某一条记录被批改成什么样子了。

Row 格局的日志内容会十分分明的记录下每一行数据批改的细节，这样就不会呈现 Statement 中存在的那种数据无奈被失常复制的状况。

不过 Row 格局也有一个很大的问题，那就是日志量太大了，特地是批量 update、整表 delete、alter 表等操作，因为要记录每一行数据的变动，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

2.2.3 Mixed

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格局，这种格局实际上就是 Statement 与 Row 的联合。

在 Mixed 模式下，零碎会主动判断该用 Statement 还是 Row：个别的语句批改应用 Statement 格局保留 binlog；对于一些 Statement 无奈精确实现主从复制的操作，则采纳 Row 格局保留 binlog。

Mixed 模式中，MySQL 会依据执行的每一条具体的 SQL 语句来区别对待记录的日志格局，也就是在 Statement 和 Row 之间抉择一种。

2.2 配置

接下来咱们来看看 binlog 的配置。

2.2.1 开启 binlog

开启 binlog 次要是批改 MySQL 的配置文件 mysqld.cnf，该文件在容器的 /etc/mysql/mysql.conf.d 目录下。

针对该配置文件，咱们做如下批改：

# 这个参数示意启用 binlog 性能，并指定 binlog 的存储目录
log-bin=javaboy_logbin

# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600

# 设置了 binlog 文件的有效期（单位：天）expire_logs_days = 7

# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）#binlog-do-db=javaboy_db

# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）#binlog-ignore-db=javaboy_no_db

# 写缓存多少次，刷一次磁盘，默认 0 示意这个操作由操作系统依据本身负载自行决定多久写一次磁盘
# 1 示意每一条事务提交都会立刻写磁盘，n 则示意 n 个事务提交才会写磁盘
sync_binlog=0

# 为以后服务取一个惟一的 id（MySQL5.7 之后须要配置）server-id=1

各项配置的含意松哥曾经在凝视中阐明了。截图如下：

配置实现后，执行如下命令重启 mysql 容器（mysql1 是我这里容器的名字）：

docker restart mysql1

重启之后，再次执行 show variables like 'log_bin%'; 即可看到 binlog 曾经开启了。

这里除了 log_bin 变量外，还有两个变量名也值得咱们关注：

  • log_bin_basename：这个是未来产生的 binlog 日志文件的名称前缀，换句话说，依据大家目前所看到的配置，未来产生的 binlog 日志文件名为 javaboy_logbin.xxx，这个文件中将会用来记录所有的 DDL 和 DML 语句事件。
  • log_bin_index：这个是 binlog 的索引文件，保留了所有 binlog 的目录，因为 binlog 可能会有多个。咱们能够来查看一下当初的 javaboy_logbin.index 文件：

能够看到，目前只有一个 logbin 文件。

2.2.2 批改 binlog_format

binlog_format 有几种不同的改法：

批改以后会话的 binlog_format，这个批改只针对以后会话无效：

也能够批改全局的 binlog_format，这个批改，当 MySQL 重启之后，会生效：

如果想一劳永逸搞定这事，能够批改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，在配置文件中，增加 binlog_format 选项，如下：

这是一个永久性的批改。

3. 常见 binlog 操作

接下来咱们再来介绍几个常见的 binlog 操作命令。

  1. 查看所有 binlog 日志

通过如下形式咱们能够查看 binlog 日志列表：

show master logs;

能够看到，我这里目前只有一个日志文件，文件名为 javaboy_logbin.000001，File_size 示意这个文件占用的字节大小是 154。

  1. 查看 master 状态

这个命令咱们在搭建 MySQL 主从的时候常常会用到，如下：

这个时候能够看到最新的 binlog 日志文件名称以及最初一个操作事件的 Position 值（这个值有啥用，咱们前面会给大家具体介绍）。

  1. 刷新 binlog

失常来说，一个 binlog 写满之后，会主动切换到下一个 binlog 开始写，不过咱们也能够执行一个 flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：

由上图能够看到，咱们刷新日志之后，再通过 show master logs 去查看日志，发现日志文件曾经多了一个新产生的了，而后再通过 show master status 去查看最新的日志文件信息，发现也曾经变为 javaboy_logbin.000002。

  1. 重置 binlog

reset master 能够重置 binlog 日志文件，让日志从新从 000001 开始记录，不过如果以后主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的谬误）。

  1. 查看 binlog

因为 binlog 是二进制日志文件，所以要是间接关上，那必定是看不了的：

没有看到任何有用的信息。

为了查看 binlog，MySQL 为咱们提供了两个官网工具，咱们一个一个来看，首先是 mysqlbinlog 命令，如下：

尽管看起来乱哄哄的，不过认真看着其实都有迹可循。因为我这里是一个新装置的数据库，里边只是创立了一个名为 javaboy 的库，而后创立了一个名为 user 的表加了两条数据，其余什么事件都没做，所以创立库的脚本咱们其实可能从纷杂的文件中找到。

产生的日志文件中有一个 end_log_pos 是日志文件的 pos 点，这个未来在数据恢复的时候有用。

不过这种查看形式不够人性化，咱们说 binlog 是依照事件来记录日志的，所以如果咱们可能依照事件的形式查看日志，就会好很多，咱们再来看看如下一个命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

这个示意以事件的形式来查看 binlog，这里波及到几个参数：

  • log_name：能够指定要查看的 binlog 日志文件名，如果不指定的话，示意查看最早的 binlog 文件。
  • pos：从哪个 pos 点开始查看，但凡 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于咱们能够指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的结尾开始查看。
  • offset：这是是偏移量，不指定默认就是 0。
  • row_count：查看多少行记录，不指定就是查看所有。

咱们来看一个简略的例子：

show binlog events in 'javaboy_logbin.000001';

这下就清晰多了，咱们能够看到之前的所有操作，例如：

  • 在 Pos 219-322 之间创立了一个库。
  • 在 Pos 387-537 之间创立了一张表。
  • 在 Pos 677-780 之间增加了一条记录。
  • …

这其实就是 Row 格局的 binlog。

5. 小结

好啦，明天这篇文章次要是和小伙伴们分享了 MySQL 的 binlog 日志，次要是一些理论知识，下篇文章松哥将通过两个具体的案例，来和大家演示不同的 binlog_format 存在的问题～

退出移动版