• 【MySQL】MySQL 5.7中过滤复制和部分复制的变化


    在线添加复制过滤是5.7引入的新特性,使用change replication filter语句。在5.7之前,增加、修改复制规则需要重启mysql。

    当前复制是没有开启过滤复制的:

    mysql> show slave statusG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.0.130
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000002
    Read_Master_Log_Pos: 351
    Relay_Log_File: centos59-relay-bin.000003
    Relay_Log_Pos: 566
    Relay_Master_Log_File: master-bin.000002
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    

    主库包含db1-db4。现在我们只想复制db1、db2:

    mysql> change replication filter replicate_do_db = (db1, db2);
    error 3017 (hy000): this operation cannot be performed with a running slave sql thread; run stop slave sql_thread first
    

    需要先停止sql thread,然后再执行。

    mysql> stop slave sql_thread;
    query ok, 0 rows affected (0.00 sec)
    mysql> change replication filter replicate_do_db = (db1, db2);
    query ok, 0 rows affected (0.00 sec)
    mysql> show slave statusG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.0.130
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000002
    Read_Master_Log_Pos: 505
    Relay_Log_File: centos59-relay-bin.000003
    Relay_Log_Pos: 720
    Relay_Master_Log_File: master-bin.000002
    Slave_IO_Running: Yes
    Slave_SQL_Running: No
    Replicate_Do_DB: db1,db2
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    

      

    去除过滤规则,需要给过滤器一个空的名字:

    mysql> stop slave sql_thread;
    query ok, 0 rows affected (0.03 sec)
    mysql> change replication filter replicate_do_db = ();
    query ok, 0 rows affected (0.00 sec)
    mysql> start slave sql_thread;
    query ok, 0 rows affected (0.00 sec)
    mysql> SHOW SLAVE STATUSG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.0.130
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000002
    Read_Master_Log_Pos: 1629
    Relay_Log_File: centos59-relay-bin.000003
    Relay_Log_Pos: 1844
    Relay_Master_Log_File: master-bin.000002
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:



    可以同时设置多个复制过滤规则,多个规则用逗号分割:

    mysql> stop slave sql_thread;
    query ok, 0 rows affected (0.03 sec)
    mysql> change replication filter
    replicate_wild_do_table = ('db1.db1_new%'),
    replicate_wild_ignore_table = ('db1.db1_old%');
    mysql> start slave sql_thread;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SHOW SLAVE STATUSG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.0.130
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000003
    Read_Master_Log_Pos: 448
    Relay_Log_File: centos59-relay-bin.000006
    Relay_Log_Pos: 663
    Relay_Master_Log_File: master-bin.000003
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: db1.db1_new%
    Replicate_Wild_Ignore_Table: db1.db1_old1%
    

      


    change replication filter命令不能像在my.cnf文件中那样设置多个相同的过滤规则,如果有多个,只有最后一个会生效。

    mysql> select * from db1.db1_old;
    empty set (0.00 sec)
    mysql> stop slave sql_thread;
    query ok, 0 rows affected (0.03 sec)
    mysql> change replication filter
    replicate_wild_do_table = ('db2.db2_tbl1%'),
    replicate_wild_do_table = ('db2.db2_tbl2%');
    mysql> START SLAVE SQL_THREAD;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SHOW SLAVE STATUSG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.0.130
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000003
    Read_Master_Log_Pos: 980
    Relay_Log_File: centos59-relay-bin.000006
    Relay_Log_Pos: 1195
    Relay_Master_Log_File: master-bin.000003
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: db2.db2_tbl2%
    Replicate_Wild_Ignore_Table:
    

      

  • 相关阅读:
    变参宏 __VA_ARGS__
    预处理中的 # 和 ##
    strlen与sizeof异同
    .vimrc
    sudo:有效用户 ID 不是 0,sudo 属于 root 并设置了 setuid 位吗
    远程ssh登陆时报错:/bin/bash: Permission denied
    Excel中VBA进行插入列、格式化、排序
    ORACLE发送带附件邮件的二三事之一
    Windows Server 2008 双网卡同时上内外网 不能正常使用
    VMWARE修改CPUID
  • 原文地址:https://www.cnblogs.com/abclife/p/16546526.html
Copyright © 2020-2023  润新知