一文读懂MySQL的binLog日志

最新推荐文章于 2024-05-14 15:24:06 发布

IT小白架构师之路

最新推荐文章于 2024-05-14 15:24:06 发布

阅读量9.8k

点赞数 4

分类专栏：一文读懂文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/YangZhenXin_Java/article/details/121922882

版权

一文读懂专栏收录该内容

3 篇文章 1 订阅

订阅专栏

一、前言

hello各位大佬好，最近天气逐渐变好，每天熬夜写bug的你们，应该适当放松下，走出办公室，走出“console.log（前端）”、“走出System.out.println（Java）”、走出“printf(C、python)”、走出“cout<<(C++)”、走出“echo(shell)”，是时候带上你的对象出去压马路、吃好吃的了。可惜啊可惜，手机对面的你可能没有对象！

没有对象怎么办呢？那就单着，小编我也没有资源介绍给你！那就跟小编学习新的知识。前段时间小编一直在讲解常用设计模式，今天小编吃饭的时候想到一个道理，读博文就像吃饭一样：吃甜的吃多了可能会想吃咸的，吃咸的吃多了可能想吃辣的；人如果一直读某一种东西，可能会产生疲倦感，所以小编今天换了个“菜”，讲一讲MySQL的binLog日志。

二、binlog日志

数据库操作类型：

在对数据库的日常操作中，MySQL的操作语法，经常使用的几种MySQL指令类型如下

DQL：数据查询语言，例如 where 、group by 、 orderby
DML：数据操作语言，例如 insert、delete、update等
DPL：事务处理语言，例如begin transaction、commit、rollback
DCL：数据控制语言，例如grant 、revoke控制用户访问权限等
DDL：数据定义语言，例如create、drop删除表、索引添加类等

binlog日志概念

binlog全称为:binary，翻译就是二进制文件，主要记录了MySQL在进行DML（数据操作语言）过程中的操作日志。在执行SQL语句的过程中，作为使用者，无需关注程序执行的过程，但是当数据库数据丢失，或者需要搭建数据库主从复制时，则此时的binlog日志文件的重要性就展现出来了。

binlog的模式有三种：STATEMENT、ROW、MIXED 。

1、STATMENT模式：基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave（主从复制模式）中的数据不一致(如sleep(暂停指定时间执行)函数， last_insert_id（自增）等情况下会出现问题)

2、ROW模式：基于行的复制，不记录每一条SQL语句的上下文信息，仅记录哪条数据被修改了，修改后的结果是什么

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、MIXED模式，混合模式的复制方式：如上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的相关操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

三、binlog日志如何打开与查看？

开启binlog日志

1. 查看本机是否开启了binlog日志，登录到mysql服务后，输入如下命令

show variables like '%log_bin%';

可以看到结果

可以看到log_bin 的值为“OFF”，我本机安装的版本，binlog日志是默认关闭的，那么如何开启呢？

2.开启binlog日志方式，打开mysql配置文件my.cnf，在[mysqlId]下面增加

log-bin=mysql-bin

3.开启binlog日志后，重新查看下

查看binlog日志的方式

1.使用命令查看所有binlog日志

show binlog events;

2.查看结果如下

查看的内容有以下几个字段

【Log_name】:日志名称
【pos】上次操作点的序号
【event_type】事件类型

FORMAT_DESCRIPTION_EVENT：代表binlog日志的第一条，且只会在第一次出现

ROTATE_EVENT：当MySQL切换至新的binlog文件的时候，MySQL会在旧的binlog文件中写入一个ROTATE_EVENT，表示新的binlog文件的文件名

QUERY_EVENT：记录更新操作的语句。

WRITE_ROWS_EVENT：在开启ROW模式记录的binlog文件中，WRITE_ROWS_EVENT记录了插入的行记录。

UPDATE_ROWS_EVENT：在开启ROW模式记录的binlog文件，UPDATE_ROWS_EVENT记录了更新的行记录。

DELETE_ROWS_EVENT：在开启ROW模式记录的binlog文件，DELETE_ROWS_EVENT记录了删除的行记录。

XID_EVENT：当事务提交时，无论哪种模式都会记录。

ROTATE_EVENT：当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置。

GTID_LOG_EVENT：在启用GTID模式（主从复制模式）后，MySQL将为每个事务都分配了个GTID。

PREVIOUS_GTIDS_LOG_EVENT：开启GTID模式（主从复制）后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT（在后简称PE）事件，它的值是上一个binlog的PE+GTID信息。

STOP_EVENT：当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。

3.查看日志中的记录

show binlog events in 'mysql-bin.000001';

可以看到系统中每一次的操作记录；

4.刷新日志时，会生成新的binlog文件

flush logs;

生成了新的binlog日志

5.命令查看了binlog的简单信息，如何查看执行时间、执行花费的时间等详细信息需要使用工具包中的mysqlbinlog命令。

mysqlbinlog  binlog文件完整路径  | more 

我执行了如下命令：

mysqlbinlog.exe D:\Program Files\MariaDB 5.5\data\mysql-bin.000003 | more

6.binlog日志内容解析，截取数据中的部分如下

# at 523 #210405 21:08:18 server id 1 end_log_pos 639 Query thread_id=13 exec_time=0 error_code=0 SET TIMESTAMP=1617628098/*!*/; update user set username='zhangqian' where uid = '15' /*!*/;

postion（描述）：at后面的数字代表在binlog日志文件的第几个字节开始（at 523 ）

timestamp（事件发生的时间戳）: 即第二行的（#210405 21:08:18）

server id（服务器标识）: （1），代表执行的主机编号

end_log_pos（结束字节数）：结束的字节位置639

Query（类型）：事件类型。

thread_id：处理的线程编号(13)

exec_time：执行花费的时间

error_code: 错误码

SET TIMESTAMP=1617628098/*!*/;代表执行的时间戳

update user set username='zhangqian' where uid = '15' 代表执行的语句，遇到下一个#at 则为下一个binlog日志事件。

四、总结

binlog日志详细记录了每一次的操作，例如执行的增加、修改操作，同样记录了每一个SQL执行的时间，类型和执行耗时，所以通过程序解析binlog日志，就可以完成对整个MySQL服务的所有操作内容的分析，完成数据同步。所以主从复制也是采用读取binlog日志的方式完成。目前市面上最流行的MyCat就是将binlog日志解析完成分库分表，主从复制的。

觉得不错可以关注我的公众号哦

å¾ç