二进制日志

没有评论

二进制日志

二进制日志中包含了数据库更改和可能更改的事件，以及每个语句的执行时长。

类似 show、select 这类不更改数据的操作，是不记录在二进制日志中的。

二进制中的密码会重写。

二进制日志在以下情况下会创建新文件

服务启动或重启
服务端刷新日志
当前日志文件达到 max_binlog_size 大小

二进制日志仅记录或读取完整事务。当写入大事务的时候，日志可能会大于 max_binlog_size，因为同一个事务被写入同一个文件，不会拆分到多个文件。

8.0.14 版本开始，可以对二进制日志文件和中继日志文件进行加密。

InnoDB 对 XA 事务中的两阶段提交的支持确保了二进制日志和 InnoDB 数据文件是同步的。

用于主从复制
- 主节点记录二进制日志，从节点复制主节点的二进制日志进行回放
用于数据恢复
- 对于事务性表，二进制日志是在事务完成后，提交之前，锁未释放时记录的。
- 对于非事务性表，更新后立即记录二进制日志

二进制日志属性
- log_bin
  - 控制是否开启二进制日志，以及二进制日志的文件名
  - 会话级变量
    - sql_log_bin
- log_bin_basename
  - 二进制日志的目录和前缀。这是一个只读变量
- log_bin_index
  - 二进制日志的索引文件，包含了二进制日志的名称，用于跟踪二进制日志
  - 扩展名为 .index，和二进制日志在同一个目录
- log_slave_updates
  - 对于复制结构，默认情况下，从节点不会把从主节点接收到的日志写入自己的二进制日志中。启用此变量，可以使从节点记录二进制日志
  - 当从节点同时也充当其他复制结构的主节点时，可以启用这个变量。
- binlog_format
  - 二进制日志的格式
- max_binlog_size
  - 二进制日志的大小
缓存
- binlog_cache_size
  - 二进制日志缓冲区大小。
  - 处理事务的线程启动时，为语句分配一个缓冲区。
    - 如果语句比这个缓冲区大，将会创建一个临时文件来存储事务，线程结束后，将删除临时文件
- max_binlog_cache_size
  - 用来限制缓存事务的总大小。
  - 如果事务大于此值，将失败并回滚。
  - 默认 4G，范围 4096-4G
同步机制
- sync_binlog
  - 二进制日志的同步机制 (双1参数之一)
  - 变量值
    - 1：(默认) 每次写入都同步到磁盘，最安全，也最慢
~~–innodb_support_xa~~
已过时。
- ~~1：(默认) 确保二进制日志和 InnoDB 数据文件同步~~

binlog_cache_use
- 显示此缓冲区和临时文件存储语句的事务数
binlog_cache_disk_use
- 显示实际上有多少个事务使用了临时文件

默认情况下，服务器记录事件长度和事件本身，然后使用事件长度验证是否正确写入
也可以设置校验和来验证事件
- 系统变量
  - binlog_checksum
  - master_verify_checksum
  - slave_sql_verify_checksum

–binlog-do-db 与 –replicate-do-db 评估方式相同
–binlog-ignore-db 与 –replicate-ignore-db 评估方式相同

以下变量会写入二进制日志中

约束检查
- foreign_key_checks
- unique_checks
字符集和排序规则
- character_set_client
- collation_connection
- collation_database
- collation_server
其他
- sql_mode (NO_DIR_IN_CREATE 模式不复制)
- sql_auto_is_null

shell> mysqlbinlog log_file | mysql -h server_name
shell> mysqlbinlog -d zabbix --base64-output=decode-rows -vvv log_file

自动清理

系统变量

expire_logs_days

mysql> show variables like '%expire_log%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+

二进制日志的过期时间

设置依据
- 最好是保留2个全备周期的天数+1

手动清理
- 清理所有的二进制日志
```
mysql> reset master;
```
- 清理部分二进制日志
```
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004';
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
```
  Note: purge binary logs 语句会更新二进制日志的索引文件。

生成新日志

立即把脏数据刷新到磁盘，并启用新的二进制日志。

mysql> flush logs;  -- 刷新所有日志
mysql> flush binary logs;  -- 只刷新二进制日志

重置日志编号
使日志编号从1开始重新计数。
```
mysql> reset master;
```

如果未开启 GTID

二进制日志的记录单元
- 事件 (event)
  - 二进制日志的最小单元
  - 对于 DDL 语句，每个语句就是一个事件
  - 对于 DML 语句，一个事务包含多个语句
    
    begin; — 事件1
    a; — 事件2
    b; — 事件3
    commit; — 事件4
- 事件的开始和结束
  - 方便从日志中截取日志事件

截取方法

查看日志列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       154 |
+------------------+-----------+

查看正在使用的二进制日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      414 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查看事件列表

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                 |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                       |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| mysql-bin.000003 | 219 | Query          |         6 |         291 | BEGIN                                                 |
| mysql-bin.000003 | 291 | Table_map      |         6 |         339 | table_id: 109 (test.t1)                               |
| mysql-bin.000003 | 339 | Write_rows     |         6 |         383 | table_id: 109 flags: STMT_END_F                       |
| mysql-bin.000003 | 383 | Xid            |         6 |         414 | COMMIT /* xid=19 */                                   |
| mysql-bin.000003 | 414 | Anonymous_Gtid |         6 |         479 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| mysql-bin.000003 | 479 | Query          |         6 |         551 | BEGIN                                                 |
| mysql-bin.000003 | 551 | Table_map      |         6 |         599 | table_id: 109 (test.t1)                               |
| mysql-bin.000003 | 599 | Write_rows     |         6 |         644 | table_id: 109 flags: STMT_END_F                       |
| mysql-bin.000003 | 644 | Xid            |         6 |         675 | COMMIT /* xid=24 */                                   |
| mysql-bin.000003 | 675 | Anonymous_Gtid |         6 |         740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| mysql-bin.000003 | 740 | Query          |         6 |         855 | use `test`; create table t2(id int, name varchar(20)) |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+

pos：事件开始位置
end_log_pos：事件结束位置

截取事件

如果截取事务，请注意，begin 开始，commit 结束。

mysqlbinlog --start-position=740 --stop-position=855 mysql-bin.000003 > /tmp/binlog.sql

如果已开启 GTID
- 二进制日志的记录单元
  - 事务
- 截取方法
```
shell> mysqlbinlog --skip-gtids --include-gtids=2dac652a-5705-11eb-9e3a-000c29a8b526:3 /mdata/binlog/mysql-bin.000004 > /tmp/gtid.sql
```
  - –skip-gtids
    - 导出时忽略 gtid。在恢复时，系统会自动生成新的 gtid。
  - –include-gtids
    - 要截取的事务
  - –exclude-gtids
    - 要排除的事务
  - –sync-binlog=1
    - 每次事务提交都立即刷写 binlog 到磁盘

binlog_format
- 二进制日志的记录格式
- 有效值
  - STATEMENT
    - 基于语句模式
    - 在主从复制时，基于语句模式可能导致问题。
```
insert into t1 values(now());
```
  - ROW
    - (默认) 基于行模式
    - 复制结构中，表必须有主键
    - 如果使用二进制日志和基于行的日志记录，则并发插入将转换为 CREATE ... SELECT 或 INSERT ... SELECT 语句的普通插入，这样做是为了确保可以通过在备份操作期间应用日志来重新创建表的精确副本
  - MIXED
    - 混合模式
    - 默认情况下，使用基于语句模式；某些情况下自动切换为基于行模式
binlog_row_event_max_size
- 基于行模式下，二进制日志的最大大小
- 行以块大小存储在二进制日志中，块大小不超过这个变量的值，单位 bytes，这个值必须是256的倍数，默认8192

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'STATEMENT';

Note: 二进制日志的记录格式还可以通过所使用的存储引擎来设置或限制。这有助于消除不同存储引擎之间主从复制的问题。

为何设置会话级变量
- 对数据库进行许多小更改的会话可能要使用基于行模式
- 一次性执行多行更新的操作，使用基于语句模式效率更高
- 某些语句需要执行很久，但是只更改了几行的数据，这个时候使用基于行模式更有利
无法动态修改的情况
- 在函数和触发器中
- ~~启用了 NDB 存储引擎~~
- 当前启用了基于行模式，并且具有打开的临时表
  
  存在临时表时，不建议动态切换复制格式。因为只有基于语句模式，才记录临时表，基于行模式，不记录临时表，混合模式，也会记录临时表。
  
  在复制时，切换复制格式也会导致问题。
  
  主从复制结构中，主从节点的复制格式必须一致。
  
  当使用基于行模式时，对于所有的 DDL 语句，仍然使用基于语句的模式，如 create/alter/drop table。
- 复制通道有打开的临时表
- 当前正在运行任何复制通道应用程序线程

混合模式下，有时会自动从基于语句模式切换到基于行模式。

~~当DML语句更新 NDBCLUSTER 表~~
当函数中包含 UUID()
当更新一个或多个存在自增列的表，且调用触发器或存储过程时
当视图的主体需要使用基于行模式时，创建视图的语句也使用它
当调用自定义函数时
~~当一条语句按行记录，且用到临时表时，后续语句都会按行记录，直到临时表被删除~~

8.0 版本，对临时表的操作不会以混合二进制日志格式记录，并且会话中临时表的存在不会影响每个语句所使用的日志模式。
当用到 found_row() 或 row_count() 时
当用到 user()、current_user()、current_user 变量时
当一条语句引用一个或多个系统变量时
当涉及到 mysql 数据库中的日志表时
当用到 load_file() 时

可以在 MySQL 服务器实例上启用二进制日志事务压缩。启用二进制日志事务压缩后，将使用 zstd 算法压缩事务有效负载，然后将其作为单个事件 (Transaction_payload_event) 写入服务器的二进制日志文件。

二进制日志以压缩状态发送到副本，以压缩状态写入中继日志。因此可以节省主节点和从节点的存储空间，并节省网络带宽。但是也会增加 CPU 和内存资源的负载。

只有查看事件或回放时才会进行解压缩。

二进制日志事务压缩和连接压缩可以结合使用。

与事务的 GTID 相关的事件
其他类型的控制事件，例如，视图更改事件和心跳事件。
突发事件以及包含这些事件的所有事务的整体
非事务性事件以及包含它们的所有事务的整体
使用基于语句的二进制日志记录的事件。
- 二进制日志事务压缩仅适用于基于行的二进制日志记录格式。

对于半同步复制，当收到完整的 Transaction_payload_event 时，副本将确认事务。

支持二进制日志事务压缩的 MySQL 服务器版本可以处理压缩和未压缩的事务有效载荷的混合
复制/组复制中，不同的主机上，与压缩有关的系统变量可以不同

系统变量
- binlog_transaction_compression
  - 启用压缩。默认 OFF
- binlog_transaction_compression_level_zstd
  - zstd 的压缩级别
  - 默认3，范围是 1-22
对于主从复制
- CHANGE REPLICATION SOURCE TO
  - SOURCE_COMPRESSION_ALGORITHMS
  - SOURCE_ZSTD_COMPRESSION_LEVEL
- ~~CHANGE MASTER TO~~
  将来会被 CHANGE REPLICATION SOURCE TO 取代。
  - ~~MASTER_COMPRESSION_ALGORITHMS~~
  - ~~MASTER_ZSTD_COMPRESSION_LEVEL~~
对于组复制
- group_replication_compression_threshold
  - 组复制的压缩阈值
- group_replication_recovery_compression_algorithm
  - 组复制恢复的压缩算法
- group_replication_recovery_zstd_compression_level
  - 组复制恢复的 zstd 压缩级别

性能表

perfomance_schema.binary_log_transaction_compression_stats

mysql> select * from performance_schema.binary_log_transaction_compression_stats\G
*************************** 1. row ***************************
                            LOG_TYPE: BINARY
                    COMPRESSION_TYPE: NONE
                 TRANSACTION_COUNTER: 1
            COMPRESSED_BYTES_COUNTER: 107
          UNCOMPRESSED_BYTES_COUNTER: 107
              COMPRESSION_PERCENTAGE: 0
                FIRST_TRANSACTION_ID: ANONYMOUS
  FIRST_TRANSACTION_COMPRESSED_BYTES: 107
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 107
         FIRST_TRANSACTION_TIMESTAMP: 2021-03-17 08:15:07.809186
                 LAST_TRANSACTION_ID: ANONYMOUS
   LAST_TRANSACTION_COMPRESSED_BYTES: 107
 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 107
          LAST_TRANSACTION_TIMESTAMP: 2021-03-17 08:15:07.809186

统计信息包括监视期间的数据压缩率，还可以查看压缩对服务器上最后一个事务的影响。
可以通过截断 (truncate) 表格来重置统计信息。

perfomance_schema.events_stages_current
- 显示事务何时处于其事务有效负载的解压缩或压缩阶段，并显示该阶段的进度

查看帮助
```
mysqlbinlog --verbose --help | more
```

发表于：MySQL

2021-05-17

复制链接

转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。

连接管理

mysql_config_editor-MySQL 配置程序

MySQL 账户

错误日志

常规查询日志