暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL二进制日志介绍

MySQL二进制日志介绍

数风云 2021-02-02
608


点击上方“数风云”关注我们吧！

文/段晗



MySQL二进制日志简介



01
概述


MySQL二进制日志（binary log，以下简称binlog）是MySQL服务在运行过程中产生的运行日志的一种。它仅记录对数据库进行修改的操作，不记录诸如SELECT，SHOW等仅查询数据的操作。因此binlog是保证MySQL数据一致性的重要日志，MySQL默认开启binlog的记录，也推荐项目组开启binlog记录功能。


02
核心目的


MySQL binlog的产生有两个核心目的：

1.支持主从复制架构。主服务器执行更改数据的事务后，会将产生的binlog发送到从服务器中，从服务器将根据binlog记录的数据修改信息执行同样的动作，以保证与主服务器的数据达成最终一致。

2.数据恢复。将备份数据完成恢复后，可通过binlog将数据备份执行之后记录的事件回放，这使得上一次备份之后执行的数据修改不会丢失，实现指定时间点数据恢复。


03
运行机制及一些常见参数


当开启一个事务时，MySQL会分配一块内存来缓存该事务的修改操作产生的binlog，这块缓冲区的大小由参数binlog_cache_size决定，默认值为32768字节（32kb）；待事务发出commit请求时，缓存的binlog将会在执行commit之前写入binlog文件。也就是说，binlog文件内所记录的，均为已全部执行完成的事务信息。

binlog的默认存储位置是MySQL的数据目录，但可以通过配置log-bin参数更改存储位置与binlog的文件名。以下是一个写入配置文件的log-bin参数的示例：


log_bin=/mysqldata/mysql_3306/log/mysql_bin


该示例说明，更改binlog存储位置需要指定绝对路径；参数值结尾的mysql_bin即是binlog文件的文件名。一个binlog文件的大小由参数max_binlog_size决定，默认值为1gb。当binlog文件超出该参数指定的大小时，MySQL将会新建一个binlog文件，并以数字编号作区分。此外，当数据库服务重启，或执行了日志刷新操作时，MySQL也会新建一个binlog文件。以下示例展示了MySQL存储binlog的目录结构：


[root@host157 log]# ll
total 510184
mysql_bin.000001
mysql_bin.000002
mysql_bin_index.index
mysqld.log
mysqld.slow.log
mysql_relay-group_replication_applier.000001
mysql_relay-group_replication_applier.000002
mysql_relay-group_replication_recovery.000001
mysql_relay_index-group_replication_applier.index
mysql_relay_index-group_replication_recovery.index


以上示例中mysql_bin.000001和mysql_bin.000002为binlog文件。由于多个binlog文件的存在，为了跟踪正在使用的binlog文件，另有mysql_bin_index.index文件标记正在使用的binlog文件。

若要查询当前正在应用的binlog文件，请在MySQL客户端输入如下语句：


Show master status;



MySQL二进制日志记录格式



01
记录格式介绍


事件记录在binlog中的格式由binlog记录格式决定。Binlog存在三种记录格式，分别如下：

1.statement：语句记录格式，该格式下binlog记录的内容是执行数据修改的SQL语句。

2.row：行记录格式，该格式下binlog记录表的行记录修改情况，也是MySQL默认的binlog记录格式。但DDL语句仍然会以语句形式记录在binlog中，如create table语句。

3.mixed：混合记录格式，该格式下binlog默认会以语句记录格式记录，但在满足某些条件时会以行记录格式记录。具体的条件请参考附录：混合记录格式下触发行记录的条件。


02
记录格式配置


binlog-format参数用来控制binlog的记录格式，其可选值有三个：STATEMENT,ROW以及MIXED。该参数是动态参数，因此既可以在参数文件中配置，也可以在MySQL运行中使用SET语句修改，示例如下：


全局参数修改：
mysql>SET GLOBAL binlog_format=’STATEMENT’;
单独会话参数修改：
mysql>SET SESSION binlog_format=’  ROW’;


03
记录格式的选择


行记录格式适用于对数据库进行大量小更改的情况，以及一条SQL语句执行时间较长，但修改行数较少的情况；若有单SQL语句修改大量行数的情况，在行记录格式下的binlog日志文件会迅速增大。

而语句记录格式更适用于单SQL语句修改大量行数的情况。不同的适用场景也是MySQL将binlog-format参数设置为可在会话中进行修改的原因。

当MySQL使用InnoDB引擎，且事务隔离级别设定为RC(READ COMMIT)或RU(READ UNCOMMIT)时，binlog记录格式只能选择行记录格式，因为此时指定语句记录格式会导致InnoDB引擎无法执行数据插入操作。且语句记录格式下存在主从复制数据不一致的问题。

因此行记录格式是更为推荐使用的binlog记录格式。如果应用场景更适用于语句记录格式，则需要首先调整事务隔离级别为RR(REPEATABLE READ)或者SERIALIZABLE，之后再修改binlog记录格式。



MySQL二进制日志解读



Binlog的记录格式是二进制，不能直接以查看文本的方式查看。MySQL官方提供了mysqlbinlog这一程序，用于将二进制日志的信息转化为文本，便于解读。


01
mysqlbinlog的用法


在linux命令行下，依照如下示例，指定binlog的文件名，即可解读binlog：


mysqlbinlog [options] mysql-bin.000032


可在binlog文件名前指定命令选项，常用选项如下：


--defaults-file=file_name  #指定配置文件，读取其中的选项作为命令选项
-R  #从远程mysql服务器读取binlog
-h=host_name  #指定远程mysql服务器hostname
-d=db_name  #仅输出与指定的db相关的内容
-o=N  #跳过日志的前n行
-u=username  #指定数据库用户名
-p=password  #指定数据库密码
-P=port_num  #指定数据库端口
-S=path  #指定socket文件
--server-id=id  #指定一个服务器id，仅输出由该服务器发出的请求引起的事件
--start-datetime=datetime  #指定一个时间戳，仅输出晚于该时间发生的事件；该时间戳的格式应符合DATETIME数据类型的规范
--stop-datetime=datetime  #指定一个时间戳，仅输出早于该时间发生的事件；该时间戳的格式应符合DATETIME数据类型的规范
--start-position=N  #指定日志偏移量，仅输出该偏移量之后的事件
--stop-position=N  #指定日志偏移量，仅输出该偏移量之前的事件
-v  #适用于行记录格式的binlog，将行数据的变化转换为sql语句形式表现，便于解读


更多mysqlbinlog命令选项可参见mysql官方文档相关章节的内容。


02
mysqlbinlog的输出内容解读


作为示例，现重启mysql数据库服务，重新生成一个binlog，之后对mysql数据库作如下操作，之后观察binlog的记录情况：


create database dhtest;
use dhtest;
create table test(t1 int,primary key(t1));
insert into test values(1),(2);
select * from test;


之后使用形如3.1节所示命令查看binlog，命令输出部分内容如下，完整内容请查看附录：


# at 479
#200725 20:56:47 server id 1  end_log_pos 612 CRC32 0x8c2cb084  Query   thread_id=557   exec_time=0     error_code=0    Xid = 12971
use `dhtest`/*!*/;
SET TIMESTAMP=1595681807/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test(t1 int,primary key(t1))
/*!*/;
......


输出的第一行“at 479”表示binlog的文件位置，该数值的含义是binlog记录的事件所占用的空间，单位为字节。

第二行记录了事件发生的时间戳，服务器id以及该事件结束所对应的位置。示例中记录的end_log_pos为612，起始位置为479，意味着binlog记录这一个事件占用了612-479=133字节。

此外第二行还记录了执行该事件的线程id以及执行时间。Error_code表示执行结果，0代表执行成功，没有错误发生。

此后的内容便是记录实际的操作。本示例的binlog采用的是行记录格式，可以看到如上文所述，DDL语句仍然以语句形式记录在了binlog中。


# at 691
#200725 20:56:47 server id 1  end_log_pos 768 CRC32 0xccc6b304  Query   thread_id=557   exec_time=0     error_code=0
SET TIMESTAMP=1595681807/*!*/;
BEGIN
/*!*/;
# at 768
# at 822
#200725 20:56:47 server id 1  end_log_pos 874 CRC32 0x5e73b013  Table_map: `dhtest`.`test` mapped to number 196
# at 874
#200725 20:56:47 server id 1  end_log_pos 919 CRC32 0x50ac4e2e  Write_rows: table id 196 flags: STMT_END_F


BINLOG '
DywcXxMBAAAANAAAAGoDAAAAAMQAAAAAAAEABmRodGVzdAAEdGVzdAABAwAAAQEAE7BzXg==
DywcXx4BAAAALQAAAJcDAAAAAMQAAAAAAAEAAgAB/wABAAAAAAIAAAAuTqxQ
'/*!*/;
# at 919
#200725 20:56:47 server id 1  end_log_pos 950 CRC32 0xae736965  Xid = 12972
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*/;


由上述内容可见，在执行DML语句时，MySQL隐式开启了一个事务，在DML语句执行完成后自动提交，并记录在了binlog中。并且select语句没有出现在binlog中。
若由“at 874”处读起，会发现以下内容是不可读的：


BINLOG '
DywcXxMBAAAANAAAAGoDAAAAAMQAAAAAAAEABmRodGVzdAAEdGVzdAABAwAAAQEAE7BzXg==
DywcXx4BAAAALQAAAJcDAAAAAMQAAAAAAAEAAgAB/wABAAAAAAIAAAAuTqxQ


这是由于mysqlbinlog默认以base64字符串编码来输出行记录格式记录的行修改。若在读取命令中加入-v选项：


mysqlbinlog -v mysql-bin.000032


此时该部分内容显示如下：


BINLOG '
DywcXxMBAAAANAAAAGoDAAAAAMQAAAAAAAEABmRodGVzdAAEdGVzdAABAwAAAQEAE7BzXg==
DywcXx4BAAAALQAAAJcDAAAAAMQAAAAAAAEAAgAB/wABAAAAAAIAAAAuTqxQ
'/*!*/;
### INSERT INTO `dhtest`.`test`
### SET
###   @1=1
### INSERT INTO `dhtest`.`test`
### SET
###   @1=2


这时我们便可以解读行数据变化情况。



MySQL二进制日志用于数据恢复



01
基本操作


binlog中记录的内容，经过mysqlbinlog程序解读之后，可直接回放于MySQL服务。例如3.2节的示例中，若先前执行的数据库操作全部丢失，这时即可使用binlog还原以上操作，恢复数据。基本操作如下：


mysqlbinlog mysql-bin.000032 | mysql -uroot -pDDB@w0rd


也可以在一条命令中指定多个binlog文件执行：


mysqlbinlog mysql-bin.000032 mysql-bin.000033 | mysql -uroot -pDDB@w0rd


不建议使用多个命令执行数据恢复，每条命令指定一个binlog文件的形式。若前一个binlog中记录了创建临时表的操作，而下一个binlog文件对该临时表有操作，那么第一条命令将binlog回放完成后，会中断mysql连接并删除临时表，此时第二个命令的执行会报错。

使用binlog恢复数据的操作要求数据库用户拥有BINLOG_ADMIN权限。


02
指定时间点数据恢复


当通过恢复全量备份来将数据还原至备份执行的时间点后，我们还希望能够进一步将数据恢复至某个指定时间点，此时可利用binlog可回放的特性执行数据恢复。

假设全量备份恢复完成后，binlog文件的位置是155，希望将数据恢复至binlog位置为355时的状态，则可以用以下命令执行恢复：


mysqlbinlog --start-position=155 --stop-position=355 mysql-bin.000032 | mysql -uroot -pDDB@w0rd


当不确定希望将数据恢复至binlog的位置号，但知晓大致的时间范围时，可用以下方式查询，自行找到想要恢复时间点对应的binlog位置号：


mysqlbinlog -v --start-datetime=”2020-03-31 20:05:00” --stop-datetime=”2020-03-31 21:05:00” mysql-bin.000032



 附录A 3.2节示例的binlog全部内容




/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200725 20:42:33 server id 1  end_log_pos 124 CRC32 0xb69fcc3b  Start: binlog v 4, server v 8.0.18 created 200725 20:42:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
uSgcXw8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC5KBxfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgE7zJ+2
'/*!*/;
# at 124
#200725 20:42:33 server id 1  end_log_pos 211 CRC32 0x974b5256  Previous-GTIDs
# 8bcae1cb-9f34-11ea-9ae1-18022da9150d:22-5555257:6555254-6555511
# at 211
#200725 20:56:47 server id 1  end_log_pos 288 CRC32 0x4472cfbd  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1595681807301788   immediate_commit_timestamp=1595681807301788   transaction_length=191
# original_commit_timestamp=1595681807301788 (2020-07-25 20:56:47.301788 CST)
# immediate_commit_timestamp=1595681807301788 (2020-07-25 20:56:47.301788 CST)
/*!80001 SET @@session.original_commit_timestamp=1595681807301788*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 288
#200725 20:56:47 server id 1  end_log_pos 402 CRC32 0x2fb095be  Query   thread_id=557   exec_time=0     error_code=0    Xid = 12966
SET TIMESTAMP=1595681807/*!*/;
SET @@session.pseudo_thread_id=557/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database dhtest
/*!*/;
# at 402
#200725 20:56:47 server id 1  end_log_pos 479 CRC32 0xbd8a988d  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no     original_committed_timestamp=1595681807498631   immediate_commit_timestamp=1595681807498631   transaction_length=210
# original_commit_timestamp=1595681807498631 (2020-07-25 20:56:47.498631 CST)
# immediate_commit_timestamp=1595681807498631 (2020-07-25 20:56:47.498631 CST)
/*!80001 SET @@session.original_commit_timestamp=1595681807498631*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 479
#200725 20:56:47 server id 1  end_log_pos 612 CRC32 0x8c2cb084  Query   thread_id=557   exec_time=0     error_code=0    Xid = 12971
use `dhtest`/*!*/;
SET TIMESTAMP=1595681807/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test(t1 int,primary key(t1))
/*!*/;
# at 612
#200725 20:56:47 server id 1  end_log_pos 691 CRC32 0x6dedb485  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1595681807510585   immediate_commit_timestamp=1595681807510585   transaction_length=338
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1595681807510585 (2020-07-25 20:56:47.510585 CST)
# immediate_commit_timestamp=1595681807510585 (2020-07-25 20:56:47.510585 CST)
/*!80001 SET @@session.original_commit_timestamp=1595681807510585*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 691
#200725 20:56:47 server id 1  end_log_pos 768 CRC32 0xccc6b304  Query   thread_id=557   exec_time=0     error_code=0
SET TIMESTAMP=1595681807/*!*/;
BEGIN
/*!*/;
# at 768
# at 822
#200725 20:56:47 server id 1  end_log_pos 874 CRC32 0x5e73b013  Table_map: `dhtest`.`test` mapped to number 196
# at 874
#200725 20:56:47 server id 1  end_log_pos 919 CRC32 0x50ac4e2e  Write_rows: table id 196 flags: STMT_END_F


BINLOG '
DywcXxMBAAAANAAAAGoDAAAAAMQAAAAAAAEABmRodGVzdAAEdGVzdAABAwAAAQEAE7BzXg==
DywcXx4BAAAALQAAAJcDAAAAAMQAAAAAAAEAAgAB/wABAAAAAAIAAAAuTqxQ
'/*!*/;
# at 919
#200725 20:56:47 server id 1  end_log_pos 950 CRC32 0xae736965  Xid = 12972
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*/;



附录B 混合记录格式下服务器使用行记录格式记录binlog的条件





当数据库事务满足以下条件时，使用混合记录格式记录binlog的服务器会使用行记录格式记录binlog：

当函数包含UUID()时；

当一个或多个带有自增列的表被更新，且此过程调用触发器或存储的函数时；

当涉及UDF调用时；

使用FOUND_ROWS()或ROW_COUNT()时；

使用USER()或CURRENT_USER()时；

当涉及的表中有mysql日志表时；

使用LOAD_FILE()函数时；

当一条语句引用一个或多个系统变量时。



顾问：许国平 李湘宜

     罗学平 刘德清 张刚

总编：孙鹏晖

美编：白羽

编辑：韩翠娟


长按二维码，关注我们吧！


-本文为“数风云”第21期文章；

-转载本公众号文章请联系我们；

-欢迎来稿：请按“题目-作者”格式命名发送到sunpenghui@abchina.com。



mysql
文章转载自数风云，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论