Binlog日志的三种模式介绍及模式修改

Binlog日志的三种模式介绍

 

一、Binlog日志的三种模式

 

1.1 Statement Level模式

 

每一条修改数据的sql都会记录到master的bin_log中slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。

 

优点:statement level下的优点首先就是解决了row level下的缺点,不需要记录每一行的变化,较少bin-log日志量,节约IO,提高性能。因为它只需要记录在master上所执行的语句的细节,以及执行语句时候的上下文信息。

 

缺点:由于它是记录执行语句,所以,为了让这些语句在slave端也能正确执行,那么它还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息,来保证所有语句在slave端能够得到和在master端相同的执行结果。由于mysql更新较快,使mysql的赋值遇到了不小的挑战,自然赋值的时候就会涉及到越复杂的内容,bug也就容易出现。在statement level下,目前就已经发现了不少情况会造成mysql的复制出现问题,主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如:sleep()函数在有些版本中就不能正确赋值,在存储过程中使用了last_insert_id()函数,可能会使slave和master上得到不一致的id等等。由于row level是基于每一行记录的裱花,所以不会出现类似的问题。

 

总结:

Statement level优点:

1、解决了row level的缺点,不需要记录每一行的变化。

2、日志量少,节约IO,从库应用日志块。

Statement level缺点:一些新功能同步可能会有障碍,比如函数、触发器等。

 

1.2 Row Level模式

 

日志中会记录成每一行数据修改的形式,然后在slave端再对相同的数据进行修改。

 

优点:在row level的模式下,bin_log中可以不记录执行的sql语句的上下文信息,仅仅只需要记录哪一条记录被修改,修改成什么样。所以row level的日志内容会非常清楚的记录每一行数据修改的细节,非常容易理解。而且不会出现某些特定情况下的存储过程,或fuction,以及trigger的调用或处罚无法被正确复制的问题

 

缺点:row level模式下,所有的执行语句都会记录到日志中,同时都会以每行记录修改的来记录,这样可能会产生大量的日志内容。

 

总结:

row level的优点:

1、记录详细

2、解决statement level模式无法解决的复制问题。

row level的缺点:日志量大,因为是按行来拆分。

 

1.3 Mixed模式(混合模式)

 

实际上就是前两种模式的结合,在mixed模式下,mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也是在statement和row之间选择一种。

 

新版本中的mysql中对row level模式也做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

 

二、调整binlog日志模式

 

binlog日志默认模式:

Server version: 5.6.17 Source distribution

mysql> show variables like "%binlog_format%";

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

 

方法一:在线修改立即生效

mysql> set global binlog_format='MIXED';

Query OK, 0 rows affected (0.00 sec)

 

退出mysql,查看当前mysql日志模式

 

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

 

方法二:在配置文件中参数如下:

[mysqld]

log-bin=/var/lib/mysql/mysql-bin

#binlog_format="ROW"

binlog_format="MIXED"   #开启MIXED模式

#binlog_format="STATEMENT"

 

修改后重启mysql服务日志模式:

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

 

三、在日志模式当前为row的模式下,记录日志的形式内容。

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

 

[root@i-3vsptbun mysql]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000011

小结:执行sql部分的sql显示为base64编码格式

#171126 17:35:53 server id 1  end_log_pos 458 Table_map: `boy`.`tomcat1` mapped to number 15

#171126 17:35:53 server id 1  end_log_pos 571 Delete_rows: table id 15 flags: STMT_END_F

### DELETE FROM `boy`.`tomcat1`

### WHERE

###   @1=11

###   @2='huang1'

### DELETE FROM `boy`.`tomcat1`

### WHERE

###   @1=2

###   @2='huang2'

### DELETE FROM `boy`.`tomcat1`

### WHERE

###   @1=3

###   @2='huang3'

###   @2='zhang7'

# at 571

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

<think>好的,用户想了解MySQL binlog三种模式及其适用场景和优缺点。首先,我需要回忆一下这三种模式的基本概念。根据提供的引用资料,STATEMENT模式记录的是SQL语句,ROW模式记录数据变更后的行,而MIXED是两者的混合。 接下来,我需要整理每种模式的特点。比如,STATEMENT的优点是日志量小,性能好,但像NOW()这种函数可能导致主从不一致。ROW模式解决了数据一致性问题,但日志量大,特别是批量操作时。MIXED试图结合两者的优点,根据情况自动选择,但可能对系统变量处理不够。 然后,考虑用户可能的场景。比如,金融系统需要严格一致性,可能用ROW;日志存储有限时用STATEMENT;而一般业务可能用MIXED。同时,优缺点对比要清晰,比如ROW的数据安全但日志大,STATEMENT性能好但有风险。 还要检查是否有遗漏点,比如ROW模式在处理批量更新时的性能问题,或者MIXED无法识别系统变量的情况。需要确保引用资料中的关键点都被涵盖,并正确标注引用来源。 最后,生成相关问题时,要围绕用户可能进一步想了解的内容,比如如何选择模式、数据恢复的影响,或者主从复制的配置建议。这样能帮助用户深入理解不同模式的应用。</think>### MySQL binlog三种格式对比及适用场景 #### 一、三种格式的核心特点 1. **STATEMENT** - **记录内容**:存储实际执行的 SQL 语句,例如 `UPDATE users SET name='Alice' WHERE id=1`[^1][^2] - **优点**: - 日志文件体积小,减少存储和传输开销 - 批量操作性能高(如 `DELETE FROM logs WHERE create_time < '2023-01-01'`) - **缺点**: - 非确定性函数(如 `NOW()`, `RAND()`)可能导致主从数据不一致 - 依赖存储过程、触发器时可能引发同步问题 2. **ROW**(默认格式) - **记录内容**:存储数据行的变更结果,例如 `UPDATE users SET name='Alice'` 会记录修改前后的整行数据 - **优点**: - 数据一致性高,完全消除非确定性函数的影响 - 支持所有类型的主从复制场景(包括存储过程、触发器) - **缺点**: - 日志体积显著增大(如 `UPDATE table SET flag=1 WHERE id>100` 会逐行记录) - 批量操作时网络传输压力大 3. **MIXED** - **记录逻辑**: - 默认使用 STATEMENT 记录 SQL 语句 - 检测到非确定性操作时自动切换为 ROW 格式 - **优点**: - 平衡日志体积与数据一致性 - 减少主从复制中的潜在风险 - **缺点**: - 无法识别系统变量(如 `@@hostname`)可能引发问题 - 需要动态判断模式,存在少量性能损耗 #### 二、适用场景对比 | 格式 | 典型场景 | 不适用场景 | |------------|--------------------------------------------------------------------------|-----------------------------------| | **STATEMENT** | 1. 日志存储空间有限<br>2. 无复杂函数/存储过程的业务场景 | 含非确定性函数的操作、金融级数据同步 | | **ROW** | 1. 金融交易系统<br>2. 主从严格一致性要求<br>3. 使用存储过程/触发器的场景 | 频繁批量更新、日志存储成本敏感 | | **MIXED** | 1. 常规业务系统<br>2. 混合使用函数与简单 SQL 的场景 | 依赖系统变量的特殊业务逻辑 | #### 三、性能与数据安全对比 $$ \text{数据安全性:ROW > MIXED > STATEMENT} $$ $$ \text{存储效率:STATEMENT > MIXED > ROW} $$ **示例场景分析**: - **电商订单支付**:使用 ROW 模式确保金额、库存等核心数据绝对一致 - **日志清理任务**:使用 STATEMENT 模式高效执行 `DELETE FROM logs WHERE id<10000` - **用户行为分析**:采用 MIXED 模式处理含 `UUID()` 函数的数据插入 #### 四、配置建议 ```sql -- 查看当前模式 SHOW VARIABLES LIKE 'binlog_format'; -- 动态修改模式(需重启生效) SET GLOBAL binlog_format = 'ROW'; ```
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

东城绝神

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值