mysql replication filter 复制过滤

2022/9/8 2:24:36

编程Tag： mysql table filter do DB replication REPLICATE

本文主要是介绍mysql replication filter 复制过滤，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、master端：

 

复制代码 
--binlog-do-db 二进制日志记录的数据库（多数据库用逗号，隔开），尽量不要使用。

--binlog-ignore-db 二进制日志中忽略数据库 （多数据库用逗号，隔开），尽量不使用。
  以下是mysql主从忽略授权表的方法案例： in master:

[mysqld]

binlog_do_db=YYY 需要同步的数据库。不添加这行表示同步所有

binlog_ignore_db = mysql   这是不记录binlog，来达到从库不同步mysql库，以确保各自权限

binlog_ignore_db = performance_schema

binlog_ignore_db = information_schema
复制代码

 

 etc/my.cnf配置文件示例：

复制代码 
[client]
#password       = [your_password]
port = 3307
socket = /home/work/mysql_3307/tmp/mysql.sock

# *** Application-specific options follow here ***

#
# The MySQL server
#
[mysqld]
binlog_do_db=dba_meta
binlog_do_db=mysql
binlog_do_db=quality
binlog_do_db=superset
binlog_do_db=superset_produc
binlog_do_db=mivideo
binlog_do_db=ott
binlog_do_db=core
binlog_do_db=report
binlog_do_db=operation
binlog_do_db=statistics
binlog_do_db=superset_domestic
binlog_do_db=student
# generic configuration options
port = 3307
socket = /home/work/mysql_3307/tmp/mysql.sock
datadir = /home/work/mysql_3307/data
user        = work
default-time-zone = "+08:00"
复制代码

 

 

 

二、slave端

复制代码 

[mysqld]

--replication-do-db    设定需要复制的数据库（多数据库使用逗号，隔开），尽量不使用。

--replication-ignore-db 设定需要忽略的复制数据库 （多数据库使用逗号，隔开），尽量不使用。

--replication-do-table   设定需要复制的表，尽量不使用。

--replication-ignore-table 设定需要忽略的复制表，尽量不使用。

--replication-wild-do-table 同replication-do-table功能一样，但是可以通配符，最佳使用。

--replication-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符，最佳使用，但与--replication-wild-do-table尽量不同时使用。

# 尽量使用--replication-wild-do-table和--replication-wild-ignore-table，但不宜两者同时使用，而是使用这两者之一即可。

复制代码


 


 


复制代码

auto@10.10.10.20((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.10.10.10
                  Master_User: mysqlsync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.009129
          Read_Master_Log_Pos: 394218960
               Relay_Log_File: relay-bin.000256
                Relay_Log_Pos: 126295006
        Relay_Master_Log_File: mysql-bin.009129
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: analysis.property_define,analysis.define,analysis.property
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 126294801
              Relay_Log_Space: 394219401
              Until_Condition: Master
               Until_Log_File: mysql-bin.009161
                Until_Log_Pos: 224619836
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 157399
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 175511070
                  Master_UUID: 99bacfbb-518b-11ea-af0c-34b35428066b
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Sat Jun  6 10:33:18 2020
auto@10.10.10.10((none))


#######################################

# 如上所示：Replicate_Wild_Do_Table: analysis.property_define,analysis.define,analysis.property

# 表示该从库只同步analysis数据库的3张表：property_define，define，property
# 如上图所示： 
Until_Condition: Master
Until_Log_File: mysql-bin.009161
Until_Log_Pos: 224619836

# 这三者表示该从库执行了:start slave sql_thread until master_log_file='mysql-bin.009161',master_log_pos=224619836; 

复制代码


 


 


 


 


三、在线配置复制过滤


复制代码

在MySQL5.5/5.6里版本里，设置同步复制过滤，例如设置忽略掉test库的t2表，你需要在my.cnf配置文件里增加：

replicate-ignore-table=test.t2
必须重启mysql服务进程才能生效。



在MySQL5.7里，通过一个新的命令，可以支持在线动态修改，而不须重启mysql进程就生效。

Example:

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1,db2);
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(db1,db2);
CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(db1.t1);
CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(db2.t2);
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('db.t%');
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('db%.a%');
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((from_db, to_db));




change replication filter  replicate_wild_do_table=('sentry.%','hive.%','mysql.%');

复制代码


 


 


 


复制代码

13.4.2.2更改复制过滤器语句
CHANGE REPLICATION FILTER filter[, filter][, ...]

filter: {
    REPLICATE_DO_DB = (db_list)
  | REPLICATE_IGNORE_DB = (db_list)
  | REPLICATE_DO_TABLE = (tbl_list)
  | REPLICATE_IGNORE_TABLE = (tbl_list)
  | REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
  | REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
  | REPLICATE_REWRITE_DB = (db_pair_list)
}

db_list:
    db_name[, db_name][, ...]

tbl_list:
    db_name.table_name[, db_table_name][, ...]
wild_tbl_list:
    'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]

db_pair_list:
    (db_pair)[, (db_pair)][, ...]

db_pair:
    from_db, to_db
CHANGE REPLICATION FILTER设置从服务器上的一个或多个复制过滤规则，方法与使用复制过滤选项（例如 或） 启动从服务器mysqld相同。
与使用服务器选项的情况不同，此语句不需要重新启动服务器即可生效，只需要先使用从SQL线程停止（然后再使用SQL线程 重新启动 ）即可。
需要 特权。 
--replicate-do-db
--replicate-wild-ignore-table
STOP SLAVE SQL_THREAD
START SLAVE SQL_THREAD
CHANGE REPLICATION FILTER SUPER

注意
无法在为组复制配置的MySQL服务器实例上设置复制过滤器，因为在某些服务器上过滤事务将使该组无法就一致状态达成协议。

以下列表显示了CHANGE REPLICATION FILTER选项及其与 --replicate-*服务器选项的关系：

REPLICATE_DO_DB：包括基于数据库名称的更新。等同于 --replicate-do-db。

REPLICATE_IGNORE_DB：根据数据库名称排除更新。等同于 --replicate-ignore-db。

REPLICATE_DO_TABLE：包括基于表名的更新。等同于 --replicate-do-table。

REPLICATE_IGNORE_TABLE：根据表名排除更新。等同于 --replicate-ignore-table。

REPLICATE_WILD_DO_TABLE：包括基于通配符模式匹配表名称的更新。等同于 --replicate-wild-do-table。

REPLICATE_WILD_IGNORE_TABLE：排除基于通配符模式匹配表名称的更新。等同于 --replicate-wild-ignore-table。

REPLICATE_REWRITE_DB：在从属服务器上用新名称替换主服务器上的指定数据库后，对从属服务器执行更新。等同于 --replicate-rewrite-db。

REPLICATE_DO_DB和 REPLICATE_IGNORE_DB过滤器 的确切效果取决于是基于语句的复制还是基于行的复制。有关更多信息，请参见第16.2.5节“服务器如何评估复制过滤规则”。

CHANGE REPLICATION FILTER通过用逗号分隔 规则，可以在单个语句中创建多个复制过滤规则 ，如下所示：

CHANGE REPLICATION FILTER
    REPLICATE_DO_DB = (d1), REPLICATE_IGNORE_DB = (d2);
发出刚刚显示的语句等同于使用选项 启动从属mysqld。 --replicate-do-db=d1 --replicate-ignore-db=d2

如果多次指定同一过滤规则，则实际上仅使用 最后一个这样的规则。例如，此处显示的两个语句具有完全相同的效果，因为REPLICATE_DO_DB 忽略了第一条语句中的第一条规则：

CHANGE REPLICATION FILTER
    REPLICATE_DO_DB = (db1, db2), REPLICATE_DO_DB = (db3, db4);

CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (db3,db4);
警告
此行为与--replicate-*过滤器选项的行为不同，后者 多次指定同一选项会导致创建多个过滤器规则。

表和数据库的名称不包含任何特殊字符，无需引用。与REPLICATION_WILD_TABLE和 REPLICATION_WILD_IGNORE_TABLE一起使用的值 是字符串表达式，可能包含（特殊）通配符，因此必须加引号。在以下示例语句中显示了这一点：

CHANGE REPLICATION FILTER
    REPLICATE_WILD_DO_TABLE = ('db1.old%');

CHANGE REPLICATION FILTER
    REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');
与一起使用的值REPLICATE_REWRITE_DB 表示成对的数据库名称。每个此类值都必须用括号括起来。以下语句db1将在主数据库db2上发生的语句重写为从数据库上的 语句 ：

CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db1, db2));
刚刚显示的语句包含两组括号，一组包含一对数据库名称，另一组包含整个列表。这也许是更容易看到在下面的例子中，它创建了两个 rewrite-db规则，一个重写的数据库 dbA来dbB，和一个重写数据库dbC到 dbD：

CHANGE REPLICATION FILTER
  REPLICATE_REWRITE_DB = ((dbA, dbB), (dbC, dbD));
该语句使所有现有的复制过滤规则保持不变；要取消设置给定类型的所有过滤器，请将过滤器的值设置为一个明确为空的列表，如本示例所示，该列表将删除所有现有 规则REPLICATE_DO_DB和 REPLICATE_IGNORE_DB规则：


CHANGE REPLICATION FILTER
    REPLICATE_DO_DB = (), REPLICATE_IGNORE_DB = ();
以这种方式将过滤器设置为空会删除所有现有规则，不会创建任何新规则，也不会还原使用--replicate-* 命令行或配置文件中的选项在mysqld启动时设置的任何规则。

REPLICATE_WILD_DO_TABLE 和REPLICATE_WILD_IGNORE_TABLE一起 使用的值必须采用格式 。在MySQL 5.7.5之前，这不是严格执行的，尽管在这些选项中使用不一致的值可能会导致错误的结果（错误＃18095449）。 db_name.tbl_name

复制代码


 


四、配置文件中配置复制过滤


 


复制代码

############################ 

 my.cnf配置文件中配置

############################


[mysqld]
  replicate-wild-do-table=miui_log_sdk.%


  replicate-wild-do-table=model.user


  replicate-wild-do-table=model.log


  replicate-wild-do-table=mysql.% 

  replicate-wild-do-table=dba_metadata.%


    replicate-wild-do-table=skill_store.%


############################# 
2. 在线配置复制过滤 ############################ 
> stop slave; > reset slave all; 
> reset master; 
> change master to master_host='10.10.10.10',master_port=3306,master_user='mysqlsync',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=150;

> change replication filter  replicate_wild_do_table=('dba_metadata.%','model.user','model.log','miui_log_sdk.%','mysql.%');


> start slave;

复制代码


 


复制过滤选项


常常看见很多同学在主库进行过滤选项设置，当然这也有好处，减少了带宽，但是在主库设置过滤选项是非常危险的操作，因为无论是显示要过滤的或者要同步的，二进制日志只记录你设置的，其他的是不会记录的。当主库有数据需要用到binlog恢复时，你就准备哭吧。所以通常在备库进行过滤选项设置。比如忽略某个库，同步所有库，或者同步某一个库，当然这会浪费带宽，但是和安全比起来，这点浪费不算什么。有时候安全与性能往往需要我们自己平衡。


还有就是跨库更新，如果我们在备库是这样设置的，比如同步yayun这个库



replicate_do_db=yayun



主库记录如下：


复制代码
复制代码

mysql> select *  from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

复制代码
复制代码


备库记录如下：


复制代码
复制代码

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

复制代码
复制代码


现在我们在主库插入一条记录


复制代码
复制代码

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into yayun.t1 (name) values ('good yayun');
Query OK, 1 row affected (0.01 sec)

mysql> select  * from yayun.t1;  
+----+------------+
| id | name       |
+----+------------+
|  1 | yayun      |
|  2 | atlas      |
|  3 | mysql      |
|  5 | good yayun |
+----+------------+
4 rows in set (0.00 sec)

mysql> 

复制代码
复制代码


查看备库：


复制代码
复制代码

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql> 

复制代码
复制代码


怎么回事？怎么没有同步？这就是跨库更新带来的问题，比如下面的更新：



use test
insert into yayun.t1 (name) values ('good yayun')



当然你会说哪个2B会这么干啊，呵呵，有时2B还是有的。所以我们还有另外2个过滤复制参数



replicate_wild_do_table
replicate_wild_ignore_table



一个是要同步的表，一个是不同步的表，通常我们可以这样写



replicate_wild_do_table=yayun.%



表示同步yayun库下面的所有表，这样就解决的跨库更新的问题。


复制格式的问题


通常推荐使用ROW格式，为什么使用？看看我前面文章MySQL数据恢复和复制对InnoDB锁机制的影响


不要用Seconds_Behind_Master来衡量MySQL主备的延迟时间 


这个后续我会写相关文章解释为什么不要用该参数衡量主备的延迟时间。


复制过滤的坑：



那就是当你的从库宕机后，无法开机了，这个时候，你配置的复制过滤规则也就没了，因此，这就需要保存复制过滤的配置，才能万无一失

                   


                   

                        这篇关于mysql   replication filter 复制过滤的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
                    

                     

                 
                

                
                    

                

                
 
                
                

                    

                        
相关编程文章

                        更多>
                    

                    
                    
                    
                


            

        


        

             


    

        
    




             

        


    

    
    
        


    

        

            

            

            


        

        

            

                本站官方QQ
                

                    
官方QQ:

                    
23002807

                

            

            
        

        

           
            

                沪ICP备2023033072号-1
                
                  
                 
                
            

            

                      
                
            

        

    







    
    
    


        
        扫一扫关注最新编程教程