AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

【Mysql实战】问题分析利器之binlog

2023-09-02 666
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介： 在【Mysql-InnoDB 系列】事务提交过程及系列文章中，对mysql（InnoDB引擎）的redolog、undolog、binlog及事务的提交过程有了一些介绍，本篇将尝试去实践binlog在日常操作中的查看、分析方式，以及可能遇到的问题和解决方法。

一 摘要

   在【Mysql-InnoDB 系列】事务提交过程及系列文章中，对mysql（InnoDB引擎）的redolog、undolog、binlog及事务的提交过程有了一些介绍，本篇将尝试去实践binlog在日常操作中的查看、分析方式，以及可能遇到的问题和解决方法。

二 binlog的位置

2.1 寻找方式

2.1.1 文件遍历

   直接简单粗暴地根据命名查找，因为binlog文件通常都是mysql-bin.000001这样的明明方式，所以sudo find / -name mysql-bin.000001即可。但效率低下，所以一般不会这样查找。

2.1.2 配置文件

   可以通过配置文件查看，Linux/Mac系统中通常是/etc/my.cnf。在实际查找时，也可以查看配置文件使用的优先级，mysql提供了相关的命令：mysqld --verbose --help | grep -A 1 'Default options'

在本地执行后的结果如下：

xxx$ mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

可见，mysql加载配置文件的优先级是：

1、/etc/my.cnf

2、/etc/mysql/my.cnf

3、/usr/local/etc/my.cnf

4、~/.my.cnf

本地配置了/etc/my.cnf，所以直接vi /etc/my.cnf查看即可。内容如下：

标红的部分就是bin-log的配置位置。

此时目录下的文件列表：

2.2 客户端常用命令

2.2.1 查看开启状态及基本配置

命令： show variables like 'log_%';

可以查看binlog的相关信息（也可以通过这种方式查看binlog位置），log_bin的value，ON表示开启，否则关闭：

2.2.2 查看所有binlog日志列表

命令：show master logs;

2.2.3 查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点

命令：show master status;

2.2.4 清空binlog日志

命令：reset master;

三 查看方法

3.1 直接查看内容

cat mysql-bin.000022 查看文件内容：

有很多乱码，同时也能大概看出一部分以往的操作，例如两个建表语句。

3.2 正确方式

3.2.1 mysqlbinlog

   mysql本身提供了binlog的查看工具： mysqlbinlog，与mysqld、mysql同位于mysql的bin目录下。使用方式为： mysqlbinlog 【binlog文件路径】。例如在我们刚才查到的binlog目录下，mysqlbinlog mysql-bin.000022：

很遗憾，出了点小问题，抛了unknown variable 'default-character-set=utf8mb4'的错误。到本文上面翻一下my.cnf，可以看到其中配置了默认字符集，而mysqlbinlog表示无法识别。

3.2.2 解决方法

Mysql操作binlog时报错这篇文章中提到过两种方法，第一种是修改配置，把default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但这种方式需要修改my.cnf并重启mysql服务。生产环境显然不适合。而且本地版本（5.7.28）如此操作后，发现也并没有解决问题，所以跳过。

方法二：

mysqlbinlog --no-defaults mysql-bin.000022

经验证可以生效，查询结果如下：

与图6直接查看文本的结果对比，可以看到不再有乱码，我们可以据此分析mysql操作过程中产生的binlog，并进而分析线上问题。

四 一个真实的问题分析案例

4.1 案例操作重现

案例篇 | 记一次 MySQL 主从双写导致的数据丢失问题 这篇文章中提到了两台互为主从的机器上都进行了写入导致数据丢失的排查记录，使用的主要方法就是分析binlog（Row格式下的RelayLog重放）。

我们尝试在本地复现其中章节： 二、Row 格式下 RelayLog 的重放 的内容，在本地分析update语句带来的binlog变更：

mysqlbinlog --no-defaults mysql-bin.000025查看，binlog的内容如下：

# at 291
#210323 12:05:11 server id 1  end_log_pos 346 CRC32 0xa9ba861a  Table_map: `test`.`t20200709` mapped to number 108
# at 346
#210323 12:05:11 server id 1  end_log_pos 402 CRC32 0x32082f49  Update_rows: table id 108 flags: STMT_END_F
BINLOG '
92hZYBMBAAAANwAAAFoBAAAAAGwAAAAAAAEABHRlc3QACXQyMDIwMDcwOQACAw8CkAEAGoa6qQ==
92hZYB8BAAAAOAAAAJIBAAAAAGwAAAAAAAEAAgAC///8AQAAAAUAbmFtZTT8AQAAAAEAMUkvCDI=
'/*!*/;
# at 402
#210323 12:05:11 server id 1  end_log_pos 433 CRC32 0x03c4c109  Xid = 9
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下出现了类似base64编码后的结果，而不是Update语句，是否是日志格式的问题？查看binlog格式配置，使用命令: show variables like '%binlog%';

日志格式确实是ROW，有讨论说在MIX格式下会出现乱码，那么我们的这个就不是binlog_format的问题了，继续尝试其他方法。在mysqlbinlog解析binlog乱码问题解密 中找到了一种方法，通过--base64-output=DECODE-ROWS -v 参数来格式化输出binlog中的Base64部分，输出如下（因为后续有操作，又生成了一个新的binlog文件）：

bogon:logs qingclass$ mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000026
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210323 12:17:23 server id 1  end_log_pos 123 CRC32 0x4d34b223  Start: binlog v 4, server v 5.7.28-log created 210323 12:17:23 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#210323 12:17:23 server id 1  end_log_pos 154 CRC32 0xffc92a6d  Previous-GTIDs
# [empty]
# at 154
#210323 12:17:53 server id 1  end_log_pos 219 CRC32 0x9fc36419  Anonymous_GTID  last_committed=0  sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210323 12:17:53 server id 1  end_log_pos 291 CRC32 0x4fc28ff1  Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1616473073/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#210323 12:17:53 server id 1  end_log_pos 346 CRC32 0x2f8d89e1  Table_map: `test`.`t20200709` mapped to number 108
# at 346
#210323 12:17:53 server id 1  end_log_pos 398 CRC32 0xca0f72bd  Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`t20200709`
### WHERE
###   @1=1
###   @2='5'
### SET
###   @1=1
###   @2='4'
# at 398
#210323 12:17:53 server id 1  end_log_pos 429 CRC32 0x5ce7c021  Xid = 2
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*/;

在第32行，终于看到了预期的结果：

4.2 binlog格式问题分析

Mysql官方文档中有binlog格式的详细描述：5.4.4.1 Binary Logging Formats，包括:STATEMENT，ROW，MIXED三种格式。

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from source to replica. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
  • In row-based logging, the source writes events to the binary log that indicate how individual table rows are affected. It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
  • A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

翻译如下：

1、MySQL中的复制功能最初是基于SQL语句从源到副本的传播。这称为基于语句的日志记录。可以通过mysqld命令，指定--binlog-format=STATEMENT参数来启动服务；

2、在基于行的日志记录中，源将事件写入二进制日志，以指示单个表行如何受到影响。因此，表始终使用主键来确保可以有效地标识行是很重要的；可以通过mysqld命令，指定--binlog-format=ROW参数来启动服务；

3、还有第三种选择：混合日志记录。对于混合日志记录，默认情况下使用基于语句的日志记录，但在某些情况下，日志记录模式会自动切换到基于行的日志记录。可以通过mysqld命令，指定--binlog-format=MIXED参数来启动服务。

不过需要注意，STATEMENT格式在生产环境应该很少使用，因为类似于： 

INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER());

这里的一些函数最好用row模式，因为主从复制的时候，uuid已经now()等会造成时间延迟，故而为了数据一致性，STATEMENT格式不是最佳选择。

5.4.4.2 Setting The Binary Log Format也指出，可以通过下面语句在全局中设置binlog_format：

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

也可以仅在当前会话中设置：

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

五 总结

   本文由一个看到的真实案例分享开始，引起对binlog的学习和基础操作。在复现操作的过程中，遇到并解决了一些问题。希望可以对大家有一定的参考作用。

文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
SQL
Linux
关键词：
mysql binlog
云数据库 RDS MySQL 版binlog
云数据库 RDS MySQL 版实战
云数据库 RDS MySQL 版分析
binlog mysql
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
架构星空
目录
相关文章
大数据文摘
|
1天前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志（Redo Log）和二进制日志（Binary Log）是两种重要的日志系统。重做日志主要用于保证事务的持久性和原子性，通过记录数据页的物理修改信息来恢复未提交的事务更改。二进制日志则记录了数据库的所有逻辑变化操作，用于数据的复制、恢复和审计。两者在写入时机、存储方式、配置参数和使用范围上有所不同，共同确保了数据库的稳定性和可靠性。
大数据文摘
11 2
潇洒洒
|
12天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时，传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率，我们可以结合使用MySQL和Redis，利用两者的优势来优化数据查询。本文将分享一次实战经验，探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
潇洒洒
33 5
绿水长流*z
|
29天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1607 14
小王老师呀
|
16天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
40 4
喜欢猪猪
|
22天前
|
架构师 关系型数据库 MySQL
MySQL最左前缀优化原则：深入解析与实战应用
【10月更文挑战第12天】在数据库架构设计与优化中，索引的使用是提升查询性能的关键手段之一。其中，MySQL的最左前缀优化原则（Leftmost Prefix Principle）是复合索引（Composite Index）应用中的核心策略。作为资深架构师，深入理解并掌握这一原则，对于平衡数据库性能与维护成本至关重要。本文将详细解读最左前缀优化原则的功能特点、业务场景、优缺点、底层原理，并通过Java示例展示其实现方式。
喜欢猪猪
50 1
土木林森
|
6天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
38 0
土木林森
|
7天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
34 0
糜终
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
77 4
baker_zhuang
|
1月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
148 0
古明地盆
|
2月前
|
消息中间件 canal 关系型数据库
Maxwell：binlog 解析器，轻松同步 MySQL 数据
Maxwell：binlog 解析器，轻松同步 MySQL 数据
古明地盆
244 11
数据库

关系型数据库

热门文章

最新文章

  • 1
    PostgreSQL 如何实现批量更新、删除、插入
  • 2
    PostgreSQL 9种索引的原理和应用场景
  • 3
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 4
    PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)
  • 5
    page fault带来的性能问题
  • 6
    PostgreSQL 金融行业高可用和容灾解决方案
  • 7
    MongoDB中使用的SCRAM-SHA1认证机制
  • 8
    从MySQL导出到PostgreSQL
  • 9
    MySQL · 性能优化 · MySQL常见SQL错误用法
  • 10
    数据库案例集锦 - 开发者的《如来神掌》
  • 1
    Linux-安装Mariadb
    9
  • 2
    【项目场景】请求数据时测试环境比生产环境多花了1秒是怎么回事？
    13
  • 3
    Mysql高可用架构方案
    35
  • 4
    SQL Server 数据太多如何优化
    43
  • 5
    springboot整合常用中间件框架案例
    52
  • 6
    数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
    27
  • 7
    MySQL 回收表碎片实践教程
    55
  • 8
    浅析MySQL优化器统计信息
    81
  • 9
    Mysql(4)—数据库索引
    56
  • 10
    Mysql(5)—函数
    67

    • 相关产品

  • 云数据库 RDS MySQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 自建MySQL 5.7数据库备份上云
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • ECS服务器部署MySQL数据库

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks智能数据建模全面公测开始啦！