MySQL Binlog入门

2023年 8月 13日 60.5k 0

引言: 之后的一段时间准备开始写一些关于mysql的文章,从实际的使用入手,介绍它的方方面面吧,主要是实际工作当中接触比较多的点。因为暑假期间数据库要做备份,涉及到binlog文件的处理,那么今天就先从这个知识点说起。MySQL的binlog(二进制日志)是一种重要的组件,用于记录数据库的操作,并以二进制格式存储。它可以捕捉数据库的插入、更新、删除等变更操作。binlog在数据恢复、备份、主从复制和故障容错等方面发挥着重要作用。本文将详细探讨MySQL binlog的底层原理,包括格式、事件、位置以及在不同场景下的重要性。

77.png

一、binlog的结构及组成部分

  二进制日志文件

  binlog全称为:binary,翻译就是二进制文件,主要记录了MySQL在进行DML(数据操作语言)过程中的操作日志。在执行SQL语句的过程中,作为使用者,无需关注程序执行的过程,但是当数据库数据丢失,或者需要搭建数据库主从复制时,则此时的binlog日志文件的重要性就展现出来了。MySQL的binlog是以文件的形式存在的,每个binlog文件都以一个唯一的递增编号来命名。一个binlog文件通常包含多个日志事件(log event),这些事件以连续的方式写入binlog文件。

  日志事件

  日志事件是binlog的基本组成单位,它是对数据库修改操作的记录。每个日志事件都包含了必要的信息,用于恢复数据库的状态。一个日志事件通常包含事件头(event header)和事件体(event body)两个部分。

  事件头包含了事件的元数据信息,如事件的类型、时间戳、服务器ID等。事件体则包含了具体的数据操作信息,如表名、操作类型、修改前后的数据等。

  事件组

  为了提高写入性能,binlog将多个日志事件组合成一个事件组(event group)进行批量写入。事件组的大小默认为1MB,可以通过配置参数进行调整。

  binlog的日志格式

  MySQL的binlog支持多种不同的日志格式,每种格式在存储和记录方式上有所不同。常用的binlog日志格式包括以下几种:

  (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日志打开与查看

   (1)开启binlog日志

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

show variables like '%log_bin%';

  可以看到结果:

12.png

  可以看到log_bin 的值为“OFF”,binlog日志是默认关闭的,那么如何开启呢?

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

log-bin=mysql-bin

  开启binlog日志后,重新查看下:

13.png

   (2)查看binlog日志

mysql> show binlog events ``in 'mysql-bin.000002'``;#查看指定binlog文件的内容 
mysql> show binary logs; #获取binlog文件列表 
mysql> show master status; #查看当前正在写入的binlog文件
mysql> show binary logs; #获取binlog文件列表 
参数名                                           | 含义                    |
| --------------------------------------------- | ---------------------- |
| log bin = (on                                 | off base name)         |
| sql log bin =( on                             | off )                  |
| expire logs days                              | 指定自动删除二进制日志的时间,即日志过期时间 |
| log bin index                                 | 指定mysql-bin.index文件的路径 |
| binlog format = [ mixed | row | statement ] | 指定二进制日志基于什么模式记录      
| max binlog size                               | 指定二进制日志文件最大值           
| binlog cache size                             | 指定事务日志缓存区大小           
| max binlog cache size                         | 指定二进制日志缓存最大大小         
| sync binlog = { 0 | n }                      | 指定写缓冲多少次,刷一次盘
SHOW BINLOG EVENTS[IN 'log_name'][FROM pos][LIMIT [offset,] row_count]
在二进制日志中显示事件。如果未指定,则显示第一个二进制日志。需要特权。'log_name'SHOW BINLOG EVENTSREPLICATION SLAVE该LIMIT子句与该语句具有相同的语法SELECT。请参见“ SELECT语句”。mysql> show binlog events limit 10;
+------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+
| Log_name         | Pos   | Event_type     | Server_id | End_log_pos | Info                                                |
+------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000001 |     4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4               |
| mysql-bin.000001 |   123 | Previous_gtids |         1 |         154 |                                                     |
| mysql-bin.000001 |   154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| mysql-bin.000001 |   219 | Query          |         1 |         300 | BEGIN                                               |
| mysql-bin.000001 |   300 | Table_map      |         1 |         412 | table_id: 392 (cactus_000510.360exthost_examinemgr) |
| mysql-bin.000001 |   412 | Delete_rows    |         1 |        8617 | table_id: 392                                       |
| mysql-bin.000001 |  8617 | Delete_rows    |         1 |       12732 | table_id: 392 flags: STMT_END_F                     |
| mysql-bin.000001 | 12732 | Xid            |         1 |       12763 | COMMIT /* xid=530 */                                |
| mysql-bin.000001 | 12763 | Anonymous_Gtid |         1 |       12828 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| mysql-bin.000001 | 12828 | Query          |         1 |       12917 | BEGIN                                               |
+------------------+-------+----------------+-----------+-------------+-------

   (2)刷新日志时,会生成新的binlog文件

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000090 |      107 |              |                  |
+------------------+----------+--------------+------------------+

   生成了新的binlog日志。

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

mysqlbinlog  binlog文件完整路径  | more 

我执行了如下命令:

mysqlbinlog.exe D:Program FilesMariaDB 5.5datamysql-bin.000003 | more

16.png

   (4)binlog日志内容解析,截取数据中的部分如下:

# at 123 #210405 12:15:13 server id 1 end_log_pos 639 Query thread_id=13 exec_time=0 error_code=0 SET TIMESTAMP=1317628098/*!*/; update user set username='xxxx' where uid = '15' /*!*/;

**postion(描述):** at后面的数字代表在binlog日志文件的第几个字节开始(at 523 )

**timestamp(事件发生的时间戳):** 即第二行的(#210405 21:08:18)

**server id([服务器](https://activity.huaweicloud.com/discount_area_v5/index.html?utm_source=hwc-csdn&utm_medium=share-op&utm_campaign=&utm_content=&utm_term=&utm_adplace=AdPlace070851)标识):** (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的工作原理

  3.1 写入过程 当数据库执行修改操作时,比如插入一条新记录、更新一条记录或删除一条记录,MySQL会将对应的日志事件写入当前的binlog文件中。MySQL会在内存中维护一个binlog缓冲区,将日志事件先写入缓冲区,并不立即写入磁盘文件。当缓冲区满时,或达到一定的时间间隔,MySQL会将缓冲区中的日志事件通过文件系统的write系统调用写入磁盘文件。

  3.2 刷盘过程 为了确保数据的持久性,MySQL会将binlog文件进行刷盘(flush)操作,将内存中的日志事件刷写到磁盘上的binlog文件中。MySQL在不同情况下有不同的刷盘策略,比如在事务提交时会进行刷盘操作,以保证事务的持久性。

  3.3 读取与解析 binlog的读取和解析通常由外部程序完成,开发者可以使用MySQL提供的工具或自定义程序进行解析。读取binlog可以按照顺序读取,或者通过指定位置或时间戳进行读取。读取binlog的过程主要包括以下几个步骤:打开binlog文件、解析事件头、解析事件体、处理事件数据。通过解析binlog可以获取到修改操作的详细信息,可以用于数据恢复、数据同步等应用场景。

四、binlog的相关配置和用法

  4.1 binlog的配置参数 在MySQL的配置文件中(通常是my.cnf或my.ini),可以通过以下配置参数来控制binlog的相关行为:

  · log_bin:是否开启binlog功能,默认为OFF,可以通过设置为ON来开启。

  · binlog_format:指定binlog的日志格式,可选值为STATEMENT、ROW、MIXED,默认为STATEMENT。

  · binlog_cache_size:控制binlog缓冲区的大小,默认为32KB。

  · max_binlog_size:指定每个binlog文件的最大大小,默认为1GB,可以通过设置为较小的值进行拆分。

  4.2 binlog的用途 binlog在MySQL的数据恢复、数据备份、数据同步等场景中都扮演着重要的角色。具体的应用包括:

  · 数据库备份与恢复:通过备份和恢复binlog文件,可以实现对数据库的增量备份和恢复。

  · 数据同步与复制:通过读取和解析binlog,可以实现数据库之间的数据同步和复制,保持多个数据库的一致性。

  · 数据库故障恢复:通过解析binlog,可以将数据库恢复到指定的时间点,以应对因故障导致的数据丢失问题。

  · 数据审计与追踪:通过记录binlog,可以实现对数据库操作的审计和追踪,以便进行安全审计和合规性检查。

五、总结

  本文详细介绍了MySQL的binlog的底层原理,包括binlog的结构、日志格式、工作原理及相关配置和用法。binlog作为MySQL中重要的日志功能之一,不仅可以用于数据备份和恢复,还可以支持数据同步和复制等应用场景。深入理解binlog的底层原理可以更好地应用和优化数据库的相关功能。

refs

# 35岁愿你我皆向阳而生

# 深入解读Docker的Union File System技术

# 说一说注解@Autowired @Resource @Reference使用场景

# 面对“魔咒”改变才是唯一的前路

# 编写Dockerfile和构建自定义镜像的步骤与技巧

# 学习自定义Spring注解

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论