MySQL二进制日志格式类型详解

最新推荐文章于 2024-08-18 23:31:17 发布

wzqnls

最新推荐文章于 2024-08-18 23:31:17 发布

阅读量4.1k

点赞数 1

分类专栏： ★BUFF ------【Mysql】文章标签： mysql 数据库二进制日志

本文链接：https://blog.csdn.net/wzqnls/article/details/78506077

版权

本文详细介绍了MySQL的二进制日志格式，包括STATEMENT、ROW和MIXED三种类型，强调了它们在数据记录、日志量大小、主从复制安全性等方面的特点。推荐在数据安全要求高的场景下使用ROW格式，并建议设置binlog_row_image=minimal以优化记录内容。

摘要由CSDN通过智能技术生成

mysql很多有类型的日志，按照组件划分的话，可以分为 服务层日志 和 存储引擎层日志 ：
- 服务层日志：二进制日志、慢查日志、通用日志
- 存储引擎层日志：innodb(重做日志、回滚日志)

其中比较重要的就是服务器层的二进制日志，其中记录了所有对mysql数据库的修改事件，包括增删改查事件和对表结构的修改事件。要注意的一点是，只有成功执行了的事件才会记录在二进制日志中，未执行成功的不会保存在二进制日志中。

二进制日志的格式：

基于段的格式 binlog_format=STATEMENT
- 这是mysql5.7之前默认的二进制日志格式
- 记录的是mysql执行的sql语句
- 优点：
  - 日志记录量相对较小，节约磁盘及网络I/O
- 缺点：
  - 必须要记录上下文信息，保证在从服务器上执行结果和住服务器上相同
  - 对一些非确定性函数无法进行正确复制，比如UUID(), user()等
  - 可能造成mysql复制的主备服务器数据不一致

命令演示：

查看当前二进制日志记录格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

修改记录格式为statement，即改为基于段的格式

mysql> set session binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

此时可用查看格式命令来确认一下，是否修改成功

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

查看当前二进制日志信息

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000058 |       201 |
| mysql-bin.000059 |     12721 |
| mysql-bin.000060 |       201 |
| mysql-bin.000061 |       201 |
| mysql-bin.000062 |      1069 |
| mysql-bin.000063 |       201 |
| mysql-bin.000064 |       201 |
| mysql-bin.000065 |       177 |
| mysql-bin.000066 |       201 |
| mysql-bin.000067 |       177 |
| mysql-bin.000068 |       201 |
| mysql-bin.000069 |       177 |
| mysql-bin.000070 |       177 |
| mysql-bin.000071 |       201 |
| mysql-bin.000072 |       201 |
| mysql-bin.000073 |       154 |
+------------------+-----------+
16 rows in set (0.00 sec)

刷新日志，通过这个操作会产生一个新的log文件

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

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000059 |     12721 |
| mysql-bin.000060 |       201 |
| mysql-bin.000061 |       201 |
| mysql-bin.000062 |      1069 |
| mysql-bin.000063 |       201 |
| mysql-bin.000064 |       201 |
| mysql-bin.000065 |       177 |
| mysql-bin.000066 |       201 |
| mysql-bin.000067 |       177 |
| mysql-bin.000068 |       201 |
| mysql-bin.000069 |       177 |
| mysql-bin.000070 |       177 |
| mysql-bin.000071 |       201 |
| mysql-bin.000072 |       201 |
| mysql-bin.000073 |       201 |
| mysql-bin.000074 |       154 |
+------------------+-----------+
16 rows in set (0.00 sec)

现在进行一个测试操作，创建一个新的数据库及表，并插入更新一些数据。

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table t(id int, c1 varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1, 'aa'),(2, 'bb');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t set c1='dd' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这个时候可以进入存放日志的文件夹进行查看二进制日志的具体内容，
不同的操作系统存放路径不同。以ubuntu为例，路径为/var/log/mysql

刚才的操作已经写入新生成的binlog文件中，使用mysqlbinlog命令打开最新的log文件，在此文件中可以清晰的查看到我们所操作过的sql语句

# lee @ acer in /var/log/mysql [10:58:38] C:127
$ mysqlbinlog mysql-bin.000074
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170713 10:40:30 server id 1  end_log_pos 123 CRC32 0x3bcc260d  Start: binlog v 4, server v 5.7.18-0ubuntu0.16.04.1-log created 170713 10:40:30
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
nt1mWQ8BAAAAdwAAAHsAAAABAAQANS43LjE4LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQ0mzDs=
'/*!*/;
# at 123
#170713 10:40:30 server id 1  end_log_pos 154 CRC32 0x004e07fa  Previous-GTIDs
# [empty]
# at 154
#170713 10:42:47 server id 1  end_log_pos 219 CRC32 0x6d2f1e61  Anonymous_GTID  last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170713 10:42:47 server id 1  end_log_pos 313 CRC32 0x182062d1  Query   thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1499913767/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1