mysql binlog

这篇具有很好参考价值的文章主要介绍了mysql binlog。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

二进制日志文件记录了数据库修改的事件,像表的修改,表数据的变更等。也包含潜在的可能修改数据的语句事件。如一些delete或update最后修改的数据行可能是0,也会被记录在binlog中(和日志格式也有一定关系,非row-based)。除此之外binlog还会记录语句的执行时间信息。

binlog的作用

1、用于主从复制,数据同步。这个前面的文章有介绍。

2、数据恢复。可以用于恢复数据库到特定的时间点(Point-in-Time Recovery)或特定的事务状态。通过重放二进制日志中的操作,可以将数据库还原到某个特定时间点之前的状态。

binlog不记录不修改数据的操作语句,像select、show。

开启binlog需要在配置文件中配置log-bin项

[mysqld]
log-bin=mysql-bin

log-bin的值指定了binlog日志文件的base_name。如上指定为mysql-bin。则对应的binlog文件会是mysql-bin.000001、mysql-bin.000002这种。Mysqld在二进制日志base_name后面附加一个数字扩展名来生成二进制日志文件名。每次服务器创建一个新的日志文件时,这个数字都会增加,从而创建一个有序的文件系列。在以下情况mysql会创建一个新的文件:

1、mysql开启或重启时刻

2、当前日志文件达到了max_binlog_size设置的值,也就是最大binlog文件大小。默认1GB。

可以通过名mysql> SHOW BINARY LOGS;来查看当前数据库binlog文件。

由于有多个日志文件的存在,mysql会用一个index文件来记录已经使用的binlog文件列表,该文件和binlog日志文件名相同,文件后缀是’.index’。如mysql-bin.index。也可以通过log-bin-index来指定文件名。

如果当前session不需要记录binlog,可以设置 SET sql_log_bin=OFF 。暂时关闭当前会话的binlog记录。

binlog格式

binlog有三种可供选择的格式:

1、STATEMENT

MySQL将会记录执行的SQL语句,包括对表的增、删、改操作。这样可以确保在执行一条相同的SQL语句时,得到相同的结果。但是,如果在执行过程中涉及到了MySQL内部函数、触发器、存储过程等,则可能会导致数据不一致的问题。因此,在使用STATEMENT格式时需要特别注意这些情况。比如在插入一个日期类型的字段时候使用now()函数取值,now()取系统时间,在不同的时点now()值不同,可能导致数据不一致。还有类似的UUID()函数。

2、ROW

ROW格式中,MySQL将会记录每一行数据的变化,包括对表的增、删、改操作。这种格式可以确保数据的完整性和一致性,但是对于大表或者数据量较大的表,可能会导致比较大的binlog文件,造成存储和传输的困难。比如一个update或delete涉及修改行数较多时,会生成对应每行的修改更新语句日志。在一些数据实时同步时被要求必须使用该格式。

3、MIXED

MIXED格式是STATEMENT和ROW格式的混合形式,MySQL会自动根据不同的操作选择合适的格式。对于简单的操作(如插入、更新、删除操作),使用STATEMENT格式;对于较为复杂的操作(如涉及MySQL内部函数、触发器、存储过程等),则使用ROW格式。这种混合形式可以兼顾数据完整性和binlog文件大小的平衡。

可以通过binlog-format来进行设置日志格式。如binlog-format=ROW

binlog文件查看

可以使用mysqlbinlog命令来查看binlog日志文件,mysqlbinlog时一个单独的命令,不用登录直接命令行操作即可。

命令格式

mysqlbinlog [options] log_file 

mysqlbinlog有很多参数,这里列举下一些常见的参数

–base64-output=[decode-rows|auto|never] :指定如何处理Base64编码的数据。

-d, --database=name :指定数据库名

-r, --result-file=name:指定输出到对应的文件

–start-datetime=name:指定开始解析的时间点

–stop-datetime=name:指定结束解析的时间点

-j, --start-position=N:指定开始解析的位置

–stop-position=N:指定结束解析的位置

-v, --verbose:显示详细信息,在ROW模式下添加该参数可以看到具体的sql语句。

-o, --offset=N 跳过N行

指定数据库base64解码解析:

> mysqlbinlog --database=db_test --base64-output=decode-rows  mysql-bin.000003|more

按时间解析:

>mysqlbinlog --start-datetime="2024-02-20 00:00:00" --stop-datetime="2024-02-21 00:00:00" mysql-bin.000003 > output.sql

指定开始解析位置:

>mysqlbinlog -j 15028 mysql-bin.000003 > from-15028.out

连接远端mysql

>mysqlbinlog -R -h 192.168.101.2 -p mysql-bin.000003

-R 等价于–read-from-remote-server ,从远端服务器读取

-h,-p指定host和password,-P指定端口

解析出的binlog信息如下:

# at 504263
#240221 15:31:11 server id 1  end_log_pos 504342 CRC32 0xb683e8bc 	Anonymous_GTID	last_committed=41	sequence_number=42	rbr_only=yes	original_committed_timestamp=1708500671155876	immediate_commit_timestamp=1708500671155876	transaction_length=308
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1708500671155876 (2024-02-21 15:31:11.155876 CST)
# immediate_commit_timestamp=1708500671155876 (2024-02-21 15:31:11.155876 CST)
/*!80001 SET @@session.original_commit_timestamp=1708500671155876*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 504342
#240221 15:31:11 server id 1  end_log_pos 504429 CRC32 0xb5706921 	Query	thread_id=87	exec_time=0	error_code=0
SET TIMESTAMP=1708500671/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=255/*!*/;
BEGIN
/*!*/;
# at 504429
#240221 15:31:11 server id 1  end_log_pos 504486 CRC32 0x78d6bd44 	Table_map: `db_test`.`account` mapped to number 299
# at 504486
#240221 15:31:11 server id 1  end_log_pos 504540 CRC32 0xf5262e79 	Update_rows: table id 299 flags: STMT_END_F
### UPDATE `db_test`.`account`
### WHERE
###   @1=6
###   @2=1
### SET
###   @1=6
###   @2=3
# at 504540
#240221 15:31:11 server id 1  end_log_pos 504571 CRC32 0xfc8b0084 	Xid = 100230
COMMIT/*!*/;

上面一条更新语句,可以看到能解析出原sql语句,时间信息,postion信息,还有一些事务相关的信息。

binlog的写入

binlog在事务提交时候进行日志的写入。这里不禁想到上篇文章写的redo log也是在事务提交的时候进行写入。这里就涉及到了日志双写,都需要进行持久化且完整。binlog日志是mysql server层产生日志,redo log是引擎层Innodb需要产生的日志。server层需要解决多个引擎层之间的事务一致性。这里需要内部的XA事务(分布式事务)来完成。其中协调者是server层。

XA采用两阶段提交协议保证分布式事务的一致性。

两阶段提交

两阶段提交(Two-Phase Commit,2PC)是一种用于分布式系统中实现事务的协议,旨在确保所有参与者要么全部提交事务,要么全部回滚事务,以保持数据的一致性。

在两阶段提交协议中,有一个协调者(Coordinator)和多个参与者(Participants)。协调者负责协调每个参与者的状态,控制整个事务的提交或回滚过程。整个过程可以分为以下两个阶段:

  1. 准备阶段(Prepare Phase):
    • 协调者向所有参与者发送事务准备请求,要求它们准备好执行事务操作。
    • 参与者接收到请求后,会执行本地事务操作,并将准备好的状态反馈给协调者。
    • 协调者等待所有参与者的响应,如果所有参与者都准备就绪,则进入下一阶段;否则,中止事务。
  2. 提交阶段(Commit Phase):
    • 如果在准备阶段所有参与者都成功准备好,协调者向所有参与者发送提交事务的请求。
    • 参与者接收到提交请求后,执行提交操作,并向协调者发送确认消息。
    • 协调者收到所有参与者的确认消息后,最终提交事务;否则,向所有参与者发送回滚事务的请求。

通过两阶段提交协议,可以确保在分布式环境中的所有参与者要么全部提交事务,要么全部回滚事务,避免了数据不一致的情况发生。

回到mysql的binlog和redolog 这里的协调者是server层binlog。参与者是引擎层的redolog。

prepare阶段

1、 redo log日志写入,XA事务状态为prepare状态

2、binlog日志写入,XA事务状态prepare状态

commit阶段

3、binlog日志写入提交,XA事务状态为commit

4、redolog日志写入提交,XA事务状态为commit

这里将redolog日志的写入分成两步进行提交。在上面的解析的binlog内容可以看到Xid(XA事务ID)信息。来分析下两阶段提交如何能保证数据完整:

这里把2和3部合并了来说,因为都是binlog的连续操作,就是整个分三大步:

A、redolog xa_prepare

B、binlog write

C、redolog xa_commit

这样会有两处间隙时刻即A|B之间,B|C之间,如果在这两处操作空隙数据库发生crash。数据库如何恢复?

数据库crash,在数据库启动恢复的时候,会根据redolog进行恢复,遇到是prepare状态的,会根据对应的XID去binlog中去找对应的操作记录是否存在,如果存在事务进行提交,如果不存在则丢弃。即A|B之间的crash会进行回滚,B|C之间的crash可以提交,这样就能保证日志数据的一致性。

binlog的写入还受sync_binlog参数的影响。

  1. sync_binlog=0:表示不进行二进制日志的同步操作,即异步写入。这种方式下,MySQL将日志写入操作系统的缓存,并不会等待日志真正写入磁盘,因此具有较高的性能,但也存在数据丢失的风险。
  2. sync_binlog=1:表示每次提交事务都会强制将二进制日志同步到磁盘。这种方式下,MySQL会在每个事务提交时等待日志写入磁盘完成后再返回确认消息,以确保数据的持久性。虽然提供了较高的数据一致性,但也会对性能产生一定的影响。
  3. sync_binlog=N(N大于1):表示每N个事务才进行一次二进制日志的同步操作。这种方式是在性能和数据一致性之间进行权衡的选择,可以在一定程度上提升性能,但也会增加数据丢失的风险。

默认值是1,即每个事务同步写盘。建议和redolog的innodb_flush_log_at_trx_commit同步保持为1。文章来源地址https://www.toymoban.com/news/detail-835685.html

到了这里,关于mysql binlog的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL去除数据库重复记录

    要从MySQL数据库中删除重复的记录,您可以使用 DELETE 语句结合 GROUP BY 和 HAVING 子句来实现。以下是一个示例: 在上述示例中,您需要将 your_table 替换为您的表名,并将 column1, column2, ... 替换为用于判断重复的列名。这个查询将删除所有重复的记录,只保留每组重复记录中的一

    2024年02月11日
    浏览(61)
  • 数据库问题记录(粗略版)oracle、mysql等主流数据库通用

    1. ORA-00918:未明确定义列 该问题情况大致为:select 所取列名错误、重复等问题。 2. “select * from temp where 1=0; ”的含义 布尔值为FALSE,只返回表结构,不返回数据。 举一反三: select * from temp where 10 , 布尔值为TRUE,返回所有数据记录; select * from temp where 1=0, 暂不清楚是何

    2024年02月07日
    浏览(48)
  • 在 MySQL 数据库中删除重复记录的步骤

    当我们在处理数据库中的数据时,有时候会出现重复记录的情况,这些重复记录会影响数据的正确性,需要将其删除。下面是在 MySQL 数据库中删除重复记录的步骤: 首先,我们需要找到数据库表中的重复记录。可以使用以下 SQL 语句查询表中所有的重复记录: 其中, table_

    2024年02月15日
    浏览(68)
  • 记录JDBC连接MySQL数据库时遇到的问题

    记录使用 JDBC连接数据库的时候遇到的问题 java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 可以参考这篇 java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 博主总结的很全,就不赘述了~ com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the

    2024年02月10日
    浏览(55)
  • 向量数据库Chroma学习记录

    Chroma是一款AI开源向量数据库,用于快速构建基于LLM的应用,支持Python和Javascript语言。具备轻量化、快速安装等特点,可与Langchain、LlamaIndex等知名LLM框架组合使用。 安装方式非常简单,只需要一行命令 这里面的集合用于存放向量以及元数据的信息,可以理解为传统数据库的

    2024年04月13日
    浏览(40)
  • 向量数据库之Lancedb学习记录

    Lancedb是一个用于人工智能的开源矢量数据库,旨在存储、管理、查询和检索大规模多模式数据的嵌入。Lancedb的核心是用Rust编写的,并构建在Lance之上,专为高性能 ML 工作负载和快速随机访问而设计。 目前0.6.8需要pyarrow-12.0.0及以上,亲测15.0会报错。 与Chroma不同,lancedb没有

    2024年04月15日
    浏览(49)
  • Android Studio 学习记录-数据库

    目录 SQL的基本语法 1.数据定义语言 2.数据操纵语言 数据库管理器 SQLiteDatabase 数据库帮助器 SQLiteOpenHelper 优化记住密码功能         本文介绍Android的数据库存储方式-SQLite的使用方法,包括:SQLite用到了哪些SQL语法,如何使用数据库管理器操纵SQLite,如何使用数据库帮助器

    2024年02月07日
    浏览(50)
  • OB数据库基础知识(学习记录)

    目录 OB业务场景 公司使用理由: 常见 bootstrap 失败原因 常见OBD 部署 失败原因 Grafana  查看集群资源由各个节点的聚合情况  OB创建租户 表分组的场景 mysqldump到处数据库schema,数据库数据,表数据 数据同步框架 DATAX obdumper使用注意事项 obdumper调优 obloader使用注意事项  什么

    2024年02月14日
    浏览(37)
  • MySQL 数据库查询与数据操作:使用 ORDER BY 排序和 DELETE 删除记录

    使用 ORDER BY 语句按升序或降序对结果进行排序。 ORDER BY 默认按升序排序。要按降序排序结果,使用 DESC 。 示例按名称按字母顺序排序结果: ORDER BY DESC 使用 DESC 以降序排序结果。 示例按名称以字母逆序排序结果: 您可以使用\\\"DELETE FROM\\\"语句从现有表格中

    2024年02月05日
    浏览(78)
  • Oracle、MySQL数据库常规命令语法-简易记录(非常规持续更新)

    前言:呈现的是非常基础必备命令以及常规关联语法,因涉及到不同数据库其表达都会有所区别,此篇纯属做个仓库记录更非常规持续更新,专业人士可忽略,且看且珍惜… MySQL: 关系型数据库、重点开源、支持大型规模、标准SQL数据语言、多平台多架构、高可用集群、可定

    2024年01月25日
    浏览(55)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包