MySQL binlog日志基于段和语句的使用

最新推荐文章于 2024-03-21 14:33:46 发布

「已注销」

最新推荐文章于 2024-03-21 14:33:46 发布

阅读量257

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_46503776/article/details/108969183

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的binlog日志类型，包括基于语句的、基于行的和混合模式，分析了它们的优缺点。在基于语句模式下，日志记录量小但可能引发主从数据不一致；基于行模式则能有效避免数据不一致，但记录量大；混合模式在两者间取舍。

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

总纲

基于语句的日志 statement
基于行的日志 row
混合日志 mixed

通用操作步骤

查看当前日志类型
```
show variables like 'binlog_format';
```

设置日志格式为基于语句形式

基于语句
set session binlog_format=statement;

基于行
set session binlog_format=row;

查询当前的binlog日志
```
show binary logs;
```
刷新日志，生成一个新的日志文件
```
flush logs;
```
执行SQL

查看最新日志文件

基于语句查看日志
mysqlbinlog binlog.000012

基于行查看日志
mysqlbinlog -vv binlog.000012

数据初始化

创建数据库
CREATE DATABASE db_binlog;
进入测试数据库
use db_binlog;
创建表
CREATE TABLE user ( id INT ( 11 ), NAME VARCHAR ( 255 ), age INT ( 11 ));
插入数据
insert into user(id,name,age) values(1,"aaa",1),(2,"bbb",2),(3,"ccc",3);

基于语句的日志

实验步骤

设置为基于语句的日志
```
set session binlog_format=statement;
```

批量更新多条数据

刷新日志文件
flush logs;
批量更新
update user set age=6;

分析日志内容

查看日志
mysqlbinlog binlog.000017
分析日志
/*!*/;
# at 336
#201008 20:57:03 server id 1  end_log_pos 453 CRC32 0x9f208ee4 	Query	thread_id=10	exec_time=0	error_code=0
use `db_binlog`/*!*/;
SET TIMESTAMP=1602161823/*!*/;
update user set age=6
/*!*/;
结果
出现这条语句 update user set age=6

优点

日志记录量相对较小，节约磁盘和网络I/O

缺点

保证语句在其他服务器上执行结果和在主服务器上相同，必须要记录上下文信息，所以在只修改或插入一条数据是，可能比基于行的的日志量更大
特定函数例如UUID(),user()可能在其他服务器会复制失败，导致主从数据不一致

基于行的日志

三种形式

FULL：记录修改行所有的内容，无论这些列是否被修改过
MINIMAL：记录被修改的列
NOBLOB：和FULL挺像，但是如果列中text和blob没有被修改就不会记录text和blob的列

查询行日志格式
show variables like '%binlog_row_image%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

设置行日志格式
set session binlog_row_image=MINIMAL;

实验步骤

设置为基于行的日志
```
set session binlog_format=row;
```

批量更新多条数据

刷新日志文件
flush logs;
批量更新
update user set age=7;

分析日志内容

查看日志
mysqlbinlog -vv binlog.000017
分析日志
'/*!*/;
### UPDATE `db_binlog`.`user`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='aaa' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='aaa' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=7 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `db_binlog`.`user`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=7 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `db_binlog`.`user`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='ccc' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='ccc' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=7 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `db_binlog`.`user`
### WHERE
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='3934f07a-0967-11eb-bf37-000c29360040' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=5 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='3934f07a-0967-11eb-bf37-000c29360040' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3=7 /* INT meta=0 nullable=1 is_null=0 */
# at 602
#201008 21:27:31 server id 1  end_log_pos 633 CRC32 0xcf5a7646 	Xid = 145
COMMIT/*!*/;


结果
每一条数据都出现一条update语句