- Replication Filter Options
- Setting Replication Filters
- Setting Replication Filters Dynamically
- Setting Replication Filters Dynamically with Multi-Source Replication
- Setting Replication Filters in Option Files
- Setting Replication Filters in Option Files with Multi-Source Replication
- Replication Filters and Galera Cluster
- See Also
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.