mysql5.7过滤重复数据_MySQL 5.7在线设置复制过滤【转】

转自

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 formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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: 0Last_Error:

Skip_Counter: 0Exec_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.01sec)

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 formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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.00sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB =();

Query OK, 0 rows affected (0.00sec)

mysql>start SLAVE SQL_THREAD;

Query OK, 0 rows affected (0.00sec)

mysql>show slave status\G

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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.00sec)

mysql>CHANGE REPLICATION FILTER

-> REPLICATE_WILD_DO_TABLE = ('db1.t1%'),

-> REPLICATE_WILD_IGNORE_TABLE = ('db1.t2%');

Query OK, 0 rows affected (0.00sec)

mysql>show slave status\G

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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.00sec)

mysql>CHANGE REPLICATION FILTER

-> REPLICATE_WILD_DO_TABLE = ('db2.t1%','db2.t2%');

Query OK, 0 rows affected (0.00sec)

mysql>show slave status\G

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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.00sec)

mysql>CHANGE REPLICATION FILTER

-> REPLICATE_WILD_DO_TABLE = ('db2.t1%'),

-> REPLICATE_WILD_DO_TABLE = ('db2.t2%');

Query OK, 0 rows affected (0.00sec)

mysql>show slave status\G

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 10.69.25.173Master_User: repl

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1902Relay_Log_File: relaylog.000002Relay_Log_Pos: 2068Relay_Master_Log_File: mysql-bin.000001Slave_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%

可以看到只有db2下面的t2开头的表生效了。

参考资料：