当前位置:Gxlcms > 数据库问题 > MySQL 5.5主从关于‘复制过滤’的深入探究

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

时间:2021-07-01 10:21:17 帮助过:23人阅读

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

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

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

 

--binlog-do-db
--binlog-ignore-db=ljk     #黑名单,这个库的修改不记录二进制日志。
该选项的行为取决于binlog格式
对于Statement-based logging:当use ljk后(即当前库为ljk时),所有的语句不被记录进binlog
当登陆mysql后不使用use或者use ljk之外的库,执行update ljk.table 依然会记录近binlog并复制[只对ljk库生效]
对于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;
    从库复制

 总结:对于主库使用binlog-ignore-db来说:只有在use db执行后才会生效

二、从库添加“replicate-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;
    从库复制
    3. Use mysql
    mysql> create table ljk (id int,name varchar(15));
    从库不复制

 总结对于从库设定replicate-ignore-db 来说,基本上只有在主库使用use db时才会生效。

三、从库添加“replicate-ignore-table = mysql.%”,主库不加过滤

 

注:这条规则加完在任何库下执行任何语句均复制;相反,在从库添加replicate-do-table = mysql.%后,在任何库下执行任何sql都不会被复制。不知道是不是bug

库操作:
    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;
    从库复制
    3. Use mysql
    mysql> create table ljk (id int,name varchar(15));
    从库复制

 总结:好像有bug,应该来说在use db后,并在对应的库下创建表应该会忽略同步。

四、 从库添加“replicate-wild-ignore-table = 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;
    从库不复制
    3. Use mysql
    mysql> create table ljk (id int,name varchar(15));
    从库不复制

 总结:使用replicate-wild-ignore-table之后,所有对于mysql库下的表级别的任何操作都被忽略同步

综上参考官方文档以及实验,可得出结论:

对于每一个添加的‘复制过滤’配置,应从两方面考虑:

    1. 不用use语句引用库,或者use xxx引用其他库之后再执行sql(又分两部分:对‘过滤的库/表’ 或 ‘对其他库/表’)会怎样

    2. use xxx引用‘过滤的库/表’,再执行sql(也分两部分:对‘过滤的库/表’ 或 ‘对其他库/表’)会怎样

除replicate-wild-do-table=/replicate-wild-ignore-table=外,其他过滤规则会受到“binlog_format”以及“当前所在库”的影响(即所谓的跨库问题)

 

 

建议使用
replicate-wild-ignore-table=mysql.%
因为replicate-ignore-db 是通过use db来确定是否过滤的。
而wild-ignore是通过真实被修改的表进行过滤的,更为准确。

实验也验证了上文提到的“对复制的过滤,采用replicate-wild-do-table/ replicate-wild-ignore-table,比较严格和明确

本文永久更新链接地址:http://www.linuxidc.com/Linux/2015-10/124181.htm

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

标签:临时表   lsp   idt   导致   函数   建表   ddl   tab   主从   

人气教程排行