[数据库] MYSQL之binlog概述
1 概述: MYSQL数据库的二进制日志：bin log
MYSQL数据库的日志种类
MySQL的日志主要有七种。以下是这七种日志的详细信息：
- 错误日志(Error Log)。记录MySQL服务器在启动、运行或停止过程中出现的问题，包括错误和警告信息，对于诊断问题非常有用。
- 查询日志(General Query Log)。记录所有客户端连接和执行的SQL语句，包括SELECT查询，主要用于调试和性能分析。
- 慢查询日志(Slow Query Log)。记录执行时间超过预设阈值的SQL查询语句，用于优化数据库性能。
- 二进制日志(Binary Log, Binlog)。记录所有修改数据的操作，如INSERT、UPDATE、DELETE等，用于数据恢复、主从复制和点播恢复。
- 重做日志(Redo Log)。记录数据库引擎对数据文件进行的物理修改，如页的插入、更新和删除操作，用于在故障发生时恢复数据到一致状态。
- 回滚日志(Undo Log)。记录数据库引擎对事务进行的修改操作的逆操作，用于事务回滚或数据库崩溃时撤销事务的影响，也用于保证事务的原子性和实现多版本并发控制(MVCC)。
- 中继日志(Relay Log)。在复制环境中，从服务器用于存储主服务器二进制日志事件的日志，用于从服务器将数据同步到主服务器的状态。
什么是二进制日志(binlog)?
MySQL的二进制日志binlog，可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是【事务安全型】的。
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。
二进制日志包括两类文件：
二进制日志索引文件（文件名后缀为 .index ），用于记录所有的二进制文件
二进制日志文件（文件名后缀为 .00000* ，如：mysql-bin.186436），用于记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
show binlog events in 'mysql-bin.186436' from 24659
什么是事务日志？(redo log/undo log)
事务日志的目的：实例或者介质失败，事务日志文件就能作为备份数据而派上用场。
innodb事务日志包括:
redo log : 指事务开始之前， 在操作任何数据之前,首先将需操作的数据备份到一个地方
undo log : 指事务中操作的任何数据,将最新的数据备份到一个地方
- redo log 事务日志
不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。
具体的落盘策略可以进行配置 。
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
RedoLog是为了实现事务的持久性而出现的产物
- undo log 事务日志
用来回滚行记录到某个版本。
事务未提交之前，Undo保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读。
undo log是为了实现事务的原子性而出现的产物,在Mysql innodb存储引擎中用来实现多版本并发控制
写 Binlog 的时机？ sync_binlog
对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
- 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
- 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
- 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
sync_binlog的设置
如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。
在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。
【二进制日志处理事务】和【非事务性语句】的区别?
在事务性语句（update）执行过程中，服务器将会进行额外的处理，在服务器执行时多个事务是并行执行的，为了把他们的记录在一起，需要引入事务日志的概念。在事务完成被提交的时候一同刷新到二进制日志。
对于非事务性语句（insert，delete）的处理。遵循以下3条规则：
1）如果非事务性语句被标记为事务，那么: 将被写入重做日志(redo log)。
2）如果没有标记为事务，而且重做日志中没有，那么: 直接写入二进制日志(bin log)。
3）如果没有标记为事务，但是重做日志中有，那么: 写入重做日志(redo log)。
注意: 如果在一个事务中有非事务性语句，那么: 将会利用规则2，优先将该影响非事务表语句直接写入二进制日志。
Binlog日志的应用场景
- MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
- 数据恢复：通过使用 mysqlbinlog 工具来使恢复数据
Binlog日志的代价
一般来说，开启binlog日志大概会有1%的性能损耗。（未实际求证）
GTID(全局事务ID)
从
MySQL 5.6开始增加了强大的GTID（Global Transaction ID，全局事务ID）这个特性，用来强化数据库的主备一致性， 故障恢复， 以及容错能力。用于取代过去传统的主从复制（即：基于binlog和position的异步复制）。
借助GTID，在发生主备切换的情况下，MySQL的其他slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制position发生误操作的风险。
另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。
背景导入 := 传统 BIN LOG 文件 + POS 的替代方案
- 情景引入
Command =
Binlog Dump GTID
-
GTID的诞生背景
GTID出现之前，在一主多从的复制拓扑中，如果主库宕机，需要从多个从库选择之一作为新主库，这个过程比较复杂。没有一种直接了当的方法找到其它从库对应的新主库二进制日志坐标。通常的做法是先要寻找每个从库复制原主库的最后语句，然后找到新主库中包含该语句的二进制日志文件，其中该语句后的第一个事件位置即为连接新主库的二进制坐标。主要难点在于不存在一个唯一标识指出“复制原主库的最后语句”，于是后来的MySQL中就出现了GTID的概念。 -
优点
替代传统的binlog + pos 复制；使用master_auto_position=1自动匹配GTID断点进行复制
SET @@SESSION.GTID_NEXT= 'f0a5a37b-4a03-11ed-89fd-fa163e42cb44:36885565'
GTID 概念
- GTID 即 全局事务ID (global transaction identifier), 其保证为每一个在主库上提交的事务在复制集群中可以生成一个唯一ID。
官网：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-lifecycle.html
GTID 编号的组成 = { Source Id = Mysql Server UUID}:{Transaction ID}
- GTID = source_id:transaction_id
其中
source_id一般指 source 的 server_uuid
示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23
- server_uuid
在MySQL第一次启动时自动生成并持久化到 auto.cnf 文件（存放在数据目录下，每台机器的 server_uuid 都不一样。
UUID：每个mysql实例的唯一ID。
由于会传递到 slave，所以也可以理解为MYSQL实例的源ID。
- transaction_id
一个从1开始的自增计数，表示在这个主库上执行的第n个事务，一个数值对应一个事务。
MySQL会保证事务与GTID之间的1:1映射，如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1表示在以b6af5b5c-666f-11e9-bed3-000c29b85ea6为唯一标识的MySQL实例上执行的第1个数据库事务。
一组连续的事务可以用 "-" 连接的事务序号范围表示。例如：
b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5
GTID 比传统复制的优势
- 更简单的实现 failover，不用以前那样在需要找 log_file 和 log_Pos 。
- 更简单的搭建主从复制。
- 比传统复制更加安全。
- GTID 是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。
- 多线程复制
GTID 与 Binlog 的关系
-
GTID 在binlog中的结构
-
GTID event 结构
-
Previous_gtid_log_event
Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。
换句话说，如果你手动set global gtid_purged=xx； 那么xx是不会记录在Previous_gtid_log_event中的。
GTID 的工作原理
- master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
- slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
- sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
- 如果有记录，说明该GTID的事务已经执行，slave会忽略。
- 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
- 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
GTID的作用
1、根据GTID可以知道事务最初是在哪个实例上提交的
2、GTID的存在方便了Replication的Failover
GTID 相关参数
| 参数 | comment |
|---|---|
| gtid_executed | 执行过的所有GTID |
| gtid_purged | 丢弃掉的GTID |
| gtid_mode | GTID模式 |
| gtid_next | session级别的变量，下一个gtid |
| gtid_owned | 正在运行的GTID |
| enforce_gtid_consistency | 保证GTID安全的参数 |
注：
purged: 清除(英译)
> show global variables like '%gtid%';
Variable_name |Value |
--------------------------------+-----------------------------------------------+
binlog_gtid_simple_recovery |ON |
enforce_gtid_consistency |ON |
gtid_executed |f0a5a37b-4a03-11ed-89fd-fa163e42cb44:1-36890196|
gtid_executed_compression_period|1000 |
gtid_mode |ON |
gtid_owned | |
gtid_purged |f0a5a37b-4a03-11ed-89fd-fa163e42cb44:1-36759372|
session_track_gtids |OFF |
> show variables like '%server%id%';
Variable_name |Value |
--------------+------------------------------------+
server_id |1852072115 |
server_id_bits|32 |
server_uuid |f0a5a37b-4a03-11ed-89fd-fa163e42cb44|
- gtid_executed
在当前实例上执行过的GTID集合，实际上包含了所有记录到binlog中的事务。
所以，设置set sql_log_bin=0后执行的事务不会生成binlog事件，也不会被记录到gtid_executed中。
执行 reset master 可以将该变量清空
---- 系统表 mysql.gtid_executed 存放了所有执行过的GTID(在活动的binlog中的除外)，但是由于不包含活动的binlog当中的GTID。
---- 因此，需要查看精确值时，可以查看 `global variable gtid_executed` 的值，
> select * from mysql.gtid_executed;
source_uuid |interval_start|interval_end|
------------------------------------+--------------+------------+
f0a5a37b-4a03-11ed-89fd-fa163e42cb44| 1| 36888871|
> show global variables like 'gtid_executed'
Variable_name|Value |
-------------+-----------------------------------------------+
gtid_executed|f0a5a37b-4a03-11ed-89fd-fa163e42cb44:1-36888993|
- gtid_purged
binlog不可能永久停留在服务器上，需要进行定期清理（如通过expire_logs_days），否则迟早它会把磁盘空间用完。
gtid_purged 用于记录已经被清除了的binlog事务集合，它是 gtid_executed 的子集。
只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为和 gtid_purged 相同的值。
gtid_executed 为空意味着要么之前没有启动过基于GTID的复制，要么执行过reset master。
执行 reset master 时同样也会把gtid_purged置空，即始终保持 gtid_purged 是 gtid_executed 的子集。已经清除的gtid集合(历史事务 binlog已被删除)。
包含了所有已经提交过的，但是不在 binlog 当中的 GTID ，它是 gtid_executed 的子集。以下几种 GTID 都会添加到 gtid_purged 当中：
- 未开启binlog的从库上提交过的GTID
- 已经被 "purge" 掉的 binlog 当中的 GTID 。(当发出 purge binary log 命令之后，如果被 purge的binlog中包含有GTID，那么查看 gtid_purged 变量值的时候，就会看到该变量值发生了变化)
- 使用 'set global gtid_purged= "xxxx" ' 添加的 GTID
人为设置 gtid_purged 的目地是为了告诉服务器，即使它们不在 binlog 中，这些 GTID 已经 被 applied 过了，不能/不需要再做重做。
- gtid_next
会话级变量，指示如何产生下一个GTID。可能的取值：
- AUTOMATIC，自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID
- ANONYMOUS，设置后执行事务不会产生GTID
显式指定的GTID，可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则，下次执行事务时会报错
- gtid_mode
是否开启GTID复制功能
- enforce-gtid-consistency = ON
启动强制GTID的一致性，如果开启GTID功能则此参数必须要开启；
slave在做同步复制时，无须找到binlog日志和POS点，直接change master to master_auto_position=1即可，自动根据GTID进行同步数据。
2 MySQL 二进制日志的使用分析
show master logs : 查看 binlog 日志文件列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 923 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show master logs;
Log_name |File_size|
----------------+---------+
mysql-bin.185431| 105503|
mysql-bin.185432| 25752|
mysql-bin.185433| 77319|
mysql-bin.185434| 114056|
mysql-bin.185435| 104589|
...
show master status : 查询 主库 binlog 最新状态
- 查看主服务状态，以得到最新的二进制日志文件的名称、位置信息
作用: 查看当前 binlog 已记录到哪里了
作用：用于获取当前 MySQL服务器作为主服务器时的二进制日志状态
> show master status;
File |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set |
----------------+--------+------------+----------------+-----------------------------------------------+
mysql-bin.186515| 49869| | |f0a5a37b-4a03-11ed-89fd-fa163e42cb44:1-36896122|
参数解释
- File : 显示当前正在写入的二进制日志文件的名称
- Position : 显示当前写入位置的偏移量。这是二进制日志文件中的字节位置
- Binlog_Do_DB : 如果设置了
binlog-do-db选项，这里会列出被包含在二进制日志中的数据库名称。如果未设置，此字段为空- Binlog_Ignore_DB : 如果设置了
binlog-ignore-db选项，这里会列出被排除在二进制日志之外的数据库名称。如果未设置，此字段为空- Executed_Gtid_Set : 已执行的 GTID 集合
- 使用场景1：MYSQL 主从同步架构的配置时
当设置 MySQL 主从复制时，你需要在从服务器上配置
CHANGE MASTER TO命令，其中需要提供主服务器的二进制日志文件名和位置。
例如：
# step1 停止同步
STOP SLAVE;
# step2 修改从库指向到主库，使用主库记录的文件名以及位点
# 主库show master status; 显示的 File, Position ）
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=107;
# step3 启动同步
START SLAVE;
这里的
MASTER_LOG_FILE和MASTER_LOG_POS就是从SHOW MASTER STATUS;命令的输出中得到的。
推荐文献
show binlog events : 分析增量日志事件
binlog文件中存储的内容称之为二进制事件，简称事件。
我们的每一个数据库更新操作(Insert、Update、Delete等)，都会对应的一个事件。
- 使用
show binlog events命令可以以列表的形式显示日志中的事件信息。
语法格式
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
说明：
- （1）IN ‘log_name’：指定要查询的binlog文件名（如果省略此参数，则默认指定第一个binlog文件）；
- （2）FROM pos：指定从哪个pos起始点开始查起（如果省略此参数，则从整个文件的第一个pos点开始算）；
- （3）LIMIT【offset】：偏移量（默认为0）；
- （4）row_count：查询总条数（如果省略，则显示所有行）。
场景：查询所有日志文件的日志事件
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000001 | 291 | Table_map | 1 | 339 | table_id: 113 (hist.t2) |
| mysql-bin.000001 | 339 | Write_rows | 1 | 413 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000001 | 413 | Xid | 1 | 444 | COMMIT /* xid=16 */ |
| mysql-bin.000001 | 444 | Anonymous_Gtid | 1 | 509 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 509 | Query | 1 | 581 | BEGIN |
| mysql-bin.000001 | 581 | Table_map | 1 | 629 | table_id: 113 (hist.t2) |
| mysql-bin.000001 | 629 | Update_rows | 1 | 845 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000001 | 845 | Xid | 1 | 876 | COMMIT /* xid=17 */ |
| mysql-bin.000001 | 876 | Rotate | 1 | 923 | mysql-bin.000002;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
场景：查询【指定日志文件】的日志事件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 112 (hist.t1) |
| mysql-bin.000002 | 336 | Write_rows | 1 | 386 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000002 | 386 | Xid | 1 | 417 | COMMIT /* xid=39 */ |
| mysql-bin.000002 | 417 | Anonymous_Gtid | 1 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 482 | Query | 1 | 554 | BEGIN |
| mysql-bin.000002 | 554 | Table_map | 1 | 599 | table_id: 112 (hist.t1) |
| mysql-bin.000002 | 599 | Write_rows | 1 | 649 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000002 | 649 | Xid | 1 | 680 | COMMIT /* xid=40 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.00 sec)
场景：查询【日志文件=mysql-bin.000002】，从【pos=417】开始查询
mysql> show binlog events in 'mysql-bin.000002' from 417;
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000002 | 417 | Anonymous_Gtid | 1 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 482 | Query | 1 | 554 | BEGIN |
| mysql-bin.000002 | 554 | Table_map | 1 | 599 | table_id: 112 (hist.t1) |
| mysql-bin.000002 | 599 | Write_rows | 1 | 649 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000002 | 649 | Xid | 1 | 680 | COMMIT /* xid=40 */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
5 rows in set (0.00 sec)
场景：查询【日志文件=mysql-bin.000002】，从【pos=219】开始查询，查询【5条记录】
mysql> show binlog events in 'mysql-bin.000002' from 219 limit 5;
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 112 (hist.t1) |
| mysql-bin.000002 | 336 | Write_rows | 1 | 386 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000002 | 386 | Xid | 1 | 417 | COMMIT /* xid=39 */ |
| mysql-bin.000002 | 417 | Anonymous_Gtid | 1 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
5 rows in set (0.00 sec)
场景：查询【日志文件=mysql-bin.000002】，从【pos=219】开始查询，查询【5条记录】，【偏移行数=2行】
mysql> show binlog events in 'mysql-bin.000002' from 219 limit 2,5;
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000002 | 336 | Write_rows | 1 | 386 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000002 | 386 | Xid | 1 | 417 | COMMIT /* xid=39 */ |
| mysql-bin.000002 | 417 | Anonymous_Gtid | 1 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 482 | Query | 1 | 554 | BEGIN |
| mysql-bin.000002 | 554 | Table_map | 1 | 599 | table_id: 112 (hist.t1) |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
5 rows in set (0.00 sec)
binlog 日志管理事件
- 使用
show binlog events命令查询到的每一行数据就是一个binlog日志管理事件。
当使用
mysqlbinlog查看日志信息时，每两个【at 数字】之间的信息就对应一个管理事件。
mysql> show binlog events in '/var/lib/mysql/mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 2 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 2 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 2 | 298 | BEGIN |
| mysql-bin.000002 | 298 | Query | 2 | 397 | use `hist`; update t2 set id=id+10000 |
| mysql-bin.000002 | 397 | Xid | 2 | 428 | COMMIT /* xid=11 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
6 rows in set (0.00 sec)
每一个管理事件对应的信息说明
- （1）Log_name：当前事件所在的binlog文件名称；
- （2）Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件。这种方式很常见，很多格式的文件，如pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。
- （3）Event_type：表示事件的类型；
- （4）Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置；
- （5）End_log_position：下一个事件的开始位置；
- （6）Info：当前事件的描述信息。
binlog 的3大模式 : Statement / Row / Mixed
- binlog 有三种模式：
- Statement: Binlog中存储SQL的语句，存储日志量是最小的。但是，对于user()等函数存在bug
- Row： 是主从复制更加安全，但是存储日志量大，但是不能直接进行读取。（默认）
- Mixed：介于两者之间，对于不确定的操作使用Row记录。根据SQL语句有系统决定是基于段还是行复制。
Statement 模式下的事件说明
以下是一个Statement日志格式的管理事件信息：
mysql> show binlog events in '/var/lib/mysql/mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 2 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 2 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 2 | 298 | BEGIN |
| mysql-bin.000001 | 298 | Intvar | 2 | 330 | INSERT_ID=2 |
| mysql-bin.000001 | 330 | Query | 2 | 456 | use `hist`; insert into t2(name) values('zhang'),('wang'),('li') |
| mysql-bin.000001 | 456 | Xid | 2 | 487 | COMMIT /* xid=31 */ |
| mysql-bin.000001 | 487 | Anonymous_Gtid | 2 | 552 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 552 | Query | 2 | 631 | BEGIN |
| mysql-bin.000001 | 631 | Intvar | 2 | 663 | INSERT_ID=5 |
| mysql-bin.000001 | 663 | Query | 2 | 789 | use `hist`; insert into t2(name) values('jack'),('tom'),('rose') |
| mysql-bin.000001 | 789 | Xid | 2 | 820 | COMMIT /* xid=32 */ |
| mysql-bin.000001 | 820 | Anonymous_Gtid | 2 | 885 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 885 | Query | 2 | 964 | BEGIN |
| mysql-bin.000001 | 964 | Query | 2 | 1063 | use `hist`; update t2 set id=id+10000 |
| mysql-bin.000001 | 1063 | Xid | 2 | 1094 | COMMIT /* xid=33 */ |
| mysql-bin.000001 | 1094 | Anonymous_Gtid | 2 | 1159 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1159 | Query | 2 | 1250 | create database wgx |
| mysql-bin.000001 | 1250 | Anonymous_Gtid | 2 | 1315 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1315 | Query | 2 | 1451 | use `wgx`; create table t1(id int primary key auto_increment,name char(20)) |
| mysql-bin.000001 | 1451 | Anonymous_Gtid | 2 | 1516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1516 | Query | 2 | 1593 | BEGIN |
| mysql-bin.000001 | 1593 | Intvar | 2 | 1625 | INSERT_ID=1 |
| mysql-bin.000001 | 1625 | Query | 2 | 1741 | use `wgx`; insert into t1(name) values('zhang'),('tom') |
| mysql-bin.000001 | 1741 | Xid | 2 | 1772 | COMMIT /* xid=44 */ |
| mysql-bin.000001 | 1772 | Stop | 2 | 1795 | |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------+
26 rows in set (0.01 sec)
说明：
- （1）每个binlog文件总是以Format Description Event作为开始，以Rotate Event（Stop Event）作为结束。在开始和结束之间，穿插着其他各种事件。
- （2）每个事务都是以Query Event作为开始，其INFO列内容为BEGIN，以Xid Event表示结束，其INFO列内容为COMMIT。即使对于单条更新SQL我们没有开启事务，Mysql也会默认的帮我们开启事务。
- （3）Intvar Event事件：如果主键是自增（AUTO_INCREMENT）列，MySQL首先会自增一个值，这就是Intvar Event的作用。需要注意的是，该事件，只会在Statement模式下出现。
- （4）Query Event：记录的就是插入的SQL。这也体现了Statement模式的作用，就是记录执行的SQL。
- （5）Stop Event：表示日志文件的结束。
Row模式下的事件说明
以下是一个Row日志格式的管理事件信息：
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000001 | 291 | Table_map | 1 | 339 | table_id: 113 (hist.t2) |
| mysql-bin.000001 | 339 | Write_rows | 1 | 413 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000001 | 413 | Xid | 1 | 444 | COMMIT /* xid=16 */ |
| mysql-bin.000001 | 444 | Anonymous_Gtid | 1 | 509 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 509 | Query | 1 | 581 | BEGIN |
| mysql-bin.000001 | 581 | Table_map | 1 | 629 | table_id: 113 (hist.t2) |
| mysql-bin.000001 | 629 | Update_rows | 1 | 845 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000001 | 845 | Xid | 1 | 876 | COMMIT /* xid=17 */ |
| mysql-bin.000001 | 876 | Rotate | 1 | 923 | mysql-bin.000002;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
说明：
- （1）每个binlog文件总是以Format Description Event作为开始，以Rotate Event（Stop Event）作为结束。在开始和结束之间，穿插着其他各种事件。
- （2）TABLE_MAP EVENT：其作用是记录INSERT、DELETE、UPDATE操作的表结构。
- （3）Write_rows：插入记录。
- （4）Update_rows：更新记录。
- （5）Rotate Event：表示日志文件的结束。
binlog 日志的优化
binlog_row_image参数
- 在ROW模式下，即使我们只更新了一条记录的其中某个字段，也会记录每个字段变更前后的值，binlog日志就会变大，带来磁盘IO上的开销，以及网络开销。这个行为可以通过binlog_row_image参数控制，该参数的取值如下：
- FULL（默认值）：记录列的所有修改，即使字段没有发生变更也会记录。
- MINIMAL：只记录修改的列。
- NOBLOB：如果是text类型或clob字段，不记录这些日志。
1、binlog_row_image 参数为 FULL 的 binlog 日志信息
[root@Mysql11 ~]# mysqlbinlog -v /var/lib/mysql/mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
.............
'/*!*/;
### UPDATE `hist`.`stu`
### WHERE
### @1=3
### @2='jack'
### @3=20
### @4='Zhengzhou'
### @5='13675871454'
### @6=1
### SET
### @1=3
### @2='jack'
### @3=21
### @4='Zhengzhou'
### @5='13675871454'
### @6=1
# at 464
#200704 11:04:54 server id 1 end_log_pos 495 CRC32 0xc311dbb6 Xid = 18
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*/;
2、binlog_row_image参数为 MINIMAL 的 binlog日志信息
[root@Mysql11 ~]# mysqlbinlog -v /var/lib/mysql/mysql-bin.000007
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
..............
'/*!*/;
### UPDATE `hist`.`stu`
### WHERE
### @1=1
### SET
### @3=21
# at 394
#200704 11:14:45 server id 1 end_log_pos 425 CRC32 0x54f07b19 Xid = 17
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*/;
3、在Row模式下记录sql语句
-
在Statement模式下，直接记录SQL比较直观。
在Row模式下，使用binlog_rows_query_log_events参数也可以记录SQL。
参数的默认为值为FALSE，如果为true的情况下，会通过Rows Query Event来记录SQL。 -
在my.cnf中添加以下配置，来开启row模式下的SQL记录：
binlog-rows-query-log_events=1
查询配置
> show variables like 'binlog_rows_query_log_events';
Variable_name |Value|
----------------------------+-----+
binlog_rows_query_log_events|OFF |
结果如下：
mysql> show binlog events in '/var/lib/mysql/mysql-bin.000009';
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000009 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000009 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000009 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000009 | 291 | Rows_query | 1 | 354 | # insert into t2(name) values('zhangfei') |
| mysql-bin.000009 | 354 | Table_map | 1 | 402 | table_id: 113 (hist.t2) |
| mysql-bin.000009 | 402 | Write_rows | 1 | 451 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000009 | 451 | Xid | 1 | 482 | COMMIT /* xid=17 */ |
| mysql-bin.000009 | 482 | Anonymous_Gtid | 1 | 547 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000009 | 547 | Query | 1 | 619 | BEGIN |
| mysql-bin.000009 | 619 | Rows_query | 1 | 666 | # update t2 set id=id+100 |
| mysql-bin.000009 | 666 | Table_map | 1 | 714 | table_id: 113 (hist.t2) |
| mysql-bin.000009 | 714 | Update_rows | 1 | 850 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000009 | 850 | Xid | 1 | 881 | COMMIT /* xid=18 */ |
| mysql-bin.000009 | 881 | Anonymous_Gtid | 1 | 946 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000009 | 946 | Query | 1 | 1018 | BEGIN |
| mysql-bin.000009 | 1018 | Rows_query | 1 | 1069 | # delete from t2 where id=201 |
| mysql-bin.000009 | 1069 | Table_map | 1 | 1117 | table_id: 113 (hist.t2) |
| mysql-bin.000009 | 1117 | Delete_rows | 1 | 1157 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000009 | 1157 | Xid | 1 | 1188 | COMMIT /* xid=34 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------+
20 rows in set (0.00 sec)
3 MYSQL Server端：开启BinLog
3.1 操作步骤(Windows系统下: my.ini)
step1 查看MYSQL是否开启 bin log
bin-log的默认配置: 关闭(OFF)
mysql> show variables like 'log_bin'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set, 1 warning (0.10 sec)
step2 若为OFF，则需开启 bin log
mysql-version: 5.7.19
my.ini文件默认不允许修改，需要右键“管理员取得所有权”之后才能保存修改。
- step2.1 配置
bin log
在打开my.ini文件，在mysqld下面添加
# Binary Logging
log-bin=mysql-bin
# 【binlog 日志存放路径】若提供的全路径，则: 生成的日志文件就在指定的路径下；若仅提供的1个文件名称(Eg: mysql-bin)，则：生成的binlog日志的位置在data目录下(eg: D:\Program Files(x86)\MySQL\db-data)
binlog-format=ROW
# 【日志中会记录成每⼀一⾏行行数据被修改的形式】
# Server Id
server-id=1
# 【指定当前机器的服务 ID(如果是集群，则不能重复)】
Binlog常见格式:
- step2.2 重启mysql服务
保存文件，重启mysql服务
cmd(管理员权限)> sc stop mysql
cmd(管理员权限)> sc start mysql
cmd(管理员权限)> sc query mysql
step2.3 确认/查验 是否mysql开启成功了binlog
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\Program Files(x86)\MySQL\db-data\mysql-bin |
| log_bin_index | D:\Program Files(x86)\MySQL\db-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, 1 warning (0.00 sec)
若
log-bin配置项提供的全路径，则: 生成的日志文件就在指定的路径下；若仅提供的1个文件名称(Eg: mysql-bin)，则：生成的binlog日志的位置在data目录下(eg: D:\Program Files(x86)\MySQL\db-data)
3.2 操作步骤(Linux系统下: my.cnf)
配置文件 :
/etc/my.cnf
log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW
server_id=1
4 MySQL Client端：用户授权、权限查验(binlog权限)
- 在后端做MySQL主从备份；亦或是在大数据领域中，各类CDC同步（
Canal/Flink CDC等），均会基于MYSQL的binlog来实现。 - 因此，知道需要哪些权限？怎么去查验、怎么授权就很重要了。
感觉网上的文章没成体系地清楚，而今天工作上处理问题的过程中遇到了此疑问，且曾多次对此产生疑问，自然便有了这一篇，作为个人的Momo备忘笔记~
- 不知道什么是数据领域的
CDC的朋友，可参见我刚参加工作，并进入大数据领域时的这篇科普文章~
4.1 binlog特性的使用，需要用户具备哪些权限？
- 应用场景与权限问题：在主从复制、基于binlog做增量数据同步时，均需要对应的数据库用户具备
binlog权限、相关库表的访问权限等各类权限。
MySQL Binlog权限需要3个权限 :
-
SELECT- 缺乏SELECT权限时，报错为
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'canal'@'%' to database 'binlog'
operation
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'canal'@'%' to database 'binlog'
- 缺乏SELECT权限时，报错为
-
REPLICATION SLAVE- 缺乏REPLICATION SLAVE权限时，报错为
- java.io.IOException: Error When doing Register slave:ErrorPacket [errorNumber=1045, fieldCount=-1, message=Access denied for user 'canal'@'%'
Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系，可以查看从服务器，从主服务器读取二进制日志。grant replication slave on *.* to 'iap'@'%'; show slave hosts; show binlog events;
- 缺乏REPLICATION SLAVE权限时，报错为
-
REPLICATION CLIENT- 缺乏REPLICATION CLIENT权限时，报错为
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this
Replication client权限代表允许执行show master status,show slave status,show binary logs命令
- 缺乏REPLICATION CLIENT权限时，报错为
-
RELOAD权限- 缺乏
RELOAD权限时，报错为Access denied; you need (at least one of) the RELOAD privilege(s) for this operation Error code: 1227; SQLSTATE: 42000.
- 必须拥有reload权限，才可以执行
flush [tables | logs | privileges] - reload 是 administrative 级的权限 (即 server administration )
- 这类权限包括： create user, process, reload, replication client, replication slave, show databases, shutdown, super
- 这类权限的授权不是针对某个数据库的。故，必须使用
on *.*来进行 :grant reload on *.* to 'iap'@'%'
- 缺乏
-
PROCESS权限- 缺乏
PROCESS权限时，报错为Access denied; you need (at least one of) the PROCESS privilege(s) for this operation Error code: 1227; SQLSTATE: 42000.
Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令- 通过这个权限，用户可以执行
SHOW PROCESSLIST和KILL命令。 - 默认情况下，每个用户都可以执行
SHOW PROCESSLIST命令，但是只能查询本用户的进程。
- 缺乏
4.2 权限查验：查验指定用户是否具有指定库/指定表的binlog权限
- Step1 Check binlog status of mysql database server
-- https://github.com/alibaba/canal/wiki/AdminGuide
show variables like 'log_bin';
show variables like 'binlog_format';
- Step2 查验指定用户是否具有指定库/指定表的binlog权限
SHOW GRANTS FOR '{userName}'@'%';
由图可见，这显然说明了该用户对图中这个库有binlog权限。
4.3 授予权限
4.3.1 对目标用户授予BINLOG权限
GRANT
SELECT
, RELOAD, PROCESS
, REPLICATION SLAVE , REPLICATION CLIENT
ON *.* -- 除 SELECT 外，其他权限并不支持 对指定库、指定表进行授权
TO '{username}'@'%' IDENTIFIED BY '{password}'; -- % 可替换为 指定的 host
FLUSH PRIVILEGES;
4.3.2 对目标用户的目标库表授予数据访问权限
GRANT SELECT, INSERT, UPDATE, DELETE ON `ths`.`ths_logConfig` TO 'bdp_cdc'@'%';
-- 或 : GRANT ALL PRIVILEGES ON `ths`.`ths_logConfig` TO 'bdp_cdc'@'%';
FLUSH PRIVILEGES;
4.3.3 核验用户的最终授权情况
-- 查询用户的所有授权情况
SHOW GRANTS FOR 'bdp_cdc'@'%';
5 GTID主从复制的配置步骤
环境说明
| 数据库角色 | IP | 应用与系统版本 |
|---|---|---|
| 主数据库 | 192.168.111.135 | centos8/redhat8 mysql-5.7 |
| 从数据库 | 192.168.111.138 | centos8/redhat8 mysql-5.7 |
主库配置
#在/etc/my.cnf文件中，添加以下配置，重启mysql
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql_bin #开启二进制日志
server-id=1 #设置从库的唯一标识符，主库的server-id值必须小于从库的该值
gtid_mode=on #启动事务ID模式
enforce-gtid-consistency=true #强制GTID一致，不允许事务违反GTID一致性
log-slave-updates=on #告诉从库将主从复制语句也记录在binlog日志，从库需要开启binlog
#重启服务生效
[root@localhost ~]# systemctl restart mysqld
#主库授权复制用户
mysql> grant replication slave on *.* to 'zhao'@'%' identified by 'Passwd123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
从库配置
#在/etc/my.cnf文件中，添加以下配置，重启mysql
[root@localhost ~]# vim /etc/my.cnf
server-id=2
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
#重启服务生效
[root@localhost ~]# systemctl restart mysqld
#从库设置要同步的主库信息，并开启同步
mysql> change master to master_host='192.168.111.135',master_port=3306,master_user='zhao',master_password='Passwd123!',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.111.135
Master_User: zhao
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 437
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 650
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
测试验证
#主数据库
mysql> create database zhao;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhao |
+--------------------+
5 rows in set (0.00 sec)
#从数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhao |
+--------------------+
5 rows in set (0.00 sec)
mysql 多节点数据库架构中，如何确定 各节点是 master 还是 slave 节点?
特别注意，互为 Master 的双主架构中，1台Server，可以既是 Master ，也是 Slave 。
主从同步原理、判断节点类型的原因
- 在 master 机器上的数据操作
- 当收到update、insert、delete指令后会将该指令写入bin-log中
- 当 master 上的数据发生变化时，该事件变化会按照顺序写入bin-log中。
- 当 slave 链接到 master 的时候，
master机器会为 slave 开启 binlog dump 线程。- 当 master 的 binlog 发生变化的时候，bin-log dump 线程会通知slave，并将相应的 binlog 内容发送给slave。
- 在 slave 机器上的数据操作：
- 当主从同步开启的时候，slave上会创建两个线程：I\O线程。
- 该线程连接到 master 机器，master 机器上的
binlog dump线程会将binlog的内容发送给该I\O线程。- 该
I/O线程接收到binlog内容后，再将内容写入到本地的relay log；- sql线程读取到
I/O线程写入的ralay log、并且根据relay log的内容对slave数据库做相应的操作。
- 结论：
- Master 节点：
- 含
bin-log dump线程- 含
bin-log二进制日志
- Slave 节点：
- 含
relay-log中继日志
判断方法
- 方法1：检查配置文件
- 在MySQL的
Master服务器上，配置文件中通常会有log_bin(二进制日志)参数
> show variables like 'log_bin'
Variable_name|Value|
-------------+-----+
log_bin |ON |
> show variables like 'relay_log'
Variable_name|Value |
-------------+-------------------+
relay_log |rds_mysql-relay-bin|
- 方法2：
SHOW SLAVE STATUS或mysql.slave_master_info表
- 在
Slave服务器上，运行SHOW SLAVE STATUS命令可以查看Slave节点的状态，包括与Master的连接信息。
> SHOW SLAVE STATUS
Slave_IO_State|Master_Host |Master_User|Master_Port|Connect_Retry|Master_Log_File|Read_Master_Log_Pos|Relay_Log_File |Relay_Log_Pos|Relay_Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Replicate_Do_DB|Replicate_Ignore_DB|Replicate_Do_Table|Replicate_Ignore_Table|Replicate_Wild_Do_Table|Replicate_Wild_Ignore_Table|Last_Errno|Last_Error|Skip_Counter|Exec_Master_Log_Pos|Relay_Log_Space|Until_Condition|Until_Log_File|Until_Log_Pos|Master_SSL_Allowed|Master_SSL_CA_File|Master_SSL_CA_Path|Master_SSL_Cert|Master_SSL_Cipher|Master_SSL_Key|Seconds_Behind_Master|Master_SSL_Verify_Server_Cert|Last_IO_Errno|Last_IO_Error|Last_SQL_Errno|Last_SQL_Error|Replicate_Ignore_Server_Ids|Master_Server_Id|Master_UUID|Master_Info_File |SQL_Delay|SQL_Remaining_Delay|Slave_SQL_Running_State|Master_Retry_Count|Master_Bind|Last_IO_Error_Timestamp|Last_SQL_Error_Timestamp|Master_SSL_Crl|Master_SSL_Crlpath|Retrieved_Gtid_Set|Executed_Gtid_Set |Auto_Position|Replicate_Rewrite_DB|Channel_Name|Master_TLS_Version|
--------------+-------------+-----------+-----------+-------------+---------------+-------------------+--------------------------+-------------+---------------------+----------------+-----------------+---------------+-------------------+------------------+----------------------+-----------------------+---------------------------+----------+----------+------------+-------------------+---------------+---------------+--------------+-------------+------------------+------------------+------------------+---------------+-----------------+--------------+---------------------+-----------------------------+-------------+-------------+--------------+--------------+---------------------------+----------------+-----------+-----------------------+---------+-------------------+-----------------------+------------------+-----------+-----------------------+------------------------+--------------+------------------+------------------+-----------------------------------------------+-------------+--------------------+------------+------------------+
|192.168.70.84|rdsRepl | 3306| 60| | 4|rds_mysql-relay-bin.000001| 4| |No |No | | | | | | | 0| | 0| 0| 413391|None | | 0|No | | | | | | |No | 0| | 0| | | 0| |mysql.slave_master_info| 0| | | 86400| | | | | | |f0a5a37b-4a03-11ed-89fd-fa163e42cb44:1-36902629| 1| | | |
> -- master.info 文件 或者 mysql.slave_master_info 表：IO线程信息日志，用于保存【从库】IO线程连接【主库】的连接状态、帐号、IP、端口、密码以及IO线程当前读取主库binlog的file和position等信息。
> -- 默认保存在 master.info 文件中，如果需要保存在 mysql.slave_master_info 表，需要在启动前设置 master-info-repository = TABLE
> @reference-doc : https://blog.csdn.net/Hehuyi_In/article/details/105329434
> select * from mysql.slave_master_info
Number_of_lines|Master_log_name|Master_log_pos|Host |User_name|User_password |Port|Connect_retry|Enabled_ssl|Ssl_ca|Ssl_capath|Ssl_cert|Ssl_cipher|Ssl_key|Ssl_verify_server_cert|Heartbeat|Bind|Ignored_server_ids|Uuid|Retry_count|Ssl_crl|Ssl_crlpath|Enabled_auto_position|Channel_name|Tls_version|
---------------+---------------+--------------+-------------+---------+------------------+----+-------------+-----------+------+----------+--------+----------+-------+----------------------+---------+----+------------------+----+-----------+-------+-----------+---------------------+------------+-----------+
25| | 4|192.168.70.84|rdsRepl |_QS8xweferPv26perg|3306| 60| 0| | | | | | 0| 30.0| |0 | | 86400| | | 1| | |
- 方法3：
- 使用
SHOW PROCESSLIST命令，在MySQL命令行中运行此命令，如果服务器是Slave，则会显示与Master的连接进程。
> SHOW PROCESSLIST
Id |User |Host |db |Command |Time |State |Info
--------+--------------------+--------------------+-------------------------+----------------+--------+-------------------------------------------------------------+---------------------------
23539234|event_scheduler |localhost | |Daemon | 2619|Waiting for next activation |
43644314|root |192.168.19.229:46690 |xxx_keycloak |Sleep | 321| |
47212322|root |192.168.19.185:33558 |xxx_scm |Sleep | 23| |
47212323|root |192.168.19.185:33560 |xxx_scm |Sleep | 2| |
48604313|root |192.16.213.15:36598 | |Binlog Dump GTID|22128448|Master has sent all binlog to slave; waiting for more updates|
> -- select * from sys.PROCESSLIST
> select * from information_schema.processlist
ID |USER |HOST |DB |COMMAND |TIME |STATE |INFO |TRX_EXECUTED_TIME|
--------+--------------------+--------------------+-------------------------+----------------+--------+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+-----------------+
72486098|root |192.168.19.95:55134 |xxx_gateway_server |Sleep | 1355| | | 0|
72486205|xxx_real_time |192.168.19.11:64031 |xxx_real_time |Sleep | 1259| | | 0|
72486455|xxx_real_time |192.168.19.11:47415 |xxx_real_time |Sleep | 1032| | | 0|
72484062|root |192.168.19.185:57958 |xxx_amon |Sleep | 98| | | 0|
| 0|
60617612|root |192.16.228.37:45400 | |Binlog Dump GTID|11062655|Master has sent all binlog to slave; waiting for more updates| | 0|
60772653|xxx_keycloak |192.168.19.238:64823 |xxx_keycloak |Sleep | 1| | | 0|
72486460|dev |192.168.19.207:14709 |xxx_sys |Sleep | 4| | | 0|
60613344|root |192.16.213.15:45658 |xxx_bd |Sleep | 0| | | 0|
72471794|rdsAdmin |localhost | |Sleep | 31| | | 0|
72487405|xxx_scheduler |192.168.19.11:14622 |xxx_scheduler |Sleep | 108| | | 0|
60609699|xxx_cdc |192.16.243.149:58836| |Binlog Dump GTID|11069908|Master has sent all binlog to slave; waiting for more updates| | 0|
60609700|xxx_cdc |192.16.243.149:58838| |Binlog Dump GTID|11069908|Master has sent all binlog to slave; waiting for more updates| | 0|
60617643|root |192.16.195.90:51204 |xxx_bd |Sleep | 0| | | 0|
...
Y Flink CDC FAQ
参见：
X 参考与推荐文献
- [大数据]ETL之增量数据抽取(CDC) - 博客园/千千寰宇 【推荐】
- [数据库]MySQL之数据库管理篇 - 博客园/千千寰宇 【推荐】
- [数据库]MySQL之SQL查询 - 博客园/千千寰宇
- MySQL Binlog 权限 - 博客园
- Mysql的Binlog原理 - CSDN
- MySQL Binlog 介绍 - CSDN
- WINDOWS下MYSQL开启BINLOG - 博客园
- Windows下Mysql5.7开启binlog步骤及注意事项 - 博客园
- [Windows]CMD命令入门教程 与 Windows常见维护问题 - 博客园/千千寰宇
- MySQL之GTID主从复制 - 腾讯云 【参考/推荐】
- MySQL学习笔记：基于GTID的主从复制 - CSDN 【推荐/参考】
- 三主mysql怎么确定那个为主 mysql主从是什么意思 - 51CTO
- MySQL主从配置 - Aliyun
本文作者：
千千寰宇
本文链接： https://www.cnblogs.com/johnnyzen
关于博文：评论和私信会在第一时间回复，或直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
日常交流：大数据与软件开发-QQ交流群: 774386015 【入群二维码】参见左下角。您的支持、鼓励是博主技术写作的重要动力！
本文链接： https://www.cnblogs.com/johnnyzen
关于博文：评论和私信会在第一时间回复，或直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
日常交流：大数据与软件开发-QQ交流群: 774386015 【入群二维码】参见左下角。您的支持、鼓励是博主技术写作的重要动力！
