浅谈MySQL Binlog

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 浅谈MySQL Binlog

1 什么是binlog?

binlog是二进制日志文件,用于记录mysql的数据更新或者潜在更新状况,在mysql主从复制中就是依靠的binlog。可以通过语句“show binlog events in ‘binlogfile’”来查看binlog的具体事件类型。binlog记录的所有操作实际上都有对应的事件类型的,MySQL binlog的三种工作模式:

  • Row level(行记录)简介:
  • 日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
  • 优点:
  • 所有更改都可以复制。这是最安全的复制形式。
  • 对于以下类型的语句,源上需要更少的行锁,从而实现更高的并发性:
  • INSERT ... SELECT
  • INSERTAUTO_INCREMENT
  • UPDATE``DELETE带有WHERE不使用键或不更改大多数检查行的子句的语句 。
  • 需要的行锁更少 。
  • 缺点:
  • RBR 可以生成更多必须记录的数据。写入数据可能会导致并发问题。用
  • 无法在副本上看到从源接收并执行了哪些语句。
  • 对于使用MyISAM 存储引擎的表,INSERT当将它们作为基于行的事件应用于二进制日志时,语句的副本需要比将它们作为语句应用时更强的锁。这意味着MyISAM在使用基于行的复制时不支持对表的并发插入。
  • Statement level(语句记录,默认)简介:
  • 每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。在主从同步中一般是不建议用statement模式的,因为会有些语句不支持,比如语句中包含UUID函数,以及LOAD DATA IN FILE语句等
  • 优点:
  • 写入日志文件的数据更少。当更新或删除影响许多行时,这会导致 日志文件所需的存储空间少得多。这也意味着可以更快地完成备份和恢复。
  • 日志文件包含进行了任何更改的所有语句,因此它们可用于审计数据库。
  • 缺点:
  • INSERT ... SELECT与基于行的复制相比,需要更多的行级锁。
  • UPDATE需要表扫描的语句,必须比基于行的复制锁定更多的行。
  • 对于InnoDBINSERT使用 AUTO_INCREMENT阻塞其他非冲突INSERT语句的语句。
  • 对于复杂语句,必须在更新或插入行之前在副本上评估和执行语句。使用基于行的复制,副本只需要修改受影响的行,而不是执行完整的语句。
  • 如果对副本进行评估时出现错误,尤其是在执行复杂语句时,则基于语句的复制可能会随着时间的推移慢慢增加受影响行的误差幅度。
  • 存储函数以与NOW()调用语句相同的值执行 。但是,这不适用于存储过程。
  • 必须在副本上应用确定性可加载函数。
  • 表定义在源和副本上必须(几乎)相同。
  • Mixed(混合模式)简介:
  • 结合了Row level和Statement level的优点,同时binlog结构也更复杂。

binlog的格式:

2 binlog有什么作用?

  • MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
  • 数据恢复:通过使用 mysqlbinlog工具来使恢复数据

3 binlog的常用操作

3.1 启动和关闭binlog

启动binlog,修改配置文件并重启:

[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# vim /etc/my.cnf
############追加以下内容#############
[mysqld]
#开启binlog,提前建好/home/mysql/binlog文件名称
log-bin=/home/mysql/binlog
############追加内容完成#############
[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# service mysql restart

关闭binlog:

删除配置文件中相应的内容

查看binlog是否开启:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
3.2 查看binlog

先在mysql中看binlog 文件的名称

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       412 |
+---------------+-----------+
1 row in set (0.00 sec)

根据binlog名称查看binlog内容

[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# mysqlbinlog binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211122 15:36:30 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.62-log created 211122 15:36:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
fkibYQ8BAAAAZwAAAGsAAAABAAQANS41LjYyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB+SJthEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#211122 15:38:14 server id 1  end_log_pos 220   Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1637566694/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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/*!*/;
create table student(id int(10),name varchar(20) )
/*!*/;
# at 220
#211122 15:38:40 server id 1  end_log_pos 288   Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1637566720/*!*/;
BEGIN
/*!*/;
# at 288
#211122 15:38:40 server id 1  end_log_pos 385   Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1637566720/*!*/;
insert into student values(1,"zs")
/*!*/;
# at 385
#211122 15:38:40 server id 1  end_log_pos 412   Xid = 12
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面输出包括信息:

  • position: 位于文件中的位置,即第一行的,说明该事件记录从文件第21019个字节开始
  • timestamp: 事件发生的时间戳。
  • server id: 服务器标识。
  • end_log_pos 表示下一个事件开始的位置(即当前事件的结束位置+1)
  • thread_id: 执行该事件的线程id 。
  • exec_time: 事件执行的花费时间
  • error_code: 错误码,0意味着没有发生错误
  • type:事件类型Query
3.3 binlog常用命令
mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status; #查看当前正在写入的binlog文件
3.4 切换binlog的模式
mysql> show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
 
mysql> SET global binlog_format = 'ROW';
Query OK, 0 rows affected (0.02 sec)
 
mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

参考文章:

https://blog.csdn.net/ljb825802164/article/details/105098174

https://www.jianshu.com/p/ea666baf0d82

https://blog.csdn.net/wwwdc1012/article/details/88373440

https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html

https://www.cnblogs.com/liujiacai/p/7669058.html

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
存储 安全 关系型数据库
Mysql 的binlog日志的优缺点
MySQL的binlog(二进制日志)是一个记录数据库更改的日志文件,它包含了所有对数据库执行的更改操作,如INSERT、UPDATE和DELETE等。binlog的主要目的是复制和恢复。以下是binlog日志的优缺点: ### 优点: 1. **数据恢复**:当数据库出现意外故障或数据丢失时,可以利用binlog进行点恢复(point-in-time recovery),将数据恢复到某一特定时间点。 2. **主从复制**:binlog是实现MySQL主从复制功能的核心组件。主服务器将binlog中的事件发送到从服务器,从服务器再重放这些事件,从而实现数据的同步。 3. **审计**:b
|
10天前
|
SQL 关系型数据库 MySQL
Mysql 的binlog日志的原理【4月更文挑战第1天】
【4月更文挑战第1天】 MySQL的binlog(二进制日志)是一个记录数据库更改的日志文件,它主要用于复制和恢复操作。以下是binlog日志的工作原理的简要概述: **事件写入**:当MySQL服务器执行一个事务时,它会将该事务中所有对数据库的修改操作(如INSERT、UPDATE和DELETE等)记录为一个事件(event)。这些事件包含了修改操作的相关信息,如操作类型、涉及的表、修改的行等。
100 1
|
1天前
|
存储 关系型数据库 MySQL
实时计算 Flink版产品使用合集之如何配置可以实现实时同步多张MySQL源表时只读取一次binlog
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
SQL 资源调度 关系型数据库
实时计算 Flink版产品使用合集之在抓取 MySQL binlog 数据时,datetime 字段会被自动转换为时间戳形式如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
17 2
|
7天前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用合集之在同步MySQL的时候卡在某个binlog文件处如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
24 1
实时计算 Flink版产品使用合集之在同步MySQL的时候卡在某个binlog文件处如何解决
|
8天前
|
Prometheus Cloud Native 关系型数据库
实时计算 Flink版产品使用合集之binlog被清理掉的问题,并且binlog有备份,有什么方法来恢复到RDS
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
23 2
|
8天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之如果MySQL的binlog保存时间只有三天,那么三天之前的数据是不是会通过Doris的外表手动插入
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
16 0
|
10天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之在DataWorks中,如何通过PolarDB for MySQL来查看binlog日志
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
35 1
|
10天前
|
SQL 关系型数据库 MySQL
mysql的binlog恢复数据
mysql的binlog恢复数据
35 0
|
10天前
|
缓存 关系型数据库 MySQL
MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响
MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响
58 0

推荐镜像

更多