Shulou(Shulou.com)06/01 Report--

Foreword:

The most commonly used architecture in MySQL is master-slave replication. In fact, there are many options for master-slave replication, especially on the slave side. We can set replication filtering, such as ignoring a table or library. These filtering options can be modified online without restarting. The original understanding of this piece is not much, recently read the relevant information, I think this function is still very convenient, this article will share this content with you.

1. Introduction of replication filter parameters

First of all, we need to understand the different parameters for setting replication filtering. Replication filtering is set from the library side, you can copy only some libraries or some tables, or you can ignore copying some libraries or some tables. These are controlled by different parameters, the following is a brief description of the role of different parameters.

REPLICATE_DO_DB: specify to synchronize only the data of a library REPLICATE_IGNORE_DB: ignore the synchronization of a library _ DO_TABLE: specify to synchronize a table replication _ IGNORE_TABLE: ignore the synchronization of a table replication _ WILD_DO_TABLE: specify to synchronize certain tables, you can use the wildcard REPLICATE_WILD_IGNORE_TABLE: ignore the synchronization of some tables, you can use the wildcard REPLICATE_REWRITE_DB: replace the library name from the library side

These replication filter parameters are easy to understand, and you can get a rough idea of what this parameter does just by looking at the name. By default, these parameters are not set. When master-slave replication is enabled, the slave side will synchronize all data sent from the master database by default.

two。 Modify replication filtering options

We can set the above parameters when we want to temporarily adjust the replication policy of the slave library. We can write the filter parameters to the configuration file and then restart the slave library to apply, but this method requires restarting the instance and is not recommended. The MySQL5.7 version is ready for online setup replication filtering. However, replication still has to be stopped, but there is no need to restart the instance to facilitate temporary adjustment. The CHANGE REPLICATION FILTER statement is mainly used, so let's simply test it:

# replication filtering mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.3.16 is not set by default Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 35198 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 910 Relay_Master_Log_File: binlog.000004 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: 35198 # setting ignores replication mysql > STOP SLAVE SQL_THREAD of db1 libraries Query OK, 0 rows affected (0.00 sec) mysql > CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1); Query OK, 0 rows affected (0.00 sec) mysql > START SLAVE SQL_THREAD Query OK 0 rows affected mysql > show slave status\ row * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.3.16 Master_ User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 35198 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 910 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: db1 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: 35198# Master Library create db1 Test Slave Library is synchronized with mysql > CREATE DATABASE `db1` DEFAULT CHARACTER SET utf8 Query OK, 1 row affected (0.01sec) mysql > show databases +-+ | Database | +-+ | information_schema | | db1 | | mysql | | performance_schema | | sys | | testdb | +-+ 6 rows in set. Sec) # View slave library status mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +-+ 5 rows in set (0.00 sec) mysql > show slave status\ G* * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.3.16 Master_User: repl Master_Port: 33061 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 35383 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1095 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do _ DB: Replicate_Ignore_DB: db1 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: 3538 cancel replication filter parameter mysql > STOP SLAVE SQL_THREAD Query OK, 0 rows affected (0.01 sec) mysql > CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (); Query OK, 0 rows affected (0.00 sec) mysql > START SLAVE SQL_THREAD Query OK 0 rows affected mysql > show slave status\ row * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.3.16 Master_ User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 35383 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1095 Relay_Master_Log_File: binlog.000004 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: 35383

Above, we briefly demonstrated how to use the CHANGE REPLICATION FILTER statement to modify replication filtering options online. all the filter parameters listed can be modified using this statement, but note that some options are mutually exclusive. The appropriate parameters should be set according to the actual requirements. The example syntax in the official document is given below:

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

Summary:

This article describes how to change the replication filtering options online. Different filtering parameters have different uses. If you do have a need to set filtering parameters, it is recommended that you conduct a comprehensive test. Some parameter settings may affect the replication of other database tables. If you want to take effect permanently, you can modify it online and then add it to the configuration file, which will still take effect after restart from the library.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.