欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL 5.5主从关于‘复制过滤’的深入探究

程序员文章站 2022-05-30 21:03:49
...

关于MySQL主从复制的过滤,例如通过binlog-ignore-db、replicate-do-db、replicate-wild-do-table等。如果不好好研究过这些过滤选

关于MySQL主从复制的过滤,例如通过binlog-ignore-db、replicate-do-db、replicate-wild-do-table等。如果不好好研究过这些过滤选项就用的话,是有可能造成主从数据不一致问题的。本文将参考MySQL-5.5官方文档并结合实验,和各位一起探讨下这里的各个设置。

以下内容参考5.5官方文档

binlog_format(STATEMENT,ROW,MIXED,5.5默认为STATEMENT)的设置会导致一些复制执行上的差异。

当使用MIXED格式时,binlog绝大多数情况也是以STATEMENT格式记录,只有在下列情况下才会切换到ROW格式:

1、 当时用UUID()函数时

2、 当一个或多个拥有AUTO_INCREMENT列的表被更新同时有‘trigger’或者‘stored function’被调用时

# MIXED对于‘trigger’和‘stored function’总是使用statement-based

3、 执行INSERT DELAYED时

4、 当视图里的某一部分需要row-based复制(例如UUID())时,创建该视图的语句被改为row-based

5、 使用用户自定义函数(UDF)时

6、 当某语句被判定为row-based,并且执行它的session需要用到临时表,则session下的所有子语句都将以ROW格式记录

7、 当使用USER(),CURRENT_USER()或者 CURRENT_USER

8、 当语句引用了一个或多个system variables。

9、 当使用LOAD_FILE()

所有DDL语句都是基于statements,不论binlog_format如何设置

复制双方binlog_format需一致,否则复制无法进行

Binlog格式影响到以下‘复制过滤’配置的行为

--binlog-do-db
--binlog-ignore-db=ljk
该选项的行为取决于binlog格式
Statement-based logging:当use ljk后(即当前库为ljk时),所有的语句不被记录进binlog
当登陆mysql后不use/或者use ljk之外的库,执行update ljk.table 依然会记录近binlog并复制
Row-based format:告诉服务器不记录任何ljk库下表的更改,无论当前在哪个库(即无论有无use语句,是否use ljk)

--replicate-do-db = ljk
该选项的行为取决于binlog格式
Statement-based replication:只有主库在use ljk 之后执行的语句才会被从库复制
无use语句或者use其他库后执行的语句均不被复制
Row-based replication:只有ljk库的更改会被复制(无论use哪个库或者是否use)
无论是否use或use哪个库,ljk库之外的变更都不会被复制
--replicate-ignore-db


总结:Statement-based跟当前use的库有关,Row-based更直接,只关心指定的库‘做或不做’。

还有以下两种参数可‘过滤复制’
以下两种选项只对表的更改有影响,库的复制不受这些参数影响(但是类似ljk.%这种,也会对库起作用)

--replicate-do-table

这两个选项在我的实验里跟描述不太一致,详细见下文实验结果

--replicate-ignore-table

--replicate-wild-do-table = ljk.%
无论use ljk或use 其他库或不use,对ljk库的更新都能被复制,同时,其他库任何情况下均不会复制(包括建库建表操作)
--replicate-wild-ignore-table

根据以上,综合建议:对复制的过滤,采用replicate-wild-do-table/ replicate-wild-ignore-table,比较严格和明确

下面是实验过程(MySQL-5.5.39)
一、 主库添加“binlog-ignore-db = mysql”,从库不加过滤

库操作:
1. 主库不use,执行建库语句
mysql> create database kai;
从库复制
2. 主库use mysql,再执行
mysql> create database kai;
从库复制

在kai数据库执行建表操作:
1. 不use
create table kai.li (id int,name char(15));
从库复制
2. use mysql;
create table kai.li (id int,name char(15));
从库不复制
3. use 其他库;
create table kai.li (id int,name char(15));
从库复制

对表内容修改:
1. 主库不use,向li表增加数据
insert into kai.li values('1','ljk');
从库复制
2. 主库 use mysql,向li表增加数据
从库不复制
3. 主库use 其他库,更新li库(即跨库更新)
mysql> use picture;
mysql> insert into kai.li values('2','lhy');
从库复制

对mysql库进行更改:
1. 不 use
mysql> create table mysql.ljk (id int,name varchar(15));
从库复制
2. Use 其他库
mysql> drop table mysql.ljk;
从库复制

二、从库添加“replicate-ignore-db = mysql”,主库不加过滤