Dynamic Replication Variables

The following variables 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):

• replicate_do_db
• replicate_ignore_db
• replicate_do_table
• replicate_ignore_table
• replicate_wild_do_table
• replicate_wild_ignore_table

In MariaDB, as of MariaDB 5.5.22, these variables have been made dynamic. This makes it possible to change them without having to restart the server.

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.

The variables are addressed to the default connection, so in a multi-source environment, the required connection needs to be specified. For example:

SHOW ALL SLAVES STATUS;

- Connection_name: gandalf
- Replicate_Do_Table: database1.table1,database1.table2

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

SHOW ALL SLAVES STATUS;

- Connection_name: gandalf
- Replicate_Do_Table: database1.table1,database1.table2

The expected result,

- Connection_name: gandalf
- Replicate_Do_Table: database1.table1,database1.table2,database1.table3

is not the case, as gandalf was not the default connection. The following solution will work in this situation:

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

or, alternatively, you can make the gandalf connection default, and then work with it in the usual fashion:

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

Differences Between Dynamic Variables and Configuration File Options

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'

See the documentation for each replication filter option for more details.