MySQL 5.7在线设置复制过滤【转】

最新推荐文章于 2024-06-26 19:37:39 发布

转载最新推荐文章于 2024-06-26 19:37:39 发布 · 62 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/paul8339/p/8309459.html

文章标签：

#数据库

本文介绍MySQL5.7中新增的在线设置复制过滤功能，无需重启实例即可轻松调整复制过滤规则，包括按数据库、表及通配符进行复制过滤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转自

MySQL 5.7在线设置复制过滤 - yayun - 博客园 https://www.cnblogs.com/gomysql/p/4991197.html

5.7也GA了，有许多新的特性，其中现在可以进行在线设置复制过滤了。但是还是得停复制，不过不用重启实例了。方便了DBA们进行临时性的调整。下面就简单的测试一下。MySQL 5.7的安装有了很大的变化，我主要是安装的二进制版本。关于如何安装以及如何搭建好复制这种小事，相信聪明的你可以很快搞定。安装请参考http://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

下面进行复制过滤设置测试。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 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: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1902

可以看见现在并没有复制过滤的设置，现在进行调整。设置只同步db1，db2这2个库。
使用命令很简单：CHANGE REPLICATION FILTER REPLICATE_DO_DB = (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
mysql>

可以看见提示要先停止SQL线程。那就先停止SQL线程。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
Query OK, 0 rows affected (0.00 sec)

成功执行，我们再看看复制状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 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: Last_Errno: 0

可以看见已经成功设置只同步db1，db2库，设置完成开启SQL线程就完事。

那么我们要是又要全部的库都要同步该如何操作呢，也很简单。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 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 status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 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:

可以看见又没有过滤了。又全部同步所有的库了。重点命令就是

CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();

我们同样可以可以设置只同步某个库下面的某张表，或者不同步某个库下面的某张表。看命令。

mysql> STOP SLAVE SQL_THREAD; 
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER
    -> REPLICATE_WILD_DO_TABLE = ('db1.t1%'), -> REPLICATE_WILD_IGNORE_TABLE = ('db1.t2%'); Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: db1.t1% Replicate_Wild_Ignore_Table: db1.t2%

可以看见我已经设置同步db1下面以t1开头的表。忽略了db1下面t2开头的表。
重点的命令就是：

CHANGE REPLICATION FILTER
REPLICATE_WILD_DO_TABLE = ('db1.t1%'),
REPLICATE_WILD_IGNORE_TABLE =  ('db1.t2%');

如果我们要设置同时同步1个库下面的某些表可以这样写：

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER
    -> REPLICATE_WILD_DO_TABLE = ('db2.t1%','db2.t2%'); Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: db2.t1%,db2.t2%

可以看到已经成功设置。

如果我们这样写是不生效的。会以最后一个表为准：

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER
    -> REPLICATE_WILD_DO_TABLE = ('db2.t1%'), -> REPLICATE_WILD_DO_TABLE = ('db2.t2%'); Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.25.173 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1902 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 2068 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: db2.t2%