MySQL最重要的日志-binlog详解

点击关注公众号，Java干货及时送达

什么是binlog

mysql中有各种类型的日志，记录了mysql包括启动、运行、连接、更改等各种操作，而binlog就是其中最重要的一种日志，它记录了MySQL所有数据的变更，并以二进制的形式存储在磁盘上

binlg记录了所有的表结构变更（CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…），以事件形式记录，还包含语句所执行的消耗的时间

官网对事件的解释为：二进制日志中存储的内容称之为事件，每一个数据库更新操作(Insert、Update、Delete，不包括Select)等都对应一个事件

binlog 的主要目的是复制和恢复。通过binlog就可以将一个MySQL数据库服务器（master) 的数据复制到一个或多个其他MySQL数据库服务器（slave)，以实现灾难恢复、水平扩展、统计分析、远程数据分发等功能。

通过binlog就可以实现mysql和其他组件的数据对接

开启binlog

首先查看mysql是否开启binlog同步功能

show variables like 'log_bin';
复制

‍

默认是关闭的，此时需要开启，就要编辑mysql的配置文件，正常是在etc目录下

如果没有就先用 which mysqld查看位置

vi /etc/my.cnf
复制

‍

修改my.cnf配置

[mysqld]
# 开启binlog
log-bin = mysql-bin
复制

也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog，通过 SET SQL_LOG_BIN=0 命令停用 binlog。

不过启用 binlog 之后须重启MySQL才能生效

常用的binlog命令

# 是否启用binlog日志
show variables like 'log_bin';


# 查看详细的binlog日志配置信息
show global variables like '%log%';


# 查看binlog的目录
show global variables like "%log_bin%";


# 查看binlog文件日志列表
show binary logs;


# 查看最新一个binlog日志文件名称和Position（操作事件pos结束点）
show master status;


# 刷新log日志，自此刻开始产生一个新编号的binlog日志文件
# 每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
flush logs;


# 查看第一个binlog文件内容
show binlog events  


# 查看具体一个binlog文件的内容
show binlog events in 'master.000001';


# 重置(清空)所有binlog日志
reset master;


# 删除slave的中继日志
reset slave;


# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2022-02-22 00:00:00';


# 删除指定日志文件
purge master logs to 'master.000001';


复制

使用binlog

1.使用mysqlbinlog自带查看命令法：
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看，binlog日志在同目录的data下

比如我刚刚建了一个表

CREATE TABLE IF NOT EXISTS `fungmu_table`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `age` VARCHAR(40) NOT NULL,
   `create_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制

然后使用命令

mysqlbinlog /usr/local/mysql/data/mysql-bin.000001
复制

‍

...............................................................................
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 423
#220224 22:23:40 server id 1  end_log_pos 738 CRC32 0x90972f69  Query thread_id=7 exec_time=0 error_code=0 Xid = 13
use `funmu`/*!*/;
SET TIMESTAMP=1645712620/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE IF NOT EXISTS `fungmu_table`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `age` VARCHAR(40) NOT NULL,
   `create_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
...............................................................................
server id 1     数据库主机的服务号；
end_log_pos 738 pos点
复制

2.mysql命令行

直接查看binlog日志的话全文内容较多，不容易分辨查看pos点信息，这时候可以使用mysql命令行

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

Log_name: mysql-bin.000001  
# pos起始点:
Pos: 11197 
# 事件类型：Query
Event_type: Query 
# 标识是由哪台服务器执行的
Server_id: 1
# pos结束点:11308(即：下行的pos起始点)
End_log_pos: 11308
# 执行的sql语句
Info: use `fangmu`; INSERT INTO `fungmu_table` VALUES (1,'fangmu','18','2022-02-22')    
复制

Binlog 的日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

STATEMENT：基于SQL语句的复制（statement-based replication, SBR）

ROW：基于行的复制（row-based replication, RBR）

MIXED：混合模式复制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement

每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

Row

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

Binlog结构和内容

日志由一组二进制日志文件（Binlog）,加上一个索引文件（index）；Binlog是一个二进制文件集合，每个Binlog以一个4字节的魔数开头，接着是一组Events；

1.魔数：0xfe62696e对应的是0xfebin；

2.Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；

3.第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；

4.其余的Event按照第一个Event的格式版本写入；

5.最后一个Event用于说明下一个binlog文件；

6.Binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表

Binlog常用场景

1、mysql主从复制

2、mysql数据恢复

3、数据同步，比如基于Canal投递MySQL Binlog到kafka、elasticsearch

END

原文链接：https://mp.weixin.qq.com/s/hI442pngafiSduqYQAZRiA