往期回顾
- 图文联合带你搞定 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-bin
binlog_expire_logs_seconds= 600
max_binlog_size=100M提醒:
- log-bin=mysql-bin
关上日志(主机须要关上)，这个 mysql-bin 也能够自定义，这里也能够加上门路，如:/home/www/mysql_bin_log/mysql-bin
- binlog_expire_logs_seconds
此参数管制二进制日志文件保留的时长单位是秒，默认 2592000 30 天 –14400 4 小时;86400 1 天; 259200 3 天;
- 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 used
with 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 社区助手微信好友，发送验证信息加群。