[数据库] 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*），用于记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

什么是事务日志？(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)

背景导入

情景引入

Command = Binlog Dump GTID

GTID的诞生背景
GTID出现之前，在一主多从的复制拓扑中，如果主库宕机，需要从多个从库选择之一作为新主库，这个过程比较复杂。没有一种直接了当的方法找到其它从库对应的新主库二进制日志坐标。通常的做法是先要寻找每个从库复制原主库的最后语句，然后找到新主库中包含该语句的二进制日志文件，其中该语句后的第一个事件位置即为连接新主库的二进制坐标。主要难点在于不存在一个唯一标识指出“复制原主库的最后语句”，于是后来的MySQL中就出现了GTID的概念。
优点

替代传统的binlog+pos复制；使用master_auto_position=1自动匹配GTID断点进行复制

GTID 概念

GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。

官网：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-lifecycle.html

GTID的组成部分

前面是server_uuid：后面是一个序列号
UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。
Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

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相关参数

参数	comment
gtid_executed	执行过的所有GTID
gtid_purged	丢弃掉的GTID
gtid_mode	GTID模式
gtid_next	session级别的变量，下一个gtid
gtid_owned	正在运行的GTID
enforce_gtid_consistency	保证GTID安全的参数

2 MYSQL Server端：开启BinLog

2.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)

2.2 操作步骤(Linux系统下: `my.cnf`)

配置文件 : /etc/my.cnf

log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW 

server_id=1

3 MySQL Client端：用户授权、权限查验(binlog权限)

在后端做MySQL主从备份；亦或是在大数据领域中，各类CDC同步（Canal / Flink CDC等），均会基于MYSQL的binlog来实现。
因此，知道需要哪些权限？怎么去查验、怎么授权就很重要了。

感觉网上的文章没成体系地清楚，而今天工作上处理问题的过程中遇到了此疑问，且曾多次对此产生疑问，自然便有了这一篇，作为个人的Momo备忘笔记~

不知道什么是数据领域的CDC的朋友，可参见我刚参加工作，并进入大数据领域时的这篇科普文章~

[大数据]ETL之增量数据抽取(CDC) - 博客园/千千寰宇

3.1 binlog特性的使用，需要用户具备哪些权限？

应用场景与权限问题：在主从复制、基于binlog做增量数据同步时，均需要对应的数据库用户具备binlog权限、相关库表的访问权限等各类权限。

MySQL Binlog权限需要3个权限 :

SELECT
- 缺乏SELECT权限时，报错为
  - com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'canal'@'%' to database 'binlog'
- 缺乏REPLICATION SLAVE权限时，报错为
  - java.io.IOException: Error When doing Register slave:ErrorPacket [errorNumber=1045, fieldCount=-1, message=Access denied for user 'canal'@'%'
- 缺乏REPLICATION CLIENT权限时，报错为
  - com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
REPLICATION SLAVE
REPLICATION CLIENT

Binlog为什么需要这些权限？

Select 权限代表允许从表中查看数据
Replication client 权限代表允许执行show master status,show slave status,show binary logs命令
Replication slave 权限代表允许slave主机通过此用户连接master以便建立主从复制关系

3.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权限。

3.3 授予权限

GRANT 
    SELECT
    , REPLICATION SLAVE
    , REPLICATION CLIENT 
ON *.*
TO '{username}'@'%' IDENTIFIED BY '{password}'; -- % 可替换为 指定的 host

4 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)

X 参考与推荐文献

posted @ 2023-04-28 18:52 千千寰宇阅读(1200) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库] MYSQL之binlog概述

1 概述: MYSQL数据库的二进制日志：bin log

MYSQL数据库的日志种类

什么是二进制日志(binlog)?

什么是事务日志？(redo log/undo log)

写 Binlog 的时机？ `sync_binlog`

【二进制日志处理事务】和【非事务性语句】的区别?

Binlog日志的应用场景

Binlog日志的代价

GTID(全局事务ID)

背景导入

GTID 概念

GTID的组成部分

GTID比传统复制的优势

GTID与Binlog的关系

GTID的工作原理

GTID相关参数

2 MYSQL Server端：开启BinLog

2.1 操作步骤(Windows系统下: `my.ini`)

step1 查看MYSQL是否开启 `bin log`

step2 若为OFF，则需开启 `bin log`

step2.3 确认/查验是否mysql开启成功了binlog

2.2 操作步骤(Linux系统下: `my.cnf`)

3 MySQL Client端：用户授权、权限查验(binlog权限)

3.1 binlog特性的使用，需要用户具备哪些权限？

3.2 权限查验：查验指定用户是否具有指定库/指定表的binlog权限

3.3 授予权限

4 GTID主从复制的配置步骤

环境说明

主库配置

从库配置

测试验证

X 参考与推荐文献

公告

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库] MYSQL之binlog概述

1 概述: MYSQL数据库的二进制日志：bin log

MYSQL数据库的日志种类

什么是二进制日志(binlog)?

什么是事务日志？(redo log/undo log)

写 Binlog 的时机？ sync_binlog

【二进制日志处理事务】和【非事务性语句】的区别?

Binlog日志的应用场景

Binlog日志的代价

GTID(全局事务ID)

背景导入

GTID 概念

GTID的组成部分

GTID比传统复制的优势

GTID与Binlog的关系

GTID的工作原理

GTID相关参数

2 MYSQL Server端：开启BinLog

2.1 操作步骤(Windows系统下: my.ini)

step1 查看MYSQL是否开启 bin log

step2 若为OFF，则需开启 bin log

step2.3 确认/查验 是否mysql开启成功了binlog

2.2 操作步骤(Linux系统下: my.cnf)

3 MySQL Client端：用户授权、权限查验(binlog权限)

3.1 binlog特性的使用，需要用户具备哪些权限？

3.2 权限查验：查验指定用户是否具有指定库/指定表的binlog权限

3.3 授予权限

4 GTID主从复制的配置步骤

环境说明

主库配置

从库配置

测试验证

X 参考与推荐文献

公告

写 Binlog 的时机？ `sync_binlog`

2.1 操作步骤(Windows系统下: `my.ini`)

step1 查看MYSQL是否开启 `bin log`

step2 若为OFF，则需开启 `bin log`

step2.3 确认/查验是否mysql开启成功了binlog

2.2 操作步骤(Linux系统下: `my.cnf`)