Replication Filters

Replication filters allow users to configure replication slaves to intentionally skip certain events.

Replication Filter Options

Replication Filters for Masters

The following options are used on a replication master to restrict local changes to specific databases from getting written to the binary log, which also determines whether any replication slaves replicate those changes.

Replication Filters for Slaves

The following options are used on a replication slave to restrict replicated changes to specific databases and/or tables (see the MySQL manual for details on the semantics on how this works with statement-based and row-based replication):

Setting Replication Filters

How you set replication filters depends on whether you are setting them dynamically or whether you are setting them in option files.

Setting Replication Filters Dynamically

In MariaDB, as of MariaDB 5.5.22, the following variables representing replication filters for slaves have been made dynamic:

This makes it possible to change them without having to restart the server.

Replication filters for masters have not been made dynamic.

The slave threads must still be stopped to change any of the above variables:

STOP SLAVE;
SET GLOBAL replicate_do_db = "mydb1,mydb2";
SET GLOBAL replicate_wild_ignore_table = "mydb1.local_%";
START SLAVE;

To specify multiple database names, table names, or wildcard patterns, separate them with commas. Note that this means that it is not possible to specify names or patterns that contain commas. If this is needed, it is necessary to use the command-line options in my.cnf and restart the server.

Setting Replication Filters Dynamically with Multi-Source Replication

The usage of dynamic replication filters changes somewhat when multi-source replication is in use. By default, the variables are addressed to the default connection, so in a multi-source environment, the required connection needs to be specified. There are two ways to do this.

Prefixing the Replication Filter with the Connection Name

One way to change a replication filter for a multi-source connection is to explicitly specify the name when changing the filter:

STOP SLAVE 'gandalf';
SET GLOBAL gandalf.replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE 'gandalf';
Changing the Default Connection

Alternatively, the default connection can be changed by setting default_master_connection, and then the replication filter can be changed in the usual fashion:

SET default_master_connection = 'gandalf';
STOP SLAVE; 
SET GLOBAL replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE;

Setting Replication Filters in Option Files

Keep in mind that although the dynamic replication filter variables support comma-separated lists, the configuration file options do not.

For example, let's say that you want to set the following filter dynamically:

SET GLOBAL replicate_do_db='database1,database2';

If you would like to make this filter persist mysqld restarts by adding it to the MariaDB configuration file, instead of using comma-separated lists, you would have to repeat the option for each occurrence. For example:

[mysqld]
replicate_do_db='database1'
replicate_do_db='database2'

Setting Replication Filters in Option Files with Multi-Source Replication

If you are using multi-source replication, then the configuration file can also include the connection name that each filter would apply to. For example:

[mysqld]
gandalf.replicate_do_db='database1'
saruman.replicate_do_db='database2'

Replication Filters and Galera Cluster

When using Galera cluster, replication filters should be used with caution. See Configuring MariaDB Galera Cluster: Replication Filters for more details.

See Also

Comments

Comments loading...