MySQL的binlog日志的简介与查看

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL的binlog日志的简介与查看

1、binlog日志是什么

binlog主要用于记录MySQL中的数据库操作,它包括DDL和DML语句(除了查询语句)。它以事件的形式记录,它是一个二进制文件。MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册  5.1.24版)。二进制有两个最重要的使用场景:

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

binlog日志主要包括两类文件:二进制索引文件(文件名后缀为.index)用于记录所有二进制文件,二进制日志文件(文件名后缀为.0000*),记录数据库中所有的DDL和DML语句。

2、binlog日志如何查看

binlog有三种模式

format 定义 优点 缺点
statement 记录的是修改SQL语句 日志文件小,节约IO,提高性能 准确性差,对一些系统函数不能准确复制或不能复制,如now0.uuid0等
row 记录的是每行实际数据变化 准确性强,能准确复制数据的变更 日志文件大,较大的网络10和磁盘lO
mixed statement和row模式的混合 准确性强,文件大小适中 有可能发生主从不一致问题

业内目前推荐使用的是row模式,准确性高,虽然说文件大,但是现在有SSD和万兆光纤网络,这些磁盘IO和网络IO都是可以接受的。mysql默认的也是row模式。

binlog日志文件是二进制文件,不能直接查看。可以通过下面的命令进行查看

mysqlbinlog mysql-bin.00001

如果是row模式的话

mysqlbinlog -vv mysql-bin.00001
2.1. 查看binlog日志是否开启

mysql> show master status; #查看当前正在写入的binlog文件

如果没有开启的话则可以通过修改 my.ini (windows环境下)或者/etc/my.cnf 配置文件的 log-bin选项。

在配置文件中加入 log-bin 配置,表示启用binlog,如果没有给定值,写成 log-bin=,则默认名称为主机名。(注:名称若带有小数点,则只取第一个小数点前的部分作为名称)

[mysqld]
log-bin=my-binlog-name
2.2. 通过mysqlbinlog命令查看binlog日志

mysqlbinlog 是一个查看mysql二进制日志的工具,可以把mysql上面的所有操作记录从日志里导出,默认的安装路径是 :/usr/local/mysql/bin/mysqlbinlog

可以通过 find / -name "mysqlbinlog" 或者 which mysqlbinlog 命令来查找mysqlbinlog 工具的路径。

如果输入 mysqlbinlog 命令提示找不到命令的话,则可以通过建立软链的方式。比如我的mysqlbinlog命令在 /data/server/mysql/bin/mysqldump 路径下,可以通过如下命令建立软链解决此问题

ln -s /data/server/mysql/bin/mysqldump /usr/bin/mysqldump

如下是使用mysqlbinlog 命令的基本示例

# mysqlbinlog 的执行格式
mysqlbinlog [options] log_file ...
# 查看bin-log二进制文件(shell方式)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
# 查看bin-log二进制文件(带查询条件)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
    --start-datetime="2023-09-11 00:00:00"  \
    --stop-datetime="2023-09-12 00:00:00"   \
    --start-position="5000"    \
    --stop-position="20000"

这里设置了日志查询的开始时间(2023-09-11 00:00:00),结束时间(2023-09-12 00:00:00),开始位置(5000)以及结束位置(20000)。

更多的mysqlbinlog命令的操作参数可以通过 mysqlbinlog --help进行查看。

mysqlbinlog 日志的部分内容

#230910 15:38:30 server id 1  end_log_pos 328 CRC32 0x3bb60240  Query thread_id=63  exec_time=0 error_code=0
BEGIN
/*!*/;
# at 328
# at 575
#230910 15:38:30 server id 1  end_log_pos 658 CRC32 0x20b2fcd1  Table_map: `db1`.`table1` mapped to number 197
# has_generated_invisible_primary_key=0
# at 658
#230910 15:38:30 server id 1  end_log_pos 771 CRC32 0xa0d5cf45  Write_rows: table id 197 flags: STMT_END_F
### INSERT INTO `db1`.`table1`
### SET
###   @1=1
###   @2=2190000159000076193
###   @3=585
###   @4=3
###   @5=4
###   @6=2
###   @7=NULL
###   @8='2020-06-05 17:04:51'
###   @9='2023-09-10 15:38:30'
###   @10='1268831135392108546'
###   @11=0
# at 771
#230910 15:38:30 server id 1  end_log_pos 802 CRC32 0xecd87f75  Xid = 90733
COMMIT/*!*/;

上面的输出包括信息

position: 位于文件中的位置,即第一行的(# at 230910),说明该事件记录从文件第230910 个字节开始
timestamp: 事件发生的时间戳,即第二行的(#190308 15:38:30)
server id: 服务器标识(1)
end_log_pos 表示下一个事件开始的位置(即当前事件的结束位置+1)
thread_id: 执行该事件的线程id (thread_id=63)
exec_time: 事件执行的花费时间
error_code: 错误码,0意味着没有发生错误
type:事件类型Query

增加 --base64-output=decode-rows –v 选项解析

(base64-output,可以控制输出语句输出base64编码的BINLOG语句;decode-rows:选项将把基于行的事件解码成一个SQL语句)

mysqlbinlog --no-defaults --database=db1 --base64-output=decode-rows -v  mysql-bin.000269>/data/server/binlog.sql

通过这个命令可以将mysql-bin.000269二进制文件的内容写到binlog.sql文件中。

删除binlog

删除binlog 可以通过 reset master 命令来实现。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
4
4
0
62
分享
相关文章
图解MySQL【日志】——Buffer Pool
Buffer Pool 是数据库管理系统(DBMS)中用于缓存磁盘数据页的内存区域,主要包含数据页、索引页、undo 页等。它通过减少磁盘 I/O 提升性能,特别是在处理大型数据库时效果显著。查询时,整个数据页而非单条记录会被加载到 Buffer Pool 中,以提高访问效率。
8 0
图解MySQL【日志】——Buffer Pool
图解MySQL【日志】——Undo Log
Undo Log(回滚日志)是 MySQL 中用于实现事务原子性和一致性的关键机制。在默认的自动提交模式下,MySQL 隐式开启事务,每条增删改语句都会记录到 Undo Log 中。其主要作用包括:
8 0
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL原理简介—8.MySQL并发事务处理
这段内容深入探讨了SQL语句执行原理、事务并发问题、MySQL事务隔离级别及其实现机制、锁机制以及数据库性能优化等多个方面。
MySQL原理简介—7.redo日志的底层原理
本文介绍了MySQL中redo日志和undo日志的主要内容: 1. redo日志的意义:确保事务提交后数据不丢失,通过记录修改操作并在系统宕机后重做日志恢复数据。 2. redo日志文件构成:记录表空间号、数据页号、偏移量及修改内容。 3. redo日志写入机制:redo日志先写入Redo Log Buffer,再批量刷入磁盘文件,减少随机写以提高性能。 4. Redo Log Buffer解析:描述Redo Log Buffer的内存结构及刷盘时机,如事务提交、Buffer过半或后台线程定时刷新。 5. undo日志原理:用于事务回滚,记录插入、删除和更新前的数据状态,确保事务可完整回滚。
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log、原理、写入过程;binlog与redolog区别、update语句的执行流程、两阶段提交、主从复制、三种日志的使用场景;查询日志、慢查询日志、错误日志等其他几类日志
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等