Mysql 系列 | binlog 详解

binlog 会记录表结构变更和表数据变更，有了 binlog 和 redolog，可以还原任意时刻的数据库状态。

binlog 二进制文件通常用来数据恢复、主从复制、审计。

本篇 Mysql 版本为 8.0。

归档日志（binlog）详解

binlog 的写入机制

• 事务执行过程中，先写到 binlog cache 中，日志提交时，再把 binlog cache 写到 binlog 文件中。

• 一个事务的 binlog 是一个整体，必须一次性写入，当超过了设置的 cache-size，就要暂存在磁盘中。

binlog 基础参数

• log_bin binlog 的开关。

• log_bin_basename log 文件位置。

• log_bin_index binlog 索引文件。

• sql_log_bin binlog 的开关。

mysql> show variables like "%log_bin%";
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

相关配置

• binlog_cache_size 每个线程中，每个事务 binlog-cache 的最大容量，默认为 32M。

• max_binlog_cache_size binlog-cache 的最大容量。

• sync_binlog 持久化到磁盘的时机。write 指日志写入 cache；fsync 指日志持久化到磁盘中，默认为 1。

  • 0，每次提交事务只 write 不 fsync。
  • 1，每次提交事务都 fsync。
  • N，每次提交事务都 write，累计 N 个事务后才 fsync。

• binlog_group_commit_sync_delay 延迟多少微秒后才 fsync，默认为 0。

• binlog_group_commit_sync_no_delay_count 累计多少个事务提交后才 fsync，默认为 0。

• binlog_format binlog 的记录模式。默认为 MIXED。

  • ROW 清除记录每一行数据的修改细节，但是会产生大量日志。

  • STATEMENT 记录执行的 SQL 语句，日志量小，但有些情况会导致主从数据不一致。

  • MIXED 是前两种模式的混合，记录准确，文件大小适中。

• max_binlog_size 每个 binlog 文件的大小，默认为 1G。

• binlog_expire_logs_seconds binlog 过期时间，默认为 30 天。

mysql> show variables like "%binlog%";
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_expire_logs_seconds                 | 2592000              |
| binlog_format                              | MIXED                |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_row_metadata                        | MINIMAL              |
| binlog_row_value_options                   |                      |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
24 rows in set (0.00 sec)

SQL 命令查看 binlog

• binlog 文件列表。show binary logs;

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000083 |   8667374 |
| mysql-bin.000084 |    122684 |
| mysql-bin.000085 |     32099 |
| mysql-bin.000086 | 794208651 |
| mysql-bin.000087 |  39070717 |
+------------------+-----------+
5 rows in set (0.00 sec)

• 当前正在写入的 binlog 文件。show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000087 | 39120223 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 查看 binlog 文件的内容。event 是 binlog 的最小单位。
  show binlog events [in 'log_name'] [FROM pos] [limit [offset,] row_count]

  • Log_name binlog 文件名

  • Pos 开始的位置

  • Event_type 事件类型

  • Server_id Mysql 服务 ID

  • End_log_pos 结束的位置

  • Info 事件内容

mysql> show binlog events in 'mysql-bin.000087' FROM 12542 limit 0,3 \G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000087
        Pos: 12542
 Event_type: Query
  Server_id: 1
End_log_pos: 12666
       Info: BEGIN
*************************** 2. row ***************************
   Log_name: mysql-bin.000087
        Pos: 12666
 Event_type: Query
  Server_id: 1
End_log_pos: 12988
       Info: use `[数据库名]`; INSERT INTO xxxxxx (xxxxxxxxxxxxxxxx) VALUES (xxxxxxxxxxxxxx)
*************************** 3. row ***************************
   Log_name: mysql-bin.000087
        Pos: 12988
 Event_type: Xid
  Server_id: 1
End_log_pos: 13019
       Info: COMMIT /* xid=541 */
3 rows in set (0.00 sec)

用 mysqlbinlog 查看二进制 binlog 文件

mysqlbinlog [binlog 文件名]

常用参数

• --start-position 指定开始位置

• --stop-position 指定结束位置

• -start-datetime="yyyy-MM-dd HH:mm:ss" 指定开始时间

• --stop-datetime 指定结束时间

• --database 指定数据库

文件内容

和上面 SQL 查得同样的 3 个 event，binlog 文件内容如下。

• 【# at 12666】一个 event 内容的开始，表示开始的位置

• 【#220714 14:11:35 server id 1 end_log_pos 12988 CRC32 0xbe5015be Query thread_id=65 exec_time=0 error_code=0】

  • 记入时间，220714 14:11:35

  • Mysql 服务 ID，server id 1

  • 结束位置，end_log_pos 12988

  • 校验内容，CRC32 0xbe5015be

  • 事件类型，Query

  • 线程 ID，thread_id=65

  • 执行时间，exec_time=0

  • 错误编码，error_code=0

• 下面几行，到下一个【# at xxx】结束，是执行的 SQL 语句

# mysqlbinlog mysql-bin.000087 --start-position=12542 --stop-position=13019
.
.
.
# at 12542
#220714 14:11:35 server id 1  end_log_pos 12666 CRC32 0xca59c21a        Query   thread_id=65    exec_time=0     error_code=0
SET TIMESTAMP=1657775495/*!*/;
SET @@session.pseudo_thread_id=65/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113664/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 12666
#220714 14:11:35 server id 1  end_log_pos 12988 CRC32 0xbe5015be        Query   thread_id=65    exec_time=0     error_code=0
use `[数据库名]`/*!*/;
SET TIMESTAMP=1657775495/*!*/;
INSERT INTO xxxxxx (xxxxxxxxxxxx) VALUES (xxxxxxxxxxxx)
/*!*/;
# at 12988
#220714 14:11:35 server id 1  end_log_pos 13019 CRC32 0x81af69ed        Xid = 541
COMMIT/*!*/;
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*/;

binlog 恢复数据

把查到的 binlog 的内容给到 mysql 去执行。（-v 表示执行 sql 语句）

# mysqlbinlog mysql-bin.000087 --databse=[database name] --start-position=12542 --stop-position=13019 | mysql -uroot -pxxxx -v [database name]

---

看懂 binlog 内容，可以更方便地还原数据等操作。