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)

大家都在看

  • 基础知识,不是很了解的

    Java中方法参数传递方式是按值传递。对于基本类型(int a, long b),参数传递时传递的是值,例如int a = 5,传递的就是5。如果是引用类型,传递是指向具体对象内存…

    数据库 2023年6月14日
    0108
  • Linux 实用命令

    实用命令: 1、查看端口占用情况 查看该端口是&…

    数据库 2023年6月16日
    081
  • SQL的语法

    创建: create database [if not exists] 数据库名称 [default charset 字符集] [collate 排序规则]; (PS:方括号(&#…

    数据库 2023年6月16日
    092
  • MyBatis详解

    😀搭建 MyBatis mysql mysql-connector-java 8.0.29 org.mybatis mybatis 3.5.7 junit junit 4.12 t…

    数据库 2023年6月14日
    092
  • haproxy服务部署

    haproxy haproxy 一、haproyx是什么 二、负载均衡类型 三、部署haproxy 1.源码部署haproxy 2.Haproxy搭建http负载均衡 一、hapr…

    数据库 2023年6月14日
    0115
  • DELL误删raid后恢复方法

    DELL误删raid后恢复方法 一台有RAID1信息的硬盘A,一块误删的硬盘B 1.插入硬盘A和B,启动,再按Ctrl+R键,进入raid管理 发现没有硬盘信息,按F2键 选择Fo…

    数据库 2023年6月9日
    0110
  • Docker下部署Spring Boot项目

    1.编写Docker File FROM openjdk:8-jdk-slim LABEL maintainer=ddzhan COPY target/*.jar /app.jar…

    数据库 2023年6月6日
    0101
  • 优秀体现在细微处

    上面是团队内小王同学整理的需求评审计划,我们看最后一列的细节之处,即,加上了与这个需求相关的资料。 希望我们都能像上面的小王同学一样。 我们的信息,从不同角度来看,总会分散在不同的…

    数据库 2023年6月9日
    0100
  • liquibase新增字段注释导致表格注释同时变更bug记录

    liquibase是一个用于数据库变更跟踪、版本管理和自动部署的开源工具。它的使用方式方法可以参考官方文档或者其他人的博客,这里不做过多介绍。 1. 问题复现 在使用过程中发现了一…

    数据库 2023年6月14日
    0119
  • 第十四章 静态代理设计模式

    1.为什么需要代理设计模式 1.1 问题 在javaEE分层开发中,那个层对我们最重要? DAO–>Service–>Controller 在javaEE分层开发中…

    数据库 2023年6月14日
    088
  • EXCLE中SUMIF和SUMIFS的用法

    一、函数的含义 1、SUMIF函数在EXCLE中是用来对指定条件对若干单元格求和; 2、SUMIFIS函数在EXCLE中是用来对一个区域内满足多重条件的单元格求和(两个条件以上);…

    数据库 2023年6月11日
    094
  • Vue 和 Django 实现 Token 身份验证

    使用 Django 编写的 B/S 应用通常会使用 Cookie + Session 的方式来做身份验证,用户登录信息存储在后台数据库中,前端 Cookie 也会存储少量用于身份核…

    数据库 2023年6月14日
    086
  • 19-TCP、UDP的区别和应用场景

    可靠性TCP 提供交付保证,这意味着一个使用TCP协议发送的消息是保证交付给客户端的,如果消息在传输过程中丢失,那么它将重发。UDP是不可靠的,它不提供任何交付的保证,一个数据包在…

    数据库 2023年6月16日
    0107
  • 互联网校招指北

    这篇文章写着写着,突然觉得《紧急救援》中有一句台词很对: “不是幸运给你机会,而是因为够坚持,才有了幸运的机会” 共勉~ 时间跨度 一年共两次校招季,2 月…

    数据库 2023年6月6日
    0102
  • 从前,有一个简单的通道系统叫尤娜……

    从前,有个女生宿舍,住着小A、小B、尤娜和我4个人。有天,小A不小心把小B的床板坐塌了。小B非常生气,当场和小A翻脸。不论人缘最好的尤娜怎么中间调解都不管用。一直到毕业,小A和小B…

    数据库 2023年6月6日
    089
  • mydumper备份mysql8.0 sql thread被kill掉

    这个问题是很久以前的事了。今天,我看了看我的笔记,发现了这个问题。我当时没有仔细看过它。我现在就想复制它。 [En] This problem was a long time ag…

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