MySQL 5.7 Online settings copy filter "Go"

Turn from

MySQL 5.7 Online Settings Replication filter-Yayun-Blog Park https://www.cnblogs.com/gomysql/p/4991197.html

5.7 also GA, there are many new features, which are now available for online Setup replication filtering. But you still have to stop copying, but you don't have to restart the instance. Makes it easier for DBAs to make temporary adjustments. Here is a simple test. The installation of MySQL 5.7 has changed a lot, and I am mainly installing the binary version. on how to install and how to build a copy of this little thing, I believe that smart you can quickly fix. Installation please refer to http://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

The following is a copy filtering Setup test.

Mysql>Show Slave status\g***************************1. Row ***************************Slave_io_state:waitingForMaster to send event Master_host:10.69.25.173MASTER_USER:REPL Master_port:320WConnect_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                

You can see that there are no settings for copying the filters now, so adjust them now. Set the 2 libraries to synchronize only db1,db2.
Using the command is simple: change REPLICATION FILTER replicate_do_db = (DB1, DB2);

mysql> change REPLICATION FILTER replicate_do_db =3017  

You can see the prompt to stop the SQL thread first. Stop the SQL thread first.

Mysql>0 rows affected (0.01 sec) mysql> change REPLICATION FILTER replicate_do_db =0 rows affect Ed (0.00 sec)    

Successfully executed, let's look at the replication status:

Mysql>Show Slave status\g***************************1. Row ***************************Slave_io_state:waitingForMaster to send event Master_host:10.69.25.173MASTER_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 Span style= "color: #ff0000;" >  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   

You can see that you have successfully set up only the DB1,DB2 library, and the setup completes by opening the SQL thread.

So if we want all the libraries to be synchronized, it's also very simple.

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:waitingForMaster 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:            

Can see and no filter. All the libraries are synced again. The key command is

Change REPLICATION FILTER replicate_do_db = ();

We can also set up to synchronize only one of the tables below a library, or not to synchronize a table below a library. Look at the order.

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:waitingForMaster 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%           

You can see that I have set the table that starts with T1 in sync db1 below. Tables that begin with T2 DB1 are ignored.
The key command is:

Change REPLICATION filterreplicate_wild_do_table = ('db1.t1%'), replicate_wild_ignore_table =  ( 'db1.t2%');      

If we're going to set up a simultaneous synchronization of 1 libraries, some of the tables below can be written like this:

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:waitingForMaster to send event Master_host:10.69.25.173 master_user:repl master_port: 3306 connect_retry:  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%        

You can see that the settings have been successfully set.

If we write like this, it will not take effect. The last table will prevail:

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:waitingForMaster to send event Master_host:10.69.25.173 master_user:repl master_port: 3306 connect_retry:  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%          

You can see that only the table at the beginning of the T2 below DB2 is in effect.

Resources:

https://www.percona.com/blog/2015/11/04/mysql-5-7-change-replication-filter-online/

MySQL 5.7 Online settings copy filter "Go"