Hello World

吞风吻雨葬落日 欺山赶海踏雪径

0%

mysql binlog

发表于 更新于 分类于

binlog (The Binary Log) 是mysql的二进制日志，记录了数据中数据变更的所有事件。

binlog 中没有记录 SELECT , SHOW 这类没有修改数据的语句。如果需要记录查询语句，可以使用The General Query Log。

开启binlog记录会产生轻微的性能损失，但是但，binlog能够设置复制和恢复操作，其好处通常超过了这种轻微的性能下降。

从8.0.14版本开始 ，binlog 文件可以加密，详见 Encrypting Binary Log Files and Relay Log Files

开启binlog

mysql8.0 之前只要设置 log_bin 就可以开启binlog:

1
2
3
4
5
6
7
8
9
[mysqld]
# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log_bin = binlog
binlog_format=mixed
# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 10
# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600

关闭只需要删除这两个配置

1
2
3
4
[mysqld]
#log_bin = binlog
#binlog_format=mixed
#expire_logs_days = 10

关闭的话 mysql8.0 之后可以使用 skip_log_bin 参数

1
2
3
4
[mysqld]
log_bin = binlog
# 注意 skip_log_bin 要放在 log_bin 之后才能生效
skip_log_bin = ON

另一个要注意的事skip_log_bin参数是不能通过 show global variables 语句查看的。

查看是否开启

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

查看binlog文件

1
2
3
4
5
6
7
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000004 |       180 | No        |
| binlog.000005 |      1972 | No        |
+---------------+-----------+-----------+

通常 binlog 文件在服务端的 data/ 目录下。

查看 master 当前binlog文件以及位置。

1
2
3
4
5
6
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |     1972 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

在以下三种情况下会创建新的binlog文件

  • 服务端启动或者重启
  • 服务端刷新日志
  • 当前binlog文件达到了 max_binlog_size 配置的大小

手动刷新日志flush logs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000004 |       180 | No        |
| binlog.000005 |      2016 | No        |
| binlog.000006 |       157 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

重置binlog

reset master 可以重置 binlog 日志文件，让日志重新从 000001 开始记录。

binlog 三种格式

binlog有三种格式

  • Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
  • Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
  • Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

Row 模式只记录了一条记录从什么改成了什么，会非常清楚的记录每一行数据变动的细节，同时也会产生大量的日志内容。

Statement 模式每条修改数据的sql都会记录下来，但是没有记录变动的数据，所以binlog的日志量小，降低了IO，提升了性能。

Mixed 模式是前两种的结合，由mysql根据执行的语句来抉择是使用 Statement 还是 Row，最大限度的记录了数据的变化，且同时降低了日志量。

binlog格式由 binlog_format 来指定，默认是 mixed

1
2
[mysqld]
binlog_format=mixed

也可以使用命令行设置 

1
set binlog_format = "STATEMENT"

查看

1
2
3
4
5
6
mysql> show variables like "binlog_format";
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

binlog查看

mysqlbinlog

binlog 是二进制文件，可以使用 mysqlbinlog 工具来查看

1
mysqlbinlog [options] log_file ...

比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
➜  mysql git:(stable) mysqlbinlog binlog.000005
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221129 13:58:35 server id 1  end_log_pos 126 CRC32 0x2edb0f3a 	Start: binlog v 4, server v 8.0.30 created 221129 13:58:35 at startup
ROLLBACK/*!*/;
BINLOG '
i5+FYw8BAAAAegAAAH4AAAAAAAQAOC4wLjMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACLn4VjEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAToP2y4=
'/*!*/;
# at 126
#221129 13:58:35 server id 1  end_log_pos 157 CRC32 0xfce57914 	Previous-GTIDs
# [empty]
# at 157
#221129 14:01:57 server id 1  end_log_pos 236 CRC32 0x89886155 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1669701717173716	immediate_commit_timestamp=1669701717173716	transaction_length=1112
# original_commit_timestamp=1669701717173716 (2022-11-29 14:01:57.173716 CST)
# immediate_commit_timestamp=1669701717173716 (2022-11-29 14:01:57.173716 CST)
/*!80001 SET @@session.original_commit_timestamp=1669701717173716*//*!*/;
/*!80014 SET @@session.original_server_version=80030*//*!*/;
/*!80014 SET @@session.immediate_server_version=80030*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#221129 14:01:57 server id 1  end_log_pos 1269 CRC32 0xc21f8573 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 449
use `test`/*!*/;
SET TIMESTAMP=1669701717/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DataGrip 2022.2.5 */ CREATE TABLE `word_en` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `w_code` varchar(64) NOT NULL COMMENT 'code',
  `word` varchar(64) NOT NULL COMMENT 'word',
  `accent` varchar(64) NOT NULL COMMENT 'accent',
  `mean_cn` varchar(1024) NOT NULL COMMENT 'mean_cn',
  `mean_en` varchar(1024) DEFAULT NULL COMMENT 'mean_en',
  `sentence` varchar(1024) DEFAULT NULL COMMENT 'sentence',
  `sentence_trans` varchar(1024) DEFAULT NULL COMMENT 'sentence_trans',
  `sentence_phrase` varchar(1024) DEFAULT NULL COMMENT 'sentence_phrase',
  `word_etyma` varchar(1024) DEFAULT NULL COMMENT 'word_etyma',
  `level` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'level',
  `weight` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'weight',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`w_code`),
  KEY `idx_word` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='英文词汇表'
/*!*/;
# at 1269
#221129 14:02:18 server id 1  end_log_pos 1348 CRC32 0xe7453964 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1669701738190811	immediate_commit_timestamp=1669701738190811	transaction_length=703
# original_commit_timestamp=1669701738190811 (2022-11-29 14:02:18.190811 CST)
# immediate_commit_timestamp=1669701738190811 (2022-11-29 14:02:18.190811 CST)
/*!80001 SET @@session.original_commit_timestamp=1669701738190811*//*!*/;
/*!80014 SET @@session.original_server_version=80030*//*!*/;
/*!80014 SET @@session.immediate_server_version=80030*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1348
#221129 14:02:18 server id 1  end_log_pos 1430 CRC32 0x69617d57 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1669701738/*!*/;
BEGIN
/*!*/;
# at 1430
#221129 14:02:18 server id 1  end_log_pos 1941 CRC32 0xde97589f 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1669701738/*!*/;
/* ApplicationName=DataGrip 2022.2.5 */ INSERT INTO `word_en`(`id`,`w_code`,`word`,`accent`,`mean_cn`,`mean_en`,`sentence`,`sentence_trans`,`sentence_phrase`,`word_etyma`,`level`,`weight`) VALUES (1,'get_close_(to)','get close (to)','/ɡet kloʊs tu/','phr. 接近','be close to','It wasn\'t until I got close to the end that I realized I had won the race.','直到我接近终点我才意识到我已经赢得了比赛。','','',1,0)
/*!*/;
# at 1941
#221129 14:02:18 server id 1  end_log_pos 1972 CRC32 0x3a770fbf 	Xid = 1056
COMMIT/*!*/;
# at 1972
#221129 20:08:35 server id 1  end_log_pos 2016 CRC32 0xf3d5585f 	Rotate to binlog.000006  pos: 4
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
# at 1348
#221129 14:02:18 server id 1  end_log_pos 1430 CRC32 0x69617d57 	Query	thread_id=10	exec_time=0	error_code=0
语句;

show binlog events

在mysql客户端中 使用show binlog events查看，语法:

1
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  • log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
  • pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
  • offset：这是是偏移量，不指定默认就是 0。
  • row_count：查看多少行记录，不指定就是查看所有。
1
mysql> show binlog events in 'binlog.000005';

show binlog events