备案控制台
探索云世界
开发者社区 数据库 关系型数据库 文章 正文

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

2023-08-31 244
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： 在【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这样的命名方式，所以sudofind/-namemysql-bin.000001即可。但效率低下，所以一般不会这样查找。

2.1.2配置文件

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

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

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查看即可。内容如下：

图1my.cnf

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

此时目录下的文件列表：

图2binlog目录下文件列表

2.2客户端常用命令

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

命令：showvariableslike'log_%';

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

图3客户端SQL查询binlog配置

2.2.2查看所有binlog日志列表

命令：showmasterlogs;

图4查看binlog列表命令

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

命令：showmasterstatus;

图5查看binlog最后一个编号命令

2.2.4清空binlog日志

命令：resetmaster;


三查看方法

3.1直接查看内容

catmysql-bin.000022查看文件内容：

图6直接查看binlog文本

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

3.2正确方式

3.2.1mysqlbinlog

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

图7mysqlbinlog报错信息

很遗憾，出了点小问题，抛了unknownvariable'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-defaultsmysql-bin.000022

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

图8mysqlbinlog查询结果

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

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

4.1案例操作重现


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

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

mysqlbinlog--no-defaultsmysql-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_FBINLOG '92hZYBMBAAAANwAAAFoBAAAAAGwAAAAAAAEABHRlc3QACXQyMDIwMDcwOQACAw8CkAEAGoa6qQ==92hZYB8BAAAAOAAAAJIBAAAAAGwAAAAAAAEAAgAC///8AQAAAAUAbmFtZTT8AQAAAAEAMUkvCDI='/*!*/;# at 402#210323 12:05:11 server id 1  end_log_pos 433 CRC32 0x03c4c109   Xid = 9COMMIT/*!*/;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编码后的结果，而不是Udate语句，是否是日志格式的问题？查看binlog格式配置，使用命令:showvariableslike'%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=0SET 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 = 2COMMIT/*!*/;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.2binlog格式问题分析

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

ReplicationcapabilitiesinMySQLoriginallywerebasedonpropagationofSQLstatementsfromsourcetoreplica.Thisiscalledstatement-basedlogging.Youcancausethisformattobeusedbystartingtheserverwith--binlog-format=STATEMENT.Inrow-basedlogging,thesourcewriteseventstothebinarylogthatindicatehowindividualtablerowsareaffected.Itisimportantthereforethattablesalwaysuseaprimarykeytoensurerowscanbeefficientlyidentified.Youcancausetheservertouserow-basedloggingbystartingitwith--binlog-format=ROW.Athirdoptionisalsoavailable:mixedlogging.Withmixedlogging,statement-basedloggingisusedbydefault,buttheloggingmodeswitchesautomaticallytorow-basedincertaincasesasdescribedbelow.YoucancauseMySQLtousemixedloggingexplicitlybystartingmysqldwiththeoption--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.2SettingTheBinaryLogFormat也指出，可以通过下面语句在全局中设置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
关键词：
binlog mysql
mysql binlog
云数据库 RDS MySQL 版实战
云数据库 RDS MySQL 版binlog
云数据库 RDS MySQL 版分析
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
架构星空
目录
相关文章
jianz123
|
21天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
jianz123
16 0
liuyunshengsir
|
23天前
|
存储 安全 关系型数据库
Mysql 的binlog日志的优缺点
MySQL的binlog（二进制日志）是一个记录数据库更改的日志文件，它包含了所有对数据库执行的更改操作，如INSERT、UPDATE和DELETE等。binlog的主要目的是复制和恢复。以下是binlog日志的优缺点： ### 优点： 1. **数据恢复**：当数据库出现意外故障或数据丢失时，可以利用binlog进行点恢复（point-in-time recovery），将数据恢复到某一特定时间点。 2. **主从复制**：binlog是实现MySQL主从复制功能的核心组件。主服务器将binlog中的事件发送到从服务器，从服务器再重放这些事件，从而实现数据的同步。 3. **审计**：b
liuyunshengsir
31 1
技术蜜糖罐
|
27天前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
技术蜜糖罐
29 0
技术蜜糖罐
|
27天前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL：深入理解MySQL聚合函数，实战进销存项目中的应用与技巧（8）
轻松入门MySQL：深入理解MySQL聚合函数，实战进销存项目中的应用与技巧（8）
技术蜜糖罐
12 0
liuyunshengsir
|
23天前
|
SQL 关系型数据库 MySQL
Mysql 的binlog日志的原理【4月更文挑战第1天】
【4月更文挑战第1天】 MySQL的binlog（二进制日志）是一个记录数据库更改的日志文件，它主要用于复制和恢复操作。以下是binlog日志的工作原理的简要概述： **事件写入**：当MySQL服务器执行一个事务时，它会将该事务中所有对数据库的修改操作（如INSERT、UPDATE和DELETE等）记录为一个事件（event）。这些事件包含了修改操作的相关信息，如操作类型、涉及的表、修改的行等。
liuyunshengsir
89 1
jianz123
|
21天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
jianz123
87 1
技术蜜糖罐
|
27天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
37 0
龙大吉
|
3天前
|
存储 关系型数据库 MySQL
MySQL数据库实战：从入门到精通
本文介绍了MySQL的使用和优化，适合Web开发者阅读。首先，确保安装并配置好MySQL，熟悉SQL基础。接着，通过命令行客户端连接数据库，执行创建、查询、添加、修改和删除数据等操作。学习数据类型并创建表存储数据。最后，探讨了数据库优化，包括查询优化和索引使用，以提升性能。
龙大吉
11 2
yma16
|
5天前
|
安全 关系型数据库 MySQL
node实战——后端koa结合jwt连接mysql实现权限登录（node后端就业储备知识）
node实战——后端koa结合jwt连接mysql实现权限登录（node后端就业储备知识）
yma16
15 3
请回答1024
|
5天前
|
SQL 关系型数据库 MySQL
不允许你不知道的 MySQL 优化实战（一）
不允许你不知道的 MySQL 优化实战（一）
请回答1024
12 2
数据库

关系型数据库

热门文章

最新文章

  • 1
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 2
    Linux 性能诊断 perf使用指南
  • 3
    PostgreSQL 9种索引的原理和应用场景
  • 4
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 5
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 6
    PostgreSQL 百亿地理位置数据 近邻查询性能
  • 7
    MySQL · 引擎特性 · 初识 MySQL GIS 及 InnoDB R-TREE
  • 8
    page fault带来的性能问题
  • 9
    Greenplum 表空间和filespace的用法
  • 10
    PostgreSQL 金融行业高可用和容灾解决方案
  • 1
    数据存储成本飙升？莫慌！RDS数据归档功能来了
    32
  • 2
    下次老板问你MySQL如何优化时，你可以这样说，老板默默给你加工资
    27
  • 3
    MySQL慢日志的介绍以及如何使用问题
    14
  • 4
    MySQL的事务（看看也许有帮助呢）
    19
  • 5
    MySQL学习手册（第一部分）
    68
  • 6
    MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%🚀
    35
  • 7
    【设计模式系列笔记】建造者模式
    104
  • 8
    【设计模式系列笔记】工厂模式
    109
  • 9
    【设计模式系列笔记】抽象工厂模式
    107
  • 10
    【设计模式系列笔记】单例模式
    120

    • 相关产品

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

    • 相关课程

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

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考