（4.7）mysql备份还原——深入解析二进制日志（3）binlog的三种日志记录模式详解

 关键词：binlog模式，binlog，二进制日志，binlog日志

 

目录概述

0、binlog概述

　　查看binlog日志参数设置： show variables like '%log_bin%';

　　查看binlog文件事件信息：show binlog events in 'binlog.000003';

　　设计到3个参数：

　　（1）binlog_format='row' -- (row,statement,mixed)

　　　　日志记录模式，行、语句、混合　　　

　　　　（1.1）statement：基于语句的复制（5.5.6之前默认），主服务器执行的SQL语句，在从服务器执行同样的语句

 

　　　　（1.2）row：基于行的复制（5.5.7之后默认），把改变的内容复制到从库，而不是把SQL命令在从库重新执行一遍。mysql5.0就开始支持

 

　　　　（1.3）mixed：混合类型的复制，默认是使用 statement 语句方式复制，一旦发现基于语句无法精确复制时（比如now() 因为主从有延迟导致数据不一致）就会采用基于 row 行的方式复制。

 

　　（2）binlog_row_image=full -- (full,minimal,noblob)

　　　　如果是用行的话，记录全部的记录、最小的记录、不记录二进制

　　　　（2.1）full：记录所有的列字段新旧字段记录（即使那一列没有更新到） -- insert/update语句的set部分是全部的新记录，delete/update的where部分是全部的旧记录

 　　　  （2.2）minimal：只记录被更新到的新旧字段记录　　--  insert/update语句的set部分是全部的新记录，update语句只有修改的列内容，update/delete的where部分没有记录（如果是主键或者唯一索引则会记录）

　　　　（2.3）noblob：二进制字段，不记录，其余的与minimal相同

　　（3）binlog_rows_query_log_events=on -- (on,off)

　　　　为off时，binlog中的记录，dml里面写的是行记录且加密，而不是SQL语句，DDL是明文。

　　　　为on时，dml里面写的是SQL语句+行记录详细信息，DDL是明文。

　　　　

 

[0.1]、binlog_format='statement'

【1.3】总结（对主从的影响）
（1）这种方式下，使用RC、RUC隔离级别会报错。
（2）DDL以及DML都是明文按SQL记录存储。
（3）对有些参数，在不同的服务器和不同的时间，执行的结果不一样，会导致主从不一致。比如currment_date,timestamp。
　　特别是一些函数：uuid(),user(),时间函数now()
（4）性能问题：比如主库有一条慢SQL执行了，也会去从库执行
（5）数据异常：主从数据不一致，执行也会有问题

[0.2]、binlog_format='row'

binlog_row_image=full;
binlog_rows_query_log_events=off;-- 显示较为详细的DML信息，明文DDL,加密DML

【2.3】总结
（1）ddl操作时明文，而dml操作时加密（dml里面写的是行记录，而不是SQL语句）
（2）针对加密的操作反解析加参数-v
mysqlbinlog --base64-output=decode-rows -v binlog.000004
（3）对复制的影响
　　a.同步最安全
　　b.不管是更新还是删除，或者批量操作数据，都是按行记录，依次处理所有行的记录，而不是SQL。
(4)查看事件（ddl为sql明文，dml为加密记录）
show binlog events in 'binlog.000004';

[0.3]、binlog_format='row'

binlog_row_image=full;
binlog_rows_query_log_events=on; --显示SQL语句+更详细（数据类型）的DML信息　

【3.3】总结
（1）ddl操作时明文，而dml操作时加密（dml里面写的是SQL语句+行记录详细信息）
（2）针对加密的操作反解析加参数-v，加2个-v
mysqlbinlog --base64-output=decode-rows -v -v binlog.000005
（3）对复制的影响
　　a.同步最安全
　　b.不管是更新还是删除，或者批量操作数据，都是按行记录，依次处理所有行的记录，而不是SQL。
(4)查看事件（ddl为sql明文，dml为加密记录）
show binlog events in 'binlog.000005';

 

[0.4]、binlog_format='mixed'

　　理解了row与statement模式以后，mixed=statement和row

　　大多数情况下是以statement模式记录，如果隔离级别为RC，那么会以row模式记录

　　混合类型的记录，默认是使用 statement 语句方式记录，一旦发现基于语句无法精确记录时（比如now() 因为主从有延迟导致数据不一致）就会采用基于 row 行的方式复制。

 

 

 

1、实测

查看binlog日志参数设置： show variables like '%log_bin%';

查看binlog文件事件信息：show binlog events in 'binlog.000003';

　　

 

涉及到3个参数

　　（1）binlog_format='row' -- (row,statement,mixed)

　　　　日志记录模式，行、语句、混合

　　（2）binlog_row_image=full -- (full,minimal,noblob)

　　　　如果是用行的话，记录全部的记录、最小的记录、不记录二进制

　　　　（2.1）full：记录所有的列字段新旧字段记录（即使那一列没有更新到） -- insert/update语句的set部分是全部的新记录，delete/update的where部分是全部的旧记录

 　　　  （2.2）minimal：只记录被更新到的新旧字段记录　　--  insert/update语句的set部分是全部的新记录，update语句只有修改的列内容，update/delete的where部分没有记录（如果是主键或者唯一索引则会记录）

　　　　（2.3）noblob：二进制字段，不记录，其余的与minimal相同

　　（3）binlog_rows_query_log_events=on -- (on,off)　　

　　　    为off时，binlog中的记录，dml里面写的是行记录且加密，而不是SQL语句，DDL是明文。

　　　　为on时，dml里面写的是SQL语句+行记录详细信息，DDL是明文。

 

1、binlog_format='statement'

【1.3】总结（对主从的影响）
（1）这种方式下，使用RC、RUC隔离级别会报错。
（2）DDL以及DML都是明文按SQL记录存储。
（3）对有些参数，在不同的服务器和不同的时间，执行的结果不一样，会导致主从不一致。比如currment_date,timestamp。
　　特别是一些函数：uuid(),user(),时间函数now()
（4）性能问题：比如主库有一条慢SQL执行了，也会去从库执行
（5）数据异常：主从数据不一致，执行也会有问题

【1.1】准备工作
-- 修改binlog记录模式
set session binlog_format='statement';
set global binlog_format='statement';
select @@global.binlog_format,@@binlog_format;

-- 修改隔离级别为重复度
set global tx_isolation='repeatable-read'; -- 旧的设置方法
set global transaction_isolation='repeatable-read'; -- 新的设置方法
set session transaction_isolation='repeatable-read';
select @@global.transaction_isolation,@@transaction_isolation;

-- 刷新binlog日志
show master status;
flush logs;
show master status;

【1.2】 测试
-- 建表
create table test2(id int primary key not null auto_increment,
tablename varchar(200),UUID varchar(50),
timepoint datetime not null default current_timestamp,
currentversion timestamp not null default current_timestamp on update current_timestamp
)engine=innodb ;

-- 插入数据
insert into test2(tablename,UUID) select 'test2',uuid();
-- 更新数据
update test2 set tablename='test2_update' where id = 1;
-- 提交
commit;
-- 查看状态
show master status; -- 会发现position有了变化，之前是154，现在是2125
-- 退出mysql,进入binlog目录，使用mysqlbinlog 查看binlog信息
mysqlbinlog --start-position=154 --stop-position=2125 binlog.000002

【1.3】总结（对主从的影响）
（1）这种方式下，使用RC、RUC隔离级别会报错。
（2）DDL以及DML都是明文按SQL记录存储。
（3）对有些参数，在不同的服务器和不同的时间，执行的结果不一样，会导致主从不一致。比如currment_date,timestamp。
　　特别是一些函数：uuid(),user(),时间函数now()
（4）性能问题：比如主库有一条慢SQL执行了，也会去从库执行
（5）数据异常：主从数据不一致，执行也会有问题
（6）查看Binlog事件
show binlog events in 'binlog.000003';
　　

　　

 　　

 

2、binlog_format='row'

binlog_row_image=full;
binlog_rows_query_log_events=off;-- 显示较为详细的DML信息，明文DDL

【2.3】总结
（1）ddl操作时明文，而dml操作时加密（dml里面写的是行记录，而不是SQL语句）
（2）针对加密的操作反解析加参数-v
mysqlbinlog --base64-output=decode-rows -v binlog.000004
（3）对复制的影响
　　a.同步最安全
　　b.不管是更新还是删除，或者批量操作数据，都是按行记录，依次处理所有行的记录，而不是SQL。
(4)查看事件（ddl为sql明文，dml为加密记录）
show binlog events in 'binlog.000004';

【2.1】全部记录+操作事件
binlog_row_image=full;
binlog_rows_query_log_events=off; 

【2.2】测试
-- 设置模式

set session binlog_format='row';
set global binlog_format='row';
set session binlog_rows_query_log_events=off; -- 默认
set global binlog_rows_query_log_events=off; -- 默认
set global binlog_row_image=full; -- 默认
set session binlog_row_image=full; -- 默认

select @@global.binlog_format,@@binlog_format,
@@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,
@@global.binlog_row_image,@@binlog_row_image\G

--设置隔离级别为可重复读

set global tx_isolation='repeatable-read'; -- 旧的设置方法
set global transaction_isolation='repeatable-read'; -- 新的设置方法
set session transaction_isolation='repeatable-read';
select @@global.transaction_isolation,@@transaction_isolation;

-- 刷新binlog日志
show master status;
flush logs;
-- mysqladmin -uroot -p flush-logs; mysql -uroot -p -e 'flush logs';
show master status;

--建表

create table test3(id int primary key not null auto_increment,
tablename varchar(200),UUID varchar(50),
timepoint datetime not null default current_timestamp,
currentversion timestamp not null default current_timestamp on update current_timestamp
)engine=innodb ;

-- 增删改数据
insert into test3(tablename,UUID) select 'test3',uuid();
insert into test3(tablename,UUID) select 'test3',uuid();

update test3 set tablename='test3_update' where id = 1;
commit;

delete from test3 where id = 1;
commit;

drop table test3;

show master status; -- 会发现position有了变化，之前是154，现在是2125

-- 退出mysql,进入binlog目录，使用mysqlbinlog 查看binlog信息

mysqlbinlog  binlog.000004
-- mysqlbinlog --start-position=154 --stop-position=2125 binlog.000004

【2.3】总结
（1）ddl操作时明文，而dml操作时加密（dml里面写的是行记录，而不是SQL语句）
（2）针对加密的操作反解析加参数-v
mysqlbinlog --base64-output=decode-rows -v binlog.000004
（3）对复制的影响
　　a.同步最安全
　　b.不管是更新还是删除，或者批量操作数据，都是按行记录，依次处理所有行的记录，而不是SQL。
(4)查看事件（ddl为sql明文，dml为加密记录）
show binlog events in 'binlog.000004';

 

 

 

3、binlog_format='row'

binlog_row_image=full;
binlog_rows_query_log_events=on; --显示SQL语句+更详细（数据类型）的DML信息

【3.1】全部记录+详细事件
binlog_row_image=full;
binlog_rows_query_log_events=on; 

【3.2】测试
-- 设置模式
set session binlog_format='row';
set global binlog_format='row';
set session binlog_rows_query_log_events=on; -- 手动指定
set global binlog_rows_query_log_events=on; -- 手动指定
set global binlog_row_image=full; -- 默认
set session binlog_row_image=full; -- 默认
select @@global.binlog_format,@@binlog_format,
@@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,
@@global.binlog_row_image,@@binlog_row_image\G


--设置隔离级别为可重复读
set global tx_isolation='repeatable-read'; -- 旧的设置方法
set global transaction_isolation='repeatable-read'; -- 新的设置方法
set session transaction_isolation='repeatable-read';
select @@global.transaction_isolation,@@transaction_isolation;

-- 刷新binlog日志
show master status;
flush logs;
-- mysqladmin -uroot -p flush-logs; mysql -uroot -p -e 'flush logs';
show master status;

--建表
create table test4(id int primary key not null auto_increment,
tablename varchar(200),UUID varchar(50),
timepoint datetime not null default current_timestamp,
currentversion timestamp not null default current_timestamp on update current_timestamp
)engine=innodb ;

-- 增删改数据
insert into test4(tablename,UUID) select 'test4',uuid();
insert into test4(tablename,UUID) select 'test4',uuid();
update test4 set tablename='test4_update' where id = 1;
commit;

delete from test4 where id = 1;
commit;

drop table test4;

show master status; -- 会发现position有了变化，之前是154，现在是2125

-- 退出mysql,进入binlog目录，使用mysqlbinlog 查看binlog信息
mysqlbinlog  binlog.000005
-- mysqlbinlog --start-position=154 --stop-position=2125 binlog.000005

【3.3】总结
（1）ddl操作时明文，而dml操作时加密（dml里面写的是SQL语句+行记录详细信息）
（2）针对加密的操作反解析加参数-v，加2个-v
mysqlbinlog --base64-output=decode-rows -v -v binlog.000005
（3）对复制的影响
　　a.同步最安全
　　b.不管是更新还是删除，或者批量操作数据，都是按行记录，依次处理所有行的记录，而不是SQL。
(4)查看事件（ddl为sql明文，dml为加密记录）
show binlog events in 'binlog.000005';


　　（2）中：mysqlbinlog --base64-output=decode-rows -v -v binlog.000005

　　

　　（4）中：show binlog events in 'binlog.000005';  SQL语句与操作内容全部都有

　　　　

 

 

4、binlog_format='mixed'

　　理解了row与statement模式以后，mixed=statement和row

　　大多数情况下是以statement模式记录，如果隔离级别为RC，那么会以row模式记录

 

2、binlog_row_image（ full、minmal、noblob）的影响

该部分原文链接：https://blog.csdn.net/weixin_41561862/article/details/114604508

　　binlog_row_image为 full 时，表无论有没有主键约束或者唯一约束binlog都会记录所有前后镜像；

　　binlog_row_image为 minimal 时，如果表有主键或唯一索引，前镜像只保留主键列，后镜像只保留修改列；如果表没有主键或唯一索引，前镜像全保留，后镜像只保留修改列；

　　binlog_row_image为 noblob 时，如果表有主键或唯一索引，修改列为text/blob列，前镜像忽略text/blob列，后镜像包含被修改的text/blob列；

　　　　　　如果表有主键或唯一索引，修改列不是text/blob列，前后镜像忽略text/blob列。如果表没有主键或唯一索引，修改列为text/blob列 ，前后镜像全保留；如果表没有主键或唯一索引，修改列不是text/blob列，前镜像全保留，后镜像忽略text/blob列。

建议： 设置为 full ，否则无法闪回，无法后续外部应用（如果 canal、clickhouse）无法消费 binlog