前言

binlog是默认开启的

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

binLog 与 undo log对比

  • redo log 它是 物理日志 ,记录内容是“在某个数据页上做了什么修改”,属于 InnoDB 存储引擎层产生的。
  • 而 binlog 是 逻辑日志 ,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”,属于MySQL Server 层。

BinLog

是什么

binlog即binary log,二进制日志文件,也叫作变更日志(update log)。它记录了数据库所有执行的 DDL 和 DML等数据库更新事件的语句,但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。
binlog主要应用场景:

一是用于 数据恢复
二是用于 数据复制

日志参数设置

1.永久

修改MySQL的my.cnf 或 my.ini文件可以设置二进制日志的相关参数:

[mysqld]
#启用二进制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

如果想改变日志文件的目录和名称,可以对my.cnf或my.ini中的log_bin参数修改如下:

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

注意: 新建的文件夹需要使用mysql用户,使用下面的命令即可。

chown -R -v mysql:mysql binlog

2.临时

如果不希望通过修改配置文件并重启的方式设置二进制日志的话,还可以使用如下指令,需要注意的是在mysql8中只有 会话级别 的设置,没有了global级别的设置。

# session级别
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.01 秒)

# global 级别 mysql8没有
mysql> set global sql_log_bin=0;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL

查看日志

当MySQL创建二进制日志文件时,先创建一个以“filename”为名称、以“.index”为后缀的文件,再创建一个以“filename”为名称、以“.000001”为后缀的文件。
MySQL服务 重新启动一次 ,以“.000001”为后缀的文件就会增加一个,并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同;如果日志长度超过了 max_binlog_size 的上限(默认是1GB),就会创建一个新的日志文件。
查看当前的二进制日志文件列表及大小。

mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156 | No |
+--------------------+-----------+-----------+
1 行于数据集 (0.02 秒)

以伪sql形式查看,同时显示binlog格式的语句,

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"

同时显示binlog格式的语句,使用下面的语句不显示

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"
# 可查看参数帮助
mysqlbinlog --no-defaults --help
# 查看最后100行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail
-100
# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A
20 '4939002'

根据pos点进行查询

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 :查询总条数(不指定就是所有行)

binlog 格式查看
除此之外,binlog还有2种格式,分别是Statement和Mixed

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 行于数据集 (0.02 秒)


使用日志恢复

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解:使用mysqlbinlog命令来读取filename中的内容,然后使用mysql命令将这些内容恢复到数据库中。
filename :是日志文件名。
option :可选项,比较重要的两对option参数是--start-date、--stop-date 和 --start-position、--stop-position
start-date 和 –stop-date :可以指定恢复数据库的起始时间点和结束时间点。
start-position和–stop-position :可以指定恢复数据的开始位置和结束位置。

注意:使用mysqlbinlog命令进行恢复操作时,必须是编号小的先恢复,例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

删除

MySQL的二进制文件可以配置自动删除,同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS 只删除指定部分的二进制日志文件, RESET MASTER 删除所有的二进制日志文件。具体如下:

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

binlog 写入机制

binlog的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache ,事务提交的时候,再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache。
pCvgp9A.png
备用链接
write和fsync的时机,可以由参数 sync_binlog 控制,默认是 0 。为0的时候,表示每次提交事务都只write,由系统自行判断什么时候执行fsync。虽然性能得到提升,但是机器宕机,page cache里面的binglog 会丢失。如下图:
pCvgn9s.png
备用链接
为了安全起见,可以设置为 1 ,表示每次提交事务都会执行fsync,就如同redo log 刷盘流程一样。最后还有一种折中方式,可以设置为N(N>1),表示每次提交事务都write,但累积N个事务后才fsync。
pCvgNC9.png
备用链接
在出现IO瓶颈的场景里,将sync_binlog设置成一个比较大的值,可以提升性能。同样的,如果机器宕机,会丢失最近N个事务的binlog日志。

binLog 与 undo log对比

  • redo log 它是 物理日志 ,记录内容是“在某个数据页上做了什么修改”,属于 InnoDB 存储引擎层产生的。
  • 而 binlog 是 逻辑日志 ,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”,属于MySQL Server 层。

两阶段提交 2PC

在执行更新语句过程,会记录redo log与binlog两块日志,以基本的事务为单位,redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,所以redo log与binlog的 写入时机 不一样。


redo log与binlog两份日志之间的逻辑不一致,会出现问题

pCv2F2R.png
备用链接
由于binlog没写完就异常,这时候binlog里面没有对应的修改记录
为了解决两份日志之间的逻辑一致问题,InnoDB存储引擎使用两阶段提交方案

使用两阶段提交后,写入binlog时发生异常也不会有影响

另一个场景,redo log设置commit阶段发生异常,那会不会回滚事务呢?

并不会回滚事务,它会执行上图框住的逻辑,虽然redo log是处于prepare阶段,但是能通过事务id找到对应的binlog日志,所以MySQL认为是完整的,就会提交事务恢复数据。
注意看右边的小图

参考文献

文章部分内容引用自宋红康老师的Mysql高级课程,在此表示感谢,如有侵权,请联系站长进行删除
<声明>:

谁都不是生下来就会的,都不是三体人,知识也可以遗传,我们的东西都是学习到的,所以每个人的知识体系难免有老师的身影,先学习后模仿,才能学会,至于笔记,摘抄部分我感觉难免,学会了就是自己的!

浙公网安备33011302000604

辽ICP备20003309号