MySQL Replication filter

The principle of replication filters is that the primary node replicates only one or a subset of the data from the database to the slave node, not all copies;

The copy filter can set a "black and white list" function to set what data can be copied to the slave node and which is not possible

There are two implementations of replication filters: one in the master node configuration and one in the slave node configuration

The disadvantage of configuring a replication filter on the master node is that binary logging is the data information for a library, and no other library information is logged, so that replay recovery from the binary log is not possible when other libraries fail.

Therefore, generally do not configure filtering on the master node;

It is more common to configure replication filtering from a node, but there are drawbacks to increasing IO read and write pressure because the primary node records the binary log information of all libraries that are still read from the node IO thread and stored in

From the trunk log of the node, the filter is set, and only the unfiltered operation is replayed when the SQL thread is executed from the node;

Here is an example to illustrate:

Configuring filters from a Node

STOP SLAVE; #首先将从节点的功能关闭SET @ @global. replicate_ignore_table= ' mydb.tbl '; #我们将mydb数据库中的tbl表过滤掉, That is, the MySQL database from the node will not contain this table start SLAVE io_thread,sql_thread; #开启从节点的IO和SQL线程 # configuration complete, next Test

Test

 create database mydb;    #在主节点创建数据库mydb, At this point on the slave node is the ability to synchronize this library  create table tbl (Id int primary key,name char ());  # Create a mydb.tbl table on the master node, and then view the table from the node if it is filtered 

mariadb [mydb]> show slave status\g;*************************** 1. row *                 Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.1.101   #主节点host                    Master_User: repluser   #主节点授权复制的账户                    master_port: 3306                 connect_retry:  60              master_log_file:  master-log.000003 &nbSP; #要复制的主节点的二进制日志           read_master_log_pos: 936     #从此二进制日志的什么位置开始复制                 Relay_Log_File: relay-log.000007   #从节点的中继日志, the binary log will be read into the trunk log through IO                  relay_log_pos: 643         Relay_Master_Log_File: master-log.000003              slave_io_running: yes  # The IO function from the node is open, important             slave_sql_running:  Yes   #从节点的SQL功能是否开启, Important                Replicate_Do_DB:    #白名单, do express allow, that is, do not filter            Replicate_Ignore_DB:    #黑名单, ignore is rejected, that is, filtering, in addition to the database, you can also filter the table             replicate_do_table:        replicate_ignore_table: mydb.tbl    #ignore表示黑名单即过滤, so we don't see this table in show tables; from node       replicate_wild _do_table:   replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   skip_ counter: 0          exec_master_log_pos: 936               Relay_Log_Space: 1216               until_condition: none                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 master_ssl_key:         seconds_behind_ master: 0           #从节点落后于主节点的时间, Unit is seconds. While the primary node is written in parallel, the read from the node IO is a serial operation, so when the primary node is written frequently, the differences between the master and slave are getting larger master_ssl_verify_server_cert: no                 Last_IO_Errno: 0    #IO和SQL错误信息                  Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:               Master_Server_Id: 11 row in set  (0.00  SEC)

Since we are setting the filter mydb.tbl table from the node, if the TBL2 table is created in the MyDB library, then this table will be copied to the slave node.

End of experiment

This article from "A_pan" blog, declined reprint!

MySQL Replication filter