【MySQL 数据宝典】【磁盘结构】- 006 bin log 二进制日志的基本模式和参数设置

一、基本概念

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。

1.1 特点

• binlog在MySQL的Server层实现(引擎共用)
• binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
  • binlog不限制大小,追加写入,不会覆盖以前的日志.
  • 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

开启Binlog日志有以下两个最重要的使用场景:

• 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

二、日志的三种模式

2.1 ROW（row-based replication, RBR）

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

• 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
• 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

2.2 STATMENT（statement-based replication, SBR）

记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

• 优点：日志量小，减少磁盘IO，提升存储和恢复速度
• 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

2.3 MIXED（mixed-based replication, MBR）

以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
企业场景如何选择binlog的模式

1. 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。
2. 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
3. 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快.

• 跳过语句模式：默认情况下，MySQL复制使用的是语句模式（Statement mode），即主服务器上执行的SQL语句会被发送到从服务器执行。然而，对于某些存储过程，MySQL可能无法正确复制执行过程中的SQL语句，因为存储过程可能包含了诸如非确定性函数、临时表等语句，这些语句在从服务器上可能无法正确执行，从而导致复制的不一致性。
• 函数依赖：存储过程中可能会调用其他存储过程或自定义函数，而这些函数的结果可能会因为主服务器和从服务器上的数据不一致而产生不同的结果，导致数据不一致。
• 依赖外部数据：存储过程可能会依赖外部的数据或环境变量，而这些数据在主服务器和从服务器上可能不同，导致存储过程的执行结果不一致。
• 并发执行问题：如果多个客户端同时执行存储过程，可能会出现并发执行的问题，从而导致数据不一致。

三、 Binlog写入机制

3.1 binlog文件结构

• MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。
  • 比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

3.2 binlog落盘策略

binlog 的写入顺序: binlog cache (write) -> OS cache  -> (fsync) disk.

• write表示: 写入文件系统缓存,fsync表示持久化到磁盘的时机
• binlog刷数据到磁盘由参数sync_binlog进行配置
  • sync_binlog=0 的时候，表示每次提交事务都只 write,不 fsync;
  • sync_binlog=1 的时候，表示每次提交事务都会执行 fsync;
  • sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

注意: 不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。如果设置成0，主动重启丢失的数据不可控制。设置成1，效率低下，设置成N(N>1)，则主机重启，造成最多N个事务的binlog日志丢失，但是性能高，丢失数据量可控。

3.3 binlog 写入流程

1. 根据记录模式和操作触发event事件生成log event.
2. 事务执行过程中，先把日志(log event) 写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
3. binlog cache，系统为每个线程分配了一片binlog cache内存 (每个线程都有自己的binlog cache，共用一份binlog文件) .
4. 事务提交的时候，执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache.

3.4 redo log 和 binlog的区别

CrashSafe指MySQL服务器宕机重启后，能够保证：

• 所有已经提交的事务的数据仍然存在。
• 所有没有提交的事务的数据自动回滚。

四、Binlog命令操作

4.1 使用sql命令查看binlog文件

启用Binlog

vim /etc/my.cnf

--增加下面几个参数
binlog-format=ROW
log-bin=mysqlbinlog
server-id=1

-- 重启mysql
systemctl restart mysqld
1
2
3
4
5
6
7
8
9

• binlog-format: 文件模式
• log-bin: mysql会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index文件为你指定的文件名后跟.index
• server-id=1:  随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了

启动成功之后，我们可以登陆查看我们的配置是否起作用

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /var/lib/mysql/mysqlbinlog       |
| log_bin_index                   | /var/lib/mysql/mysqlbinlog.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
1
2
3
4
5
6
7
8
9
10
11

• log_bin: 是否开启binlog日志
• log_bin_basename: 基础文件名
• log_bin_index : binlog文件的索引文件，管理所有binlog文件
• sql_log_bin: 表示当前会话是否记录 bin log，默认值on(当sql_log_bin关闭后，主库服务器上的改动不记录bin log，不会复制到从库)。

查看binlog文件列表

  mysql> show binary logs;
+--------------------+-----------+
  | Log_name           | File_size |
  +--------------------+-----------+
  | mysqlbinlog.000001 |       177 |
  | mysqlbinlog.000002 |       177 |
  | mysqlbinlog.000003 |       154 |
  +--------------------+-----------+
1
2
3
4
5
6
7
8

查看正在写入的binlog文件

  show master status;
1

查看binlog文件信息

使用show binlog events命令查询到的每一行数据就是一个binlog管理事件

mysql> show binlog events;
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name           | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysqlbinlog.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysqlbinlog.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysqlbinlog.000001 | 154 | Stop           |         1 |         177 |                                       |
+--------------------+-----+----------------+-----------+-------------+-----------
1
2
3
4
5
6
7
8

参数说明

• Log_name：当前事件所在的binlog文件名称；
• Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。

第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件。这种方式很常见，很多格式文件，如pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。

• Event_type：表示事件的类型；
• Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置；
• End_log_position：下一个事件的开始位置；
• Info：当前事件的描述信息。

查看指定binlog文件内容

mysql> show binlog events in 'mysqlbinlog.000001'\G;
*************************** 1. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.30-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 154
 Event_type: Stop
  Server_id: 1
End_log_pos: 177
     Info: 
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

使用mysqlbinlog命令查看binlog文件

• mysql给我们提供了一个用于查看binlog日志的工具，叫做mysqlbinlog

[root@localhost mysql]# mysqlbinlog mysqlbinlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
  # at 4
  #220125  6:40:35 server id 1  end_log_pos 123 CRC32 0x1e570724  Start: binlog v 4, server v 5.7.30-log created 220125  6:40:35 at startup
  ROLLBACK/*!*/;
BINLOG '
  4wvwYQ8BAAAAdwAAAHsAAAAAAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADjC/BhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  ASQHVx4=
  '/*!*/;
  # at 123
  #220125  6:40:35 server id 1  end_log_pos 154 CRC32 0x43fa19f1  Previous-GTIDs
  # [empty]
  # at 154
  #220125  6:41:17 server id 1  end_log_pos 177 CRC32 0x205de899  Stop
  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*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

输出指定position位置的binlog日志

 mysqlbinlog --start-position='154' --stop-position='177'
1

输出指定position位置的binlog日志到指定文件中

mysqlbinlog --start-position='154' --stop-position='177' mysqlbinlog.000001 > test.log
1

输出指定position位置的binlog日志到压缩文件中

mysqlbinlog --start-position="指定开始位置" --stop-position="指定结束位置" binlog文件|gzip > 压缩文件名
1

输出指定开始时间的binlog日志

mysqlbinlog --start-datetime="yyyy-MM-dd HH:mm:ss" binlog文件
1