分享好友 站长动态首页 网站导航

MySQL 的 binlog 的三种格式这么好玩!

2022-03-31 09:45 · 头闻号数据库

MySQL 中的日志比较重要的有 binlog(归档日志)、redo log(重做日志)以及 undo log,那么跟我们本文相关的主要是 binlog,另外两个日志松哥将来有空了再和大家详细介绍。

1. binlog

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

binlog 是 MySQL Server 层的日志,而不是存储引擎自带的日志,它记录了所有的 DDL 和 DML(不包含数据查询语句)语句,而且是以事件形式记录,还包含语句所执行的消耗的时间等,需要注意的是:

根据 MySQL 官方文档的介绍,开启 binlog 之后,大概会有 1% 的性能损耗,不过这还是可以接受的,一般来说,binlog 有两个重要的使用场景:

2. 配置 binlog

为了演示方便,松哥这里在 Docker 中安装了 MySQL,我们以此为例来开始今天的演示。如果小伙伴们还不懂 docker 的使用,可以在公众号后台回复 docker,有松哥写的教程。

首先我们在 docker 中安装好 MySQL,然后进入到容器中,通过如下命令可以查看 binlog 是否开启:

这个 OFF 就表示 binlog 是一个关闭状态,没有开启。

通过以下命令可以查看 binlog 日志的格式,如下:

可以看到,这个 binlog 的格式为 ROW。

这里就涉及到一个问题,binlog 的格式。

2.1 binlog 的格式

binlog 有三种格式:

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 变量外,还有两个变量名也值得我们关注:

可以看到,目前只有一个 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 操作命令。

通过如下方式我们可以查看 binlog 日志列表:

show master logs;

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

这个命令我们在搭建 MySQL 主从的时候经常会用到,如下:

这个时候可以看到最新的 binlog 日志文件名称以及最后一个操作事件的 Position 值(这个值有啥用,我们后面会给大家详细介绍)。

正常来说,一个 binlog 写满之后,会自动切换到下一个 binlog 开始写,不过我们也可以执行一个 flush logs 命令来手动刷新 binlog,手动刷新 binlog 之后,就会产生一个新的 binlog 日志文件,接下来所有的 binlog 日志都将记录到新的文件中。如下:

由上图可以看到,我们刷新日志之后,再通过 show master logs 去查看日志,发现日志文件已经多了一个新产生的了,然后再通过 show master status 去查看最新的日志文件信息,发现也已经变为 javaboy_logbin.000002。

reset master 可以重置 binlog 日志文件,让日志重新从 000001 开始记录,不过如果当前主机有一个或者多个从机在运行,那么该命令就运行不了(因为从机是通过 binlog 来实现数据库同步的,主机把 binlog 清空了,从机会报找不到 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,这里涉及到几个参数:

我们来看一个简单的例子:

show binlog events in 'javaboy_logbin.000001';

这下就清晰多了,我们可以看到之前的所有操作,例如:

这其实就是 Row 格式的 binlog。

4. 小结

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

本文转载自微信公众号「江南一点雨 」,可以通过以下二维码关注。转载本文请联系江南一点雨公众号。

免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报

举报
反对 0
打赏 0
更多相关文章

评论

0

收藏

点赞