MySQL-日志&二进制日志binlog初探

小小工匠

发布于 2021-08-17 11:10:57

7970

文章被收录于专栏：小工匠聊架构小工匠聊架构

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

二进制日志： http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/binary-log.com.coder114.cn.html

MySQL日志分类

MySQL服务层日志

存储引擎层日志

以Innodb存储引擎来讲，主要由 Redo log 和 Undo log , 为了支持事务。

binlog

这里我们重点来了解下 binlog的主要功能

binlog都记录了哪些内容

binlog中主要记录了所有对MySQL数据库的修改事件，包括增删改事件以及对表结构的修改事件，不包括 select 和 show 之类的操作（这部分会记到查询日志中） 。需要注意的一点：只有成功执行的才回被记录到binlog中，那些执行出错或者已经回滚的数据，是不会被记录到binlog中的。

binlog 的主要目的是主从复制和数据恢复

在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复：通过使用 mysqlbinlog工具来使恢复数据

什么时候写binlog

InnoDB （支持事务的存储引擎），必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘取决于参数 sync_binlog

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

建议设置为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

binlog 文件以及扩展

binlog日志包括两类文件:

二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

何时会生成新的binlog

MySQL服务器停止或重启时
使用 flush logs 命令；
当 binlog 文件大小超过 max_binlog_size 变量的值

以上三种情况，MySQL会重新生成一个新的日志文件，文件序号递增。

注： max_binlog_size 的最小值是4096字节，最大值和默认值是 1GB (1073741824字节)。

事务被写入到binlog的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，所以有的时候我们会看到binlog文件大于 max_binlog_size 的情况。

binlog的格式

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

STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
ROW：基于行的复制（row-based replication, RBR）
MIXED：混合模式复制（mixed-based replication, MBR)

Version < MySQL 5.7.7 ，默认的格式是 STATEMENT，

Version >= MySQL 5.7.7 + ，默认值是 ROW。

日志格式通过 binlog-format 指定 —> binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

基于段的格式-STATEMENT

基于段的格式 binlog_format=STATEMENT

STATEMENT 的优缺点

优点：日志记录量相对较小，节省磁盘及网络I/O ,只对一条删记录进行修改或者插入，row格式所产生的日质量小于段产生的日志量
缺点：必须要记录上下文的信息，以确保在从服务器上能够正确执行。但是有些特定函数比如UUID(),user() ,now()这样非确定性函数还是无法复制，有可能造成主备服务器数据不一致。

使用mysqlbinlog查看binlog

[root@artisan ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.7.29-log MySQL Community Server (GPL)
.....
.....
mysql> show variables like 'binlog_format'; # 查看当前binlog的格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set session binlog_formate=statement; # 修改session级别的binlog格式为statement
mysql> set session binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';  #确认下当前binlog的格式
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
....
....
| mysql-bin.000049 |      177 |
+------------------+-----------+
45 rows in set (0.00 sec)

mysql> flush logs; #  刷新log ,会产生一个新的binlog
Query OK, 0 rows affected (0.05 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
....
....
| mysql-bin.000049 |      177 |
| mysql-bin.000050 |      154 |
+------------------+-----------+
46 rows in set (0.00 sec)

mysql> 下面随便搞点操作，方便观察binlog



mysql> create database artisan2;
Query OK, 1 row affected (0.00 sec)

mysql> use artisan2;
Database changed
mysql> create table t(id int , c1 varchar(10));
Query OK, 0 rows affected (0.30 sec)

mysql> insert into t values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

切到binlog的目录下（/etc/my.cnf中配置的log-bin项）

[root@artisan binlog]# pwd
/var/lib/mysql/data/binlog
[root@artisan binlog]# mysqlbinlog  mysql-bin.000050 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200131 16:15:14 server id 1  end_log_pos 123 CRC32 0xf1d6a9f8 	Start: binlog v 4, server v 5.7.29-log created 200131 16:15:14
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
EuIzXg8BAAAAdwAAAHsAAAABAAQANS43LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Afip1vE=
'/*!*/;
# at 123
#200131 16:15:14 server id 1  end_log_pos 154 CRC32 0x19c20618 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@artisan binlog]#

5.7.29版本的这个statement格式的binlog ，居然没法直接看内容了，先记录下。

5.7.9中还能看到具体的SQL

基于行的格式-Row

基于行的日志格式 binlog_format=ROW

举个例子，假设有一个SQL修改了1万条数据，基于段Statement的日志格式仅仅会记录这个SQL，而基于Row的日志会有1万条记录分别记录每一行的数据修改。

优缺点

优点：记录每一条数据的变更，因此使得MySQL主从复制更加安全。对每一行数据的修改比基于段的复制高效。还可以用来数据恢复（比对数据的变更）
缺点：因为要记录每一条的变更，因此记录日志量较大

日志内容的控制参数 binlog_row_image

binlog_row_image = FULL | MINIMAL | NOBLOB  （3个选项，默认FULL）

举个例子一个表中有 20 列(20个字段) ，3个参数的区别如下

FULL 全部字段都记录
MINIMAL 仅记录变更的字段数据
NOBLOB ：和full类似，只是不记录BLOB类型的字段，其他全记录

mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set session binlog_row_image=minimal;  # 修改 
Query OK, 0 rows affected (0.00 sec)

增加c2字段，text类型

full

默认 FULL的情况

mysqlbinlog -vv 命令查看ROW格式的日志

minimal

修改为 minimal

mysqlbinlog -vv 命令查看ROW格式的日志

可以看到

仅记录了变化的字段的内容。

noblob

设置参数为 noblob

将非 blob字段更新下，看看是否记录blob字段的信息

mysqlbinlog -vv 命令查看ROW格式的日志

可以看到

第三列，text类型的，并没有在binLog中。

查看Row格式的binglog

Row格式的增加 -vv 查看

[root@artisan binlog]# mysqlbinlog -vv  mysql-bin.000050

我这个5.7.29的版本看不到。。。有可能是打开方式不对，待确认。

5.7.9版本可以

混合日志格式-mixed

binlog_format=MIXED

特点

根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL决定

不展开了，并不是一种新的格式

常用binlog操作

## binlog相关的命令 

```sql
# 查看是否开启binlog
mysql>show binary logs;

#查看binlog格式：
mysql>show variables like 'binlog_format';

#获取binlog文件列表：
mysql>show binary logs;

#查看当前正在写入的binlog文件：
mysql>show master status;

#查看master上的binlog：
mysql>show master logs;

#只查看第一个binlog文件的内容：
mysql>show binlog events;

#查看指定binlog文件的内容：
mysql>show binlog events in 'mysql-bin.000045';


#清空所有的bin-log：
mysql>reset master;

#生成一个新的binlog：
mysql>flush logs;