往期回顾

  • 图文联合带你搞定MySQL日志之Undo log(回滚日志)
  • 图文联合带你搞懂InnoDB MVCC
  • 图文联合带你搞懂MySQL日志之Redo Log(重做日志)
  • 图文联合带你搞懂MySQL日志之Error Log（谬误日志）
  • 图文联合带你搞懂MySQL日志之Slow Query Log（慢查问日志）
  • 图文联合带你搞懂MySQL日志之relay log（中继日志）
  • 图文联合带你搞懂MySQL日志之General Query Log（通用查问日志）

此篇为图文联合搞懂MySQL日志的最初一篇，MySQL中共有八大日志，其中数据定义语句日志不是给用户查看的，在此篇尾部简略介绍，接下来图文联合系列还会持续推出新文章！

二进制日志(Binary log)

binlog能够说是MySQL中比拟重要的日志了，在日常开发及运维过程中，常常会遇到。

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，然而不蕴含没有批改任何数据的语句（如数据查问语句select、show等）。

它以事件模式记录并保留在二进制文件中。通过这些信息，咱们能够再现数据更新操作的全过程。

如果想要记录所有语句（例如，为了辨认有问题的查问)，须要应用通用查问日志。

Binary log次要利用场景：

  • 一是用于数据恢复，如果MySQL数据库意外进行，能够通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些批改，而后依据二进制日志文件中的记录来复原数据库服务器。
  • 二是用于数据复制，因为日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据统一的目标。

能够说MySQL数据库的数据备份、主备、单主、多主、MGR都离不开Binary log,须要依附Binary log来同步数据，保证数据一致性。

查看默认状况

查看记录二进制日志是否开启：在MySQL8中默认状况下，二进制文件是开启的。

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 | ON                          |//  函数创立 | log_bin_use_v1_row_events       | OFF                         || sql_log_bin                     | ON                          |//变更sql记录下来+---------------------------------+-----------------------------+6 rows in set (0.01 sec)
  • log_bin_basename:是binlog日志的根本文件名，前面会追加标识来示意每一个文件
  • log_bin_index:是binlog文件的素引文件，这个文件治理了所有的binlog文件的目录
  • log_bin_trust_function_creators:限度存储过程，后面咱们曾经讲过了，这是因为二进制日志的一个重要性能是用于主从复制，而存储函数有可能导致主从的数据不统一。所以当开启二进制日志后，须要限度存储函数的创立、批改、调用
  • log_bin_use_v1_row_events此只读零碎变量已弃用。ON示意应用版本1二进制日志行，OFF示意应用版本2二进制日志行（MySQL5.6的默认值为2)。

日志参数设置

形式 1 ：永久性形式

批改MySQL的my.cnf或my.ini文件能够设置二进制日志的相干参数：

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

提醒:

  1. log-bin=mysql-bin

关上日志(主机须要关上)，这个mysql-bin也能够自定义，这里也能够加上门路，如:/home/www/mysql_bin_log/mysql-bin

  1. binlog_expire_logs_seconds

此参数管制二进制日志文件保留的时长单位是秒，默认2592000 30天 --14400 4小时;86400 1天; 259200 3天;

  1. max_binlog_size

管制单个二进制日志大小，以后日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比拟凑近最大值而又遇到一个比拟大事务时，为了保障事务的完整性，可能不做切换日志的动作只能将该事务的所有SQL都记录进以后日志，直到事务完结。个别状况下可采取默认值。

设置带文件夹的bin-log日志寄存目录

如果想扭转日志文件的目录和名称，能够对my.cnf或my.ini中的log_bin参数批改如下：

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

留神：新建的文件夹须要应用mysql用户，应用上面的命令即可。

chown -R -v mysql:mysql binlog

提醒 数据库文件最好不要与日志文件放在同一个磁盘上!这样，当数据库文件所在的磁盘产生故障时，能够应用日志文件复原数据。

形式 2 ：临时性形式

如果不心愿通过批改配置文件并重启的形式设置二进制日志的话，还能够应用如下指令，须要留神的是在mysql 8 中只有会话级别的设置，没有了global级别的设置。

# global 级别mysql> set global sql_log_bin= 0 ;ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be usedwith SET GLOBAL# session级别mysql> SET sql_log_bin = 0 ;Query OK, 0 rows affected (0.01 秒)

查看日志

当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 rows in set (0.00 sec)

所有对数据库的批改都会记录在binglog中。但binlog是二进制文件，无奈间接查看，借助mysqlbinlog命令工具了。指令如下:在查看执行，先执行一条sQL语句，如下

update student set name='张三_back' where id=1;[root@localhost ~]$ cd /var/lib/mysql[root@localhost ~]$ mysqlbinlog  "/var/lib/mysql/lqhdb-binlog.000001"

执行后果能够看到，这是一个简略的日志文件，日志中记录了用户的一些操作，这里并没有呈现具体的SQL语句，这是因为binlog关键字前面的内容是通过编码后的二进制日志。

这里一个update语句蕴含如下事件

  • Query事件负责开始一个事务(BEGIN)
  • Table_map事件负责映射须要的表
  • Update_rows事件负责写入数据
  • Xid事件负责完结事务

上面命令将行事件以伪SQL的模式体现进去

mysqlbinlog -v "/var/lib/mysql/binlog/test.000002"

后面的命令同时显示binlog格局的语句，应用如下命令不显示它

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test.000002"

对于mysqlbinlog工具的应用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简略分享几个罕用的语句，更多操作能够参考官网文档。

# 可查看参数帮忙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'

下面这种方法读取出binlog日志的全文内容比拟多，不容易分辨查看到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日志文件，分成无效事件行的形式返回，并可应用limit指定pos点的起始偏移，查问条数。其它举例:

#a、查问第一个最早的binlog日志:show binlog events\G ;#b、指定查问mysql-bin.088802这个文件show binlog events in 'atguigu-bin. 008002'\G;#c、指定查问mysql-bin. 080802这个文件，从pos点:391开始查起:show binlog events in 'atguigu-bin.008802' from 391\G;#d、指定查问mysql-bin.000802这个文件，从pos点:391开始查起，查问5条（即5条语句)show binlog events in 'atguigu-bin.000882' from 391 limit 5\G#e、指定查问 mysql-bin.880002这个文件，从pos点:391开始查起，偏移2行〈即两头跳过2个）查问5条（即5条语句)。show binlog events in 'atguigu-bin.088882' from 391 limit 2,5\G;

binlog格局查看

mysql> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   | //行格局+---------------+-------+1 rows in set (0.00 sec)

除此之外，binlog还有 2 种格局，别离是Statemen和Mixed

  • Statement 每一条会批改数据的sql都会记录在binlog中。 长处：不须要记录每一行的变动，缩小了binlog日志量，节约了IO，进步性能。
  • Row 5.1.5版本的MySQL才开始反对row level 的复制，它不记录sql语句上下文相干信息，仅保留哪条记录被批改。 长处：row level 的日志内容会十分分明的记录下每一行数据批改的细节。而且不会呈现某些特定状况下的存储过程，或function，以及trigger的调用和触发无奈被正确复制的问题。
  • Mixed 从5.1.8版本开始，MySQL提供了Mixed格局，实际上就是Statement与Row的联合。

应用日志复原数据

mysqlbinlog复原数据的语法如下：

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之前复原。

flush logs; #能够生成新的binLog 文件，不然这个文件边复原边变大是不行的。show binary logs; # 显示有哪些binLog 文件

复原数据

mysqlbinlog [option] filename|mysql –uuser -ppass;mysqlbinlog --no-defaults  --start-position=236  --stop-position=1071 --database=my_db1 /var/lib/mysql/lqhdb-bin.000002 | /usr/bin/mysql -root -p123456 -v my_db1

删除二进制日志

MySQL的二进制文件能够配置主动删除，同时MySQL也提供了平安的手动删除二进制文件的办法。PURGE MASTER LOGS只删除指定局部的二进制日志文件，RESET MASTER删除所有的二进制日志文件。具体如下：

1.PURGE MASTER LOGS：删除指定日志文件

PURGE MASTER LOGS语法如下：

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

举例 :应用PURGE MASTER LOGS语句删除创立工夫比binlog.000005早的所有日志

(1)屡次重新启动MysSQL服务，便于生成多个日志文件。而后用SHOW语句显示二进制日志文件列表

SHOW BINARY LOGS;

(2）执行PURGE MASTER LOGS语句删除创立工夫比binlog.000005早的所有日志

PURGE MASTER LOGS T0 "binlog. 000005";

(3）显示二进制日志文件列表

SHGW BINARY LOGS;

举例:应用PURGE MASTER LOGS语句删除2023年3月17日前创立的所有日志文件。具体步骤如下:

(1) 显示二进制日志文件列表

SHOW BINARY LOGS;

(2）执行mysqlbinlog命令查看二进制日志文件binlog.000005的内容

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"

(3）应用PURGE MASTER LOGS语句删除2023年3月17日前创立的所有日志文件

PURGE MASTER LOGS before "20220317";

(4）显示二进制日志文件列表

SHOW BINARY LOGS;

2022年01月05号之前的二进制日志文件都曾经被删除，最初一个没有删除，是因为以后在用，还未记录最初的工夫，所以未被删除。

2.RESET MASTER:删除所有二进制日志文件

reset master;

其它场景

二进制日志能够通过数据库的全量备份和二进制日志中保留的增量信息，实现数据库的无损失复原。然而，如果遇到数据量大、数据库和数据表很多（比方分库分表的利用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止地位不容易治理。

在这种状况下，一个无效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就能够无效防止数据库故障导致的数据异样等问题。

深刻了解二进制日志

写入机制

binlog的写入机会也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以零碎会给每个线程调配一个块内存作为binlog cache。

咱们能够通过binlog_cache_size参数管制单个线程binlog cache大，如果存储内容超过了这个参数，就要暂存到磁盘(Swap)。binlog日志刷盘流程如下:

上图的write，是指把日志写入到文件系统的page cache，并没有把数据长久化到磁盘，所以速度比拟快。

上图的fsync，才是将数据长久化到磁盘的操作

write和fsync的机会，能够由参数sync_binlog管制，默认是 0 。

为 0 的时候，示意每次提交事务都只write，由零碎自行判断什么时候执行fsync。尽管性能失去晋升，然而机器宕机，page cache外面的binglog 会失落。如下图：

为了平安起见，能够设置为 1 ，示意每次提交事务都会执行fsync，就如同 redo log 刷盘流程 一样。最初还有一种折中形式，能够设置为N(N>1)，示意每次提交事务都write，但累积N个事务后才fsync。

在呈现IO瓶颈的场景里，将sync_binlog设置成一个比拟大的值，能够晋升性能。同样的，如果机器宕机，会失落最近N个事务的binlog日志。

binlog与redolog比照

  • redo log 它是物理日志，记录内容是“在某个数据页上做了什么批改”，属于 InnoDB 存储引擎层产生的。
  • 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，相似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层
  • 尽管它们都属于长久化的保障，然而则重点不同。
    • redo log让InnoDB存储引擎领有了解体恢复能力。
    • binlog保障了MySQL集群架构的数据一致性。

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。

相干链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交换群：

微信：扫码增加GreatSQL社区助手微信好友，发送验证信息加群。