大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
由于数据库 Blob字段太多,导致从库进行binlog不能正常进行的处理方法
成都创新互联公司专注于巴彦淖尔网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供巴彦淖尔营销型网站建设,巴彦淖尔网站制作、巴彦淖尔网页设计、巴彦淖尔网站官网定制、微信小程序开发服务,打造巴彦淖尔网络公司原创品牌,更为您提供巴彦淖尔网站排名全网营销落地服务。
binlog_format为row格式的时候记录的不是简单的sql,而是实际变更的行,一些大的DML操作,会导致binlog量增加很大,消耗额外的IO、网络资源
可以通过设置binlog_row_p_w_picpath=minimal解决
测试:
binlog_row_p_w_picpath默认值是full
结论:可以对比发现当binlog_row_p_w_picpath=minimal的时候binlog只记录了影响的那一行记录,有效减少了binlog日志量。
数据库版本:5.6.*
参数binlog_row_p_w_picpath 控制着这种p_w_picpath类型,默认为FULL(log all columns),即记录before&after p_w_picpaths。
该参数还有两种,minimal和noblob,minimal表示只记录after更改后的值,并且如果有主键或者非空唯一索引,则只以该字段作为where条件判断;noblob同full,只是不记录blob、text列。
对于insert则没有什么好说的,我们主要重点关注一下update和delete操作。
binlog_row_p_w_picpath=full的情况下,对于update和delete所有的表(包含带有主键、非空唯一索引,唯一索引,没有索引)产生的binlog均一致,binlog情况如下:
--建表语句
CREATE TABLE `pk_test`(
`id` bigint(20) NOT NULL,
`username` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into pk_test values (1,2);
insert into pk_test values (2,2);
commit;
show master statusG;--记录binlog文件和pos
deletefrom pk_test where id =1;
update pk_test set username='3';
commit;
MySQLbinlog --no-defaults -v --start-position=637945822/mysqllog/3307/binlog/mysql-bin.000001| more
### DELETE FROM `baofeng`.`pk_test`
### WHERE
### @1=1
### @2='2'
.....
### UPDATE `baofeng`.`pk_test`
### WHERE
### @1=2
### @2='2'
### SET
### @1=2
### @2='3'
从上面我们可以看到,在默认为FULL的binlog_row_p_w_picpath下,无论表有没有主键、唯一索引,全部按照全表字段作为条件,且update会更新全部字段。
binlog_row_p_w_picpath=minimal的情况下:
--建表语句
CREATE TABLE `ui_test`(
`id` bigint(20) NOT NULL,
`username` varchar(30) NOT NULL,
UNIQUE (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `ui_test_null`(
`id` bigint(20),
`username` varchar(30) NOT NULL,
UNIQUE key (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `null_test`(
`id` bigint(20),
`username` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into pk_test values (1,2);
insert into ui_test values (1,2);
insert into ui_test_null values (1,2);
insert into null_test values (1,2);
commit;
update pk_test set username='4';
deletefrom pk_test;
deletefrom ui_test;
deletefrom ui_test_null;
update null_test set username='4';
deletefrom null_test;
### UPDATE `baofeng`.`pk_test`
### WHERE
### @1=1
### SET
### @2='4'
....
### DELETE FROM `baofeng`.`pk_test`
### WHERE
### @1=1
.....
### DELETE FROM `baofeng`.`ui_test`
### WHERE
### @1=1
.....
### DELETE FROM `baofeng`.`ui_test_null`
### WHERE
### @1=1
### @2='2'
.....
### UPDATE `baofeng`.`null_test`
### WHERE
### @1=1
### @2='2'
### SET
### @2='4'
.....
### DELETE FROM `baofeng`.`null_test`
### WHERE
### @1=1
### @2='2'
从上面的例子可以看到,当binlog_row_p_w_picpath=minimal的情况下,where条件只有主键或不为空的唯一索引,且只会更新被改变的字段。
在上面的测试我们可以看到,如果采用minimal格式,将减少主键和非空唯一索引表的before值,以及减少所有表update的after未被改变的值。
从效率上来说,减少了网络传输以及加快了update的效率。
参考资料:
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_p_w_picpath