二进制日志文件记录了数据库修改的事件,像表的修改,表数据的变更等。也包含潜在的可能修改数据的语句事件。如一些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)。协调者负责协调每个参与者的状态,控制整个事务的提交或回滚过程。整个过程可以分为以下两个阶段:
- 准备阶段(Prepare Phase):
- 协调者向所有参与者发送事务准备请求,要求它们准备好执行事务操作。
- 参与者接收到请求后,会执行本地事务操作,并将准备好的状态反馈给协调者。
- 协调者等待所有参与者的响应,如果所有参与者都准备就绪,则进入下一阶段;否则,中止事务。
- 提交阶段(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参数的影响。文章来源:https://www.toymoban.com/news/detail-835685.html
- sync_binlog=0:表示不进行二进制日志的同步操作,即异步写入。这种方式下,MySQL将日志写入操作系统的缓存,并不会等待日志真正写入磁盘,因此具有较高的性能,但也存在数据丢失的风险。
- sync_binlog=1:表示每次提交事务都会强制将二进制日志同步到磁盘。这种方式下,MySQL会在每个事务提交时等待日志写入磁盘完成后再返回确认消息,以确保数据的持久性。虽然提供了较高的数据一致性,但也会对性能产生一定的影响。
- 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模板网!