引言: 之后的一段时间准备开始写一些关于mysql的文章,从实际的使用入手,介绍它的方方面面吧,主要是实际工作当中接触比较多的点。因为暑假期间数据库要做备份,涉及到binlog文件的处理,那么今天就先从这个知识点说起。MySQL的binlog(二进制日志)是一种重要的组件,用于记录数据库的操作,并以二进制格式存储。它可以捕捉数据库的插入、更新、删除等变更操作。binlog在数据恢复、备份、主从复制和故障容错等方面发挥着重要作用。本文将详细探讨MySQL binlog的底层原理,包括格式、事件、位置以及在不同场景下的重要性。
一、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%';
可以看到结果:
可以看到log_bin 的值为“OFF”,binlog日志是默认关闭的,那么如何开启呢?
开启binlog日志方式,打开mysql配置文件my.cnf,在[mysqlId]下面增加。
log-bin=mysql-bin
开启binlog日志后,重新查看下:
(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
(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注解