共计 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