MySQL之binlog_row_image参数

本文通过实验对比了MySQL中binlog_row_image设置为full和minimal时binlog日志量的差别。结果显示,将参数设置为minimal可以显著减少binlog日志量,从而节省磁盘空间、内存开销和网络流量,减小了93.83%。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

根据文档描述,在基于RBR的复制中,每一个数据行更改事件会包含两份影像(image)。
一份是“before”影像,是指被更改的数据行的那些列的原始记录,一份是“after”影像,是指被更改后的列(值)。
默认配置下,mysql会在“before”和“after”影像中记录更改数据行的所有列。
但是很明显,这不是必须的。
其实我们可以通过配置binlog_row_image参数来节省磁盘,内存以及网络资源。
因为其实仅仅只需要记录我们更改的那些列(值)。


当删除一条数据行时,仅仅只有“before”影像。
当插入一条数据行时,仅仅只有“after”影像。
所以只有当更新一条数据行时,才会同时记录“before”影像和“after”影像。


而对于“before”影像,其实仅仅需要记录我们更改的列值,以及能够唯一识别这些数据行的列值(比如主键)即可。


参数binlog_row_image有如下配置:
full:在“before”和“after”影像中,记录所有的列值;
minimal:在“before”和“after”影像中,仅仅记录被更改的以及能够唯一识别数据行的列值;
noblob:在“before”和“after”影像中,记录所有的列值,但是BLOB 与 TEXT列除外(如未更改)。

默认值是full。


下面试验看看full和minimal的binlog日志量的区别:

C:\>net start mysql57_3306
MySQL57_3306 服务正在启动 ..
MySQL57_3306 服务已经启动成功。


C:\>mysql -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> select @@global.binlog_format,@@session.binlog_format;
+------------------------+-------------------------+
| @@global.binlog_format | @@session.binlog_format |
+------------------------+-------------------------+
| ROW                    | ROW                     |
+------------------------+-------------------------+
1 row in set (0.00 sec)


mysql> select @@global.binlog_row_image,@@session.binlog_row_image;
+---------------------------+----------------------------+
| @@global.binlog_row_image | @@session.binlog_row_image |
+---------------------------+----------------------------+
| FULL                      | FULL                       |
+---------------------------+----------------------------+
1 row in set (0.00 sec)


mysql> select @@global.max_binlog_cache_size;
+--------------------------------+
| @@global.max_binlog_cache_size |
+--------------------------------+
|                      134217728 |
+--------------------------------+
1 row in set (0.00 sec)


mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 194
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-7371
1 row in set (0.00 sec)


mysql> UPDATE cms.`b_business_record` tl
    -> SET tl.`projectName` = 'test'
    -> WHERE tl.`id` IS NOT NULL;
Query OK, 63 rows affected (1.41 sec)
Rows matched: 63  Changed: 63  Warnings: 0


mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 38602    #在full下,日志量从194字节增大到38602字节,增加了38602 - 194 = 38408字节
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-7372
1 row in set (0.00 sec)


mysql> set @@session.binlog_row_image='minimal';
Query OK, 0 rows affected (0.00 sec)


mysql> select @@global.binlog_row_image,@@session.binlog_row_image;
+---------------------------+----------------------------+
| @@global.binlog_row_image | @@session.binlog_row_image |
+---------------------------+----------------------------+
| FULL                      | MINIMAL                    |
+---------------------------+----------------------------+
1 row in set (0.00 sec)


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


mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000032
         Position: 194
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-7373
1 row in set (0.00 sec)


mysql> UPDATE cms.`b_business_record` tl
    -> SET tl.`projectName` = 'sss'
    -> WHERE tl.`id` IS NOT NULL;
Query OK, 63 rows affected (0.09 sec)
Rows matched: 63  Changed: 63  Warnings: 0


mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000032
         Position: 2564  #而在minimal下,binlog日志量只增大了 2564 - 194 = 2370字节
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-7374
1 row in set (0.00 sec)


mysql>


经过测试,minimal相比full,binlog日志量减少了93.83%。

这在节约磁盘空间和内存开销以及网络流量上,确实有着明显的优势。

<think>我们正在讨论MySQL主从复制使用GTID时的配置问题,特别是关于binlog_format的设置。根据用户的问题,他想知道在使用GTID时是否必须设置binlog_format为ROW,以及完整的配置要求。 首先,回顾一下GTID复制的基本要求: - GTID模式(gtid_mode=ON) - 强制GTID一致性(enforce_gtid_consistency=ON) - 主库和从库的server_id必须唯一 关于binlog_format,根据MySQL官方文档,GTID复制并不强制要求binlog_format必须为ROW。实际上,GTID复制可以支持STATEMENT、ROW和MIXED三种格式。但是,官方推荐使用ROW格式,原因如下: 1. 数据一致性:ROW格式基于行复制,可以避免一些非确定性语句(如使用随机数或系统时间)导致的主从不一致问题。 2. 安全性:在MIXED模式下,当遇到非确定性语句时,MySQL会自动切换到ROW格式,但这可能导致复制中断或错误。而直接使用ROW格式可以避免这种切换带来的风险。 3. 与GTID的兼容性:虽然GTID支持STATEMENT和ROW,但STATEMENT模式在复制某些特殊语句时(如使用临时表)可能会出现问题。 然而,必须注意的是,在GTID复制中,如果使用STATEMENT或MIXED格式,需要确保所有事务都是确定性的,否则可能导致复制错误。因此,为了确保复制的稳定性和数据一致性,推荐使用ROW格式。 接下来,我们提供完整的GTID复制配置步骤,包括主库和从库的配置: ### 主库配置(Master) 1. 修改配置文件(如my.cnf): ```ini [mysqld] server_id=1 # 必须唯一 log_bin=mysql-bin # 启用二进制日志 gtid_mode=ON # 开启GTID模式 enforce_gtid_consistency=ON # 强制GTID一致性 binlog_format=ROW # 推荐设置为ROW,但不是强制要求 expire_logs_days=7 # 可选,设置日志过期时间 ``` 2. 重启MySQL服务使配置生效。 3. 创建复制用户: ```sql CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; ``` ### 从库配置(Slave) 1. 修改配置文件(如my.cnf): ```ini [mysqld] server_id=2 # 必须唯一,且不同于主库 relay_log=mysql-relay-bin # 中继日志 read_only=ON # 建议设置为只读 gtid_mode=ON enforce_gtid_consistency=ON binlog_format=ROW # 推荐与主库一致 ``` 2. 重启MySQL服务。 ### 初始化数据同步 1. 在主库使用mysqldump备份数据(注意使用--master-data选项): ```bash mysqldump -u root -p --all-databases --master-data --single-transaction --gtid-purged=AUTO > full_backup.sql ``` - `--master-data`:会记录备份时对应的binlog位置,但在GTID模式下,我们主要使用GTID。 - `--gtid-purged=AUTO`:自动处理GTID信息,确保从库不会重复执行已包含在备份中的事务。 2. 将备份文件复制到从库并导入: ```bash mysql -u root -p < full_backup.sql ``` ### 启动复制 在从库执行: ```sql CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_AUTO_POSITION=1; # 启用GTID自动定位 START SLAVE; ``` ### 验证复制状态 ```sql SHOW SLAVE STATUS\G ``` 检查: - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Retrieved_Gtid_Set: 显示从主库接收的GTID集合 - Executed_Gtid_Set: 显示从库已执行的GTID集合 ### 结论 - **GTID复制不强制要求binlog_format=ROW**,但强烈推荐使用ROW格式以确保数据一致性和复制稳定性。 - 如果使用STATEMENT或MIXED格式,需要确保所有事务都是确定性的,否则可能导致复制错误。 ### 注意事项 - 在配置GTID复制前,确保所有服务器版本支持GTID(MySQL 5.6及以上版本)。 - 如果从传统复制切换到GTID复制,需要按照特定步骤进行,避免数据不一致。 §§相关问题§§ 1. 为什么在GTID复制中推荐使用ROW格式的二进制日志? 2. 如何将现有的非GTID主从复制迁移到GTID模式? 3. 在GTID复制中,如何跳过特定的错误事务? 4. GTID复制相对于传统复制有哪些优势?</think>### MySQL GTID复制中`binlog_format`配置详解 #### 核心结论 **GTID复制不强制要求`binlog_format=ROW`**,但**强烈推荐使用ROW格式**。以下是完整配置说明: --- ### 一、GTID与`binlog_format`关系 | 复制模式 | 支持GTID | 推荐等级 | 风险说明 | |----------------|----------|----------|----------| | **ROW** | ✅ 完全支持 | ★★★★★ | 最安全,数据一致性最高[^3] | | **MIXED** | ✅ 支持 | ★★★☆☆ | 非确定性语句自动转ROW,有切换风险 | | **STATEMENT** | ⚠️ 有限支持 | ★☆☆☆☆ | 易导致主从不一致,不推荐[^1] > **关键说明**: > 1. GTID本身不依赖binlog格式,但**STATEMENT模式在复制非确定性语句(如`RAND()`/`NOW()`)时会导致数据不一致** > 2. 官方文档明确建议:*"For best results with GTID transactions, use `binlog_format=ROW`"* --- ### 二、完整GTID复制配置要求 #### 主库配置 (`my.cnf`) ```ini [mysqld] server_id = 1 # 全局唯一ID log_bin = /var/log/mysql/mysql-bin.log gtid_mode = ON # 核心GTID开关 enforce_gtid_consistency = ON # 强制GTID安全约束 binlog_format = ROW # ★ 强烈推荐设置 ★ expire_logs_days = 7 # 日志保留周期 ``` #### 从库配置 (`my.cnf`) ```ini [mysqld] server_id = 2 # 区别于主库 relay_log = /var/log/mysql/mysql-relay-bin read_only = ON # 从库写保护 gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW # 与主库一致 log_slave_updates = ON # 级联复制必需 ``` #### 必须重启的操作 ```bash # 主从库均需执行 systemctl restart mysqld ``` #### 初始化命令示例 ```sql -- 主库创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'SecurePass123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; -- 从库启动GTID复制 CHANGE MASTER TO MASTER_HOST = '192.168.1.100', MASTER_USER = 'repl', MASTER_PASSWORD = 'SecurePass123!', MASTER_AUTO_POSITION = 1; -- ★ GTID核心参数 ★ START SLAVE; ``` --- ### 三、为什么推荐ROW模式? 1. **数据一致性保障** ROW格式直接记录行变更,避免STATEMENT模式下函数/触发器导致的主从差异[^3] 2. **冲突解决优势** 当复制出错时,ROW格式的二进制日志可直接用于数据修复: ```sql SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 12345; ``` 3. **GTID特性兼容** ROW格式完美支持`MASTER_AUTO_POSITION=1`的自动故障转移 4. **空间效率优化** 虽然ROW日志较大,但配合`binlog_row_image=MINIMAL`可减少30-50%日志量: ```ini binlog_row_image = MINIMAL # 只记录变更列 ``` --- ### 四、特殊场景处理 #### 若必须使用STATEMENT模式 1. 需严格限制: ```sql SET GLOBAL enforce_gtid_consistency=ON; ``` 2. 禁止使用: - `CREATE TABLE ... SELECT` - 非确定性函数(`UUID()`, `RAND()`等) - 临时表操作 #### MIXED模式注意事项 当检测到非确定性语句时自动切换ROW格式,但可能造成: - 日志量突增 - 复制延迟波动 - 格式切换失败风险(需监控`SHOW SLAVE STATUS`) --- ### 五、验证配置 ```sql -- 检查GTID状态 SHOW GLOBAL VARIABLES LIKE 'gtid_mode'; /* 正确输出: gtid_mode | ON */ -- 验证复制格式 SELECT @@global.binlog_format; /* 推荐输出: ROW */ -- 监控复制错误 SHOW SLAVE STATUS\G /* 关键指标: Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Error: Last_SQL_Error: */ ``` > 通过以上配置,GTID复制可自动管理事务位置,无需手动跟踪`binlog_file`和`binlog_pos`[^2]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值