阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

198次阅读
没有评论

共计 4831 个字符,预计需要花费 13 分钟才能阅读完成。

关于 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,比较严格和明确

同时,我们先说下根据以上理论以及下面实验得出的结论:

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

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

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

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

 

下面是实验过程(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”,主库不加过滤

库操作
    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-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));
    从库复制
 

四、从库添加“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));
    从库不复制

实验也验��了上文提到的结论。

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4831字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中