1、简介

  • binlog即binary log，二进制日志文件。它记录了数据库所有执行的DDL和DML语句(除了数据查询语句select、show等)，以事件形式记录并保存在二进制文件中。
  • binlog主要有两个应用场景：
  • 一是用于复制，master把它的二进制日志传递给slave来达到master-slave数据一致的目的。
  • 二是用于数据恢复，例如还原备份后，可以重新执行备份后新产生的binlog，使得数据库保持最新状态
  • 除去这两个主要用途外，binlog可以用于异构系统之间数据的交互，binlog完整保存了一条记录的前项和后项记录，可以用DTS服务，将MySQL数据以准实时的方式抽取到底层数据平台，比如Base、Hive、Spark等，打通OLTP和OLAP
  • binlog日志可以选择三种模式，分别是STATEMENT、ROW、MIXED，下面简单介绍下这三种模式：
  • statement：基于SQL语句的复制，每一条会修改数据的SQL语句会记录到binlog中。该模式下产生的binlog日志量会比较少，但可能导致主从数据不一致。
  • row：基于行的复制，不记录每一条具体执行的SQL语句，仅需记录哪条数据被修改了，以及修改前后的样子。该模式下产生的binlog日志量会比较大，但优点是会非常清楚的记录下每一行数据修改的细节，主从复制不会出错。
  • mixed：混合模式复制，以上两种模式的混合使用，一般的复制使用statement模式保存binlog，对于statement模式无法复制的操作使用row模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式
  • binlog模式在MySQL 5.7.7之前，默认为statement，在之后的版本中，默认为row模式。因为row模式更安全，可以清楚记录每行数据修改的细节。

2、binlog相关参数

  • binlog默认情况下是不开启的，不过一般情况下，初始化的时候建议在配置文件中增加log-bin参数来开启binlog
# 配置文件中增加log-bin配置
[mysqld]
log-bin = binlog

# 不指定路径默认在data目录下，也可以指定路径
[mysqld]
log-bin = /data/mysql/logs/binlog

# 查看数据库是否开启了binlog
show variables like 'log_bin%';
  • 开启binlog后，还需注意一些与binlog相关的参数，下面简单介绍下相关参数：
binlog_format  # 设置binlog模式，建议设为row
binlog_do_db # 此参数表示只记录指定数据库的二进制文件，默认全部记录，一般情况下不建议更改。如果需要指定多个数据库，再加多条指定
binlog_ignore_db # 此参数表示不记录指定的数据库的二进制日志，同上，一般不显示指定。
max_binlog_cache_size # 此参数表示binlog使用的内存最大的尺寸
binlog_cache_size # 此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试
binlog_cache_use # 使用二进制日志缓存的事务数量
binlog_cache_disk_use # 使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
expire_logs_days # 此参数控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。可根据实际情况设置，比如保留15天或30天。MySQL8.0版本可用binlog_expire_logs_seconds参数代替。
max_binlog_size # 控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束，一般情况下可采取默认值。
log_bin_trust_function_creators # 当二进制日志启用后，此参数就会启用。它控制是否可以信任存储函数创建者，不能创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值）,用户不得创建或修改存储函数，除非它们具有除create routine 或alter routine特权之外的super权限。建议设置为1.
sync_binlog # 控制MySQL服务端将二进制日志同步到磁盘的频率，默认值为1。设置为0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新；设置为1，表示每次事务提交，MySQL都会把binlog刷下去，这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响；设置为n，其中n为0或1意外的值，在进行N次事务提交以后，MySQL将执行一次fsync之类的磁盘同步指令，将binlog文件缓存刷新到磁盘。推荐设置为1，处于性能考虑也可酌情调整

3、binlof操作与管理相关的SQL常用的语句：

  • 查看当前最新的一个binlog日志的编号名称，及最后一个事件结束的位置
show master status;
  • 查看目前保留的所有binlog日志列表及大小
show binary logs;
  • 刷新binlog，此刻开始产生一个新编号的binlog日志文件
flush logs;
# 或
flush binary logs
  • 手动清理指定时间之前的binlog日志
purge binary logs before '2020-07-01 12:00:00';
  • 将指定日志文件之前的日志清除
purge binary logs to 'binlog-000012';
  • 清理3天前的binlog日志
purge master logs before date_sub(now(), interval 3 DAY)
# BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。这里的清理是指从此刻-3243600s前的文件，是此文件最后更新的时间。
# 时间和文件名一定不可以写错，尤其是时间中的年和文件名中的序号，以防不小心将正在使用的binlog删除！！！
# 切勿删除正在使用的binlog！！！
  • 清空所有的binary日志，慎用
reset master;
  • 查看指定的binlog日志event
show binlog events in 'binlog-000030';
  • 从指定的位置开始，查看指定的binlog日志
show binlog events in 'binlog-000030' from 931;
  • 从指定的位置开始，查看指定的binlog日志，限制查询的event数
show binlog events in 'binlog-000030' from 931 limit 2;


本文来自博客园，作者：郭祺迦，转载请注明原文链接：