MySQL主从备库过滤参数分析和测试

测试环境:

GTID的主从复制,主库(9900)——》备库(9909),存在测试库表:

9900_db1库:t1、t2、t3、t4、t5表

9900_db2库:t6、t7、t8、t9、t10表

1、replicate-do-db参数:

–replicate-do-db=name,只同步指定的数据库,可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB来创建,比如现在只同步9900_db1库,需要在从库执行:

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_do_db=(9900_db1); //(9900_db1,…)可指定多个

Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.04 sec)

也可以写入到参数文件永久生效(若要指定多个,需要多次指定该参数)。

此时在主库9900_db2库中的表插入数据的话,将不会同步到备库,在9900_db1库中的表插入数据的话,是可以同步到备库的。

但是需要注意的如下:

①该参数不能再group replication架构中使用,因为可能会使组无法达到一致的状态。

②在binlog为statement格式下(row不会有下面问题):

sql线程会将replicate-do-db限制的数据库为use使用的数据库。

如果在主库执行如下操作,备库将不会复制(不单单是insert,这里只是用insert举例,只要涉及到复制库和未复制库的跨库操作就不会复制):

mysql> use 9900_db2;

Database changed

mysql> insert into 9900_db1.t1(name) values(‘gg’);

Query OK, 1 row affected (0.63 sec)

产生这种行为的原因是:仅通过语句很难判断是否该复制(比如跨库update和delete多个表),如果没有必要,只检查默认库比检查所有库要快。

③在binlog为row格式下,以下操作不会同步到备库:

mysql> use 9900_db1;

Database changed

mysql> insert into 9900_db2.t6(name) values(‘gg’);

Query OK, 1 row affected (0.50 sec)

这个也很好理解,因为没有同步9900_db2即便跨同步的库也没用。

总之,在statement格式下的binlog,只要涉及到同步库和未同步库的跨库操作就不会同步;在row格式下的binlog只要涉及到未同步库的操作,都不会同步。

④以下操作在statement格式下和row格式下有区别:

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1,9900_db2.t6 set 9900_db1.t1.name=’aaa’,9900_db2.t6.name=’bbb’ where 9900_db1.t1.name=’aa’ and 9900_db2.t6.name=’bb’;

Query OK, 2 rows affected (3.23 sec)

Rows matched: 2 Changed: 2 Warnings: 0

使用的是replicate-do-db指定的库,在binlog为statement格式下会同步到备库,如果不是使用的replicate-do-db库的话将不会同步。

使用的是replicate-do-db指定的库,在binlog为row格式下不论是不是使用的replicate-do-db库,都会只同步9900_db1的操作。

总之,修改操作涉及到同步的库和未同步的库的时候,在binlog为statement格式下如果使用的是(这里使用指的是use dbname)同步的库操作都会同步到备库,如果使用未同步的库的话操作不会同步;在row格式的binlog下无论使用的哪个库都只会同步,replicate-do-db指定的库的操作。

2、replicate-ignore-db参数:

–replicate-ignore-db=name,不同步指定的数据库,可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB来创建,和replicate-do-db一样,不过多解释了。

这里CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(9900_db1);

需要注意的如下:

①该参数不能再group replication架构中使用,因为可能会使组无法达到一致的状态。同replicate-do-db。

②在binlog为statement格式下,只要use的是replicate-ignore-db的数据库那么将不会复制到备库,use的其他数据库是可以复制到备库的,比如如下操作是可以被复制的,因为use的不是replicate-ignore-db库:

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’; //因为这里是显示指定的9900_db1库基于statement格式下不会进行过滤。

Query OK, 1 row affected (1.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

在binlog为row格式下,无论use的是不是ignore的数据库,都不会更新ignore库中的表。

其实和replicate-do-db大同小异只不过这里是不同步的库。

对于上面的④步中的:

如果为statement格式的话,use的是ignore库,那么不会同步ignore库中的表,其他的表可以同步;如果use的不是ignore库,那么都会同步。

如果为row格式的话,无论use的是不是ignore的库,都不会同步ignore库中的表的操作。

3、replicate-do-table参数:

–replicate-do-table=db_name.tbl_name,只同步指定的表,可以使用CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(9900_db1.t1,9900_db2.t6);进行过滤,也可写入到参数文件,如果要过滤多个表的话,需要指定多次该参数。

测试指定的是只同步9900_db1.t1,9900_db2.t6这两个表,replicate-do-table指定该参数后不管use的是哪个数据库都会进行同步(和binlog格式无关),测试如下:

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2; //使用其他数据库也是如此

Database changed

mysql> update 9900_db1.t1 set name=’aa’ where name=’a’;

Query OK, 1 row affected (1.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新,也是无论use哪个数据库都会进行更新(不分binlog格式)。当更新操作中既包含db table表又包含不能同步的表时,statement格式时都会同步到备库,但是如果备库不存在其他表的话主从将会报错(与use数据库无关);row格式的时候备库只同步db table的表(与use数据库无关)。

4、replicate-ignore-table参数:

–replicate-ignore-table=db_name.tbl_name,不同步指定的表,可以使用CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(9900_db1.t1,9900_db2.t6);进行过滤,也可写入到参数文件,如果要过滤多个表的话,需要指定多次该参数。

测试指定的是不同步9900_db1.t1,9900_db2.t6这两个表,replicate-ignore-table指定该参数后不管use的是哪个数据库都不会进行同步,测试如下:

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.17 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新,也是无论use哪个数据库备库都不会进行更新(不分binlog格式)。对于操作中包含既然包含ignore table表又包含可以同步的表的时候,那么在statement格式下replicate-ignore-table指定的表和可以同步的表都不会同步(与use数据库无关);在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的(与use数据库无关)。

5、replicate-wild-do-table参数:

–replicate-wild-do-table=db_name.tbl_name,只同步指定的表,可以包含%和_通配符,它们与LIKE模式匹配操作符的含义相同。可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘9900_db1.t1′,’9900_db2.t6’)进行过滤(注意库名表名的引号),也可以写入参数文件如果不使用通配符等的话,想要同步多个表,需要在参数文件写多次。

测试指定的是只同步9900_db1.t1,9900_db2.t6这两个表,replicate-wild-do-table指定该参数后不管use的是哪个数据库都会进行同步(和binlog格式无关),测试如下:

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2; //使用其他数据库也是如此

Database changed

mysql> update 9900_db1.t1 set name=’aa’ where name=’a’;

Query OK, 1 row affected (1.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新,也是无论use哪个数据库都会进行更新(不分binlog格式)。当更新操作中既包含db table表又包含不能同步的表时,statement格式时都会同步到备库,但是如果备库不存在其他表的话主从将会报错(与use数据库无关);row格式的时候备库只同步db table的表(与use数据库无关)。

replicate-wild-do-table和replicate-do-table效果是一样的,不同的是replicate-wild-do-table可以使用通配符等。

该选项适用于表、视图和触发器,不适用于存储过程和函数等事件。

例如:replication -wild-do-table=foo%.Bar%.只复制使用数据库名称以foo开头、表名称以Bar开头的表的更新。如果表名模式是%,那么它将匹配任何表名。

该选项不适用隐式更新表,比如授权语句grant会隐式更新mysql.user表,备库不受该参数限制。

6、replicate-wild-ignore-table参数:

–replicate-wild-ignore-table=db_name.tbl_name,不同步指定的表,可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=(‘9900_db1.t1′,’9900_db2.t6’);进行过滤,也可写入到参数文件,如果要过滤多个表的话,需要指定多次该参数。

测试指定的是不同步9900_db1.t1,9900_db2.t6这两个表,replicate-wild-ignore-table指定该参数后不管use的是哪个数据库都不会进行同步( 和binlog格式无关),测试如下:

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.17 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新,也是无论use哪个数据库备库都不会进行更新(和binlog格式无关)。对于操作中包含既然包含ignore table表又包含可以同步的表的时候,那么在statement格式下replicate-wild-ignore-table指定的表和可以同步的表都不会同步(与use数据库无关);在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的(与use数据库无关)。

因为row格式的binlog中将语句拆分了两个:

MySQL主从备库过滤参数分析和测试

replicate-wild-ignore-table和replicate-ignore-table效果是一样的,不同的是replicate-wild-ignore-table可以使用通配符等。

例如:replication-wild-ignore-table=foo%.Bar%.不会复制使用数据库名称以foo开头、表名称以Bar开头的表的更新。如果表名模式是%,那么它将匹配任何表名。

该选项不适用隐式更新表,比如授权语句grant会隐式更新mysql.user表,备库不受该参数限制。如果需要过滤掉GRANT语句或其他管理语句,可能的解决方法是使用–replication-ignore-db参数。

例如:

USE nonreplicated;

GRANT SELECT, INSERT ON replicated.t1 TO ‘someuser’@’somehost’;

上面的语句序列会导致GRANT语句被忽略。

7、replicate-rewrite-db:

–replicate-rewrite-db=from_name->to_name将主库的数据库名from_name转换为从库的to_name,要指定多个需要多次使用该参数,或者写入参数文件,例如:

mysqld –replicate-rewrite-db=”olddb->newdb”

也可以通过CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB来创建。

这里使用主库的9900_db1和从库的test:CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((9900_db1,test));

对于binlog为statement和row格式该参数的效果是不同的:

①statement格式下,需改语句中指定库名(比如update dbname.tblname)这样的话无论use的哪个库都不会同步到test库(如果备库也有和主库同名的库9900_db1,那么会同步到该库),如果语句中不指定库名,use 参数指定的主库的库名(也就是9900_db1)的话直接写表名更新是可以同步到备库test库的。

②row格式下,无论use的是哪个库,也无论更新的时候有没有指定库名,都会同步到备库的test库,而如果备库中9900_db1库也不会同步。

DDL的话不论binlog格式如何,都是根据use的库来进行的,也就是说,指定库名(比如update dbname.tblname)这样的话无论use的哪个库都DDL不会同步到test库(如果备库也有和主库同名的库9900_db1,那么会同步到该库),有可能sql thread会报错;如果语句中不指定库名,use 参数指定的主库的库名(也就是9900_db1)的话直接写表名更新是可以同步到备库test库的。

为确保重写产生预期的结果,尤其是与其他复制筛选选项结合使用时,请在使用选项–replicate-rewrite-db时遵循以下建议:

①在源和具有不同名称的副本上手动创建from_name 和 to_name 数据库。

②如果您使用基于语句的或混合的二进制日志记录格式,请不要使用跨数据库查询,也不要在查询中指定数据库名称。对于 DDL 和 DML语句,都依赖use语句指定的当前数据库,并且在查询中仅使用table名

③如果仅对 DDL 语句使用基于row格式的binlog,依靠use语句指定当前数据库,并且在查询中使用table名,对于DML语句,可以根据需要使用完全限定的table名(db.table)。

如果遵循这些 建议,则可以安全的将–replicate-rewrite-db选项与table级复制过滤选项(例如–replicate-do-table)结合使用。

Original: https://www.cnblogs.com/ordinarydba/p/16424501.html
Author: DB文档搬运工
Title: MySQL主从备库过滤参数分析和测试

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/621134/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • MySQL事务

    事务用于保证数据的一致性,由一组相关的dml语句组成,该组的dml语句要么全部成功后提交,要么全部失败。进行一个事物操作时,也可以防止其它用户修改表的数据。 举个例子:转账这个操作…

    数据库 2023年6月16日
    089
  • API开放平台网关需要做什么?

    首发于公众号:BiggerBoy欢迎关注,查看更多技术文章 怎么搭建API开放平台网关? API的全称是应用编程接口(Application Programming Interfa…

    数据库 2023年6月11日
    0102
  • 软件测试流程是什么?这题我不会啊

    转载请注明出处❤️ 作者:测试蔡坨坨 原文链接:caituotuo.top/ae4fff18.html 你好,我是测试蔡坨坨。 最近收到不少准备转行软件测试的小伙伴私信问真实企业里…

    数据库 2023年6月11日
    0107
  • Java基础四—泛型、注解、异常、反射

    泛型 泛型的本质是为了参数化类型( 在不创建新的类型的情况下,通过泛型指定的不同类型来控制形参具体限制的类型)。也就是说在泛型使用过程中,操作的数据类型被指定为一个参数,这种参数类…

    数据库 2023年6月6日
    089
  • 360浏览器兼容模式下jsp页面访问不到js文件

    360浏览器兼容模式下jsp页面访问不到js文件 查看自己js中的语法问题,不要用ES6的语法,编译不了故找不到js文件 const var of 码出高效 java 所有整型包装…

    数据库 2023年6月11日
    071
  • element-ui 提示框 确认按钮在左 取消按钮在右

    添加 //取消按钮 样式 cancelButto…

    数据库 2023年6月16日
    077
  • 1480. 一维数组的动态和

    给你一个数组 nums 。数组「动态和」的计算公式为:runningSum[i] = sum(nums[0]…nums[i]) 。 请返回 nums 的动态和。 示例 …

    数据库 2023年6月16日
    0110
  • Excel中VLOOKUP函数的用法

    一、VLOOKUP函数的作用 作用 :VLOOKUP函数可以帮助我们在已有的内容中快速匹配到我们想要的结果 二、VLOOKUP函数的参数及用法实例 VLOOKUP函数有四个参数:V…

    数据库 2023年6月11日
    081
  • leetcode 235. Lowest Common Ancestor of a Binary Search Tree 二叉搜索树的最近公共祖先(简单)

    一、题目大意 给定一个二叉搜索树, 找到该树中两个指定节点的最近公共祖先。 百度百科中最近公共祖先的定义为:”对于有根树 T 的两个结点 p、q,最近公共祖先表示为一个…

    数据库 2023年6月16日
    0103
  • 测试计划

    ​ 1.测试计划的定义:描述需要完成的所有工作,包括被测项目的目的、背景、范围、资源、进度、环境、任务、策略,以及相应的风险和措施。 ​ 2.测试计划的作用: 对后面的测试过程起到…

    数据库 2023年6月16日
    078
  • MYSQL基础学习笔记

    关系型数据库与非关系型数据库 1、关系型数据库(SQL):主要依据”一对一、一对多、多对多”等关系模型来创建的数据库。 2、非关系型数据库(NoSQL):主…

    数据库 2023年5月24日
    077
  • 「萌新指南」SOA vs. 微服务:What’s the Difference?

    实话实说,在我还没有实习之前,我是连 SOA 是啥都不知道的,只听说过微服务,毕竟微服务实在太火了,想不知道都难,我觉得实习的时候肯定也是微服务,进组之后发现是 SOA 架构,当时…

    数据库 2023年6月6日
    0109
  • Word书签替换,加盖电子印章及转换PDF(Java实用版)

    一、前言 在项目中有需要对word进行操作的,可以看看哈,本次使用比较强大的spire组件来对word进行操作,免费版支持三页哦,对于不止三页的word文件,可以购买收费版,官网:…

    数据库 2023年6月16日
    091
  • Java 全栈知识体系(2021 PDF 版本)

    40000 +字长文总结,民工哥已将此文整理成PDF文档了,需要的见文后下载获取方式。 全栈知识体系总览 Java入门与进阶 面向对象与Java基础 Java 基础 –…

    数据库 2023年6月9日
    075
  • gh-ost使用问题记录

    因为 pt-osc 对数据库性能影响较大,且容易造成死锁问题,目前我们在线更改表结构都使用 gh-ost 工具进行修改,这里记录一下使用 gh-ost 过程中的问题,以作记录;首先…

    数据库 2023年6月9日
    081
  • MySQL实战45讲 12

    12 | 为什么我的MySQL会”抖”一下? 一条 SQL 语句,正常执行的时候特别快,但是 有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现…

    数据库 2023年5月24日
    080
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球