In MySQL Replication, users can filter statements either at master (using –binlog-* startup options) or at the slave (using –replicate-* startup options). Prior to MySQL-5.7.3, users could set these filtering rules either through command line parameters or by using my.cnf file. In either case MySQL server must be restarted in order to change the filtering rules. It is not easy to restart MySQL server in real time scenarios (because of downtime issues and also loss of buffer cache resulting in performance problems). It is always helpful having a way to dynamically configure these filtering rules. Particularly in environments where slaves are configured dynamically to replicate certain databases or tables based on load and usage.
In MySQL-5.7.3, a new command “CHANGE REPLICATION FILTER” has been introduced through which users can change the *slave* side replication filters dynamically without the need for restarting the server.
Replication filtering:
At the master, users can control the statements which need to get into the binary log file by specifying startup options (like –binlog-*). Users cannot use these options to control the databases and tables that need to be executed on the slave. Instead, use filtering on the slave to control the events that are executed on the slave. At the slave, decisions about whether to execute or ignore statements received from the master are made according to the –replicate-* options that the slave was started with. In the simplest case, when there are no –replicate-* options, the slave executes all statements that it receives from the master.
At the slave server, SQL thread is the only thread accessing replication filters at any give point of time as shown in the picture above. If the SQL thread is not active, filtering rules can be changed without any harm. Hence a new command “CHANGE REPLICATION FILTER” has been introduced which allows dynamic change of filters.
‘CHANGE REPLICATION FILTER‘ Command:
Starting from MySQL-5.7.3, user can set one or more replication filtering rules on the slave dynamically while the server is up and running. It requires only SQL thread to be down.
The following slave replication filters can be changed dynamically using this command.
- REPLICATE_DO_DB
- REPLICATE_IGNORE_DB
- REPLICATE_DO_TABLE
- REPLICATE_IGNORE_TABLE
- REPLICATE_WILD_DO_TABLE
- REPLICATE_WILD_IGNORE_TABLE
- REPLICATE_REWRITE_DB
|
Syntax: db_list: tbl_list: wild_tbl_list: db_pair_list: db_pair: Example: |
1) What is the equivalent way of doing –replicate-do-db=db1 start option using the command?
A) You can achieve the same using
Eg: CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1)
2) To specify db1 and db2 in replicate-ignore-db, I was using –replicate-ignore-db=db
–replicate-ignore-db=db2. How do I achieve the same using the command?
A) You should be able to set more than one value to any rule using one command.
Eg: To put db1, db2 database names in replicate-ignore-db, You can do
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(db1, db2)
3) Can I change master side filter rules (–binlog-*) using this command?
A) No, this new command is only for replication filter rules on slave (–replicate-* options ).
4) What are the replication filter rules which can be changed dynamically using this command?
A) You can change all slave side replication filter rules dynamically using this command. Following is the complete list of filter rules which can be set dynamically in MySQL-5.7.3.
- REPLICATE_DO_DB
- REPLICATE_IGNORE_DB
- REPLICATE_DO_TABLE
- REPLICATE_IGNORE_TABLE
- REPLICATE_WILD_DO_TABLE
- REPLICATE_WILD_IGNORE_TABLE
- REPLICATE_REWRITE_DB
5) When I am using this command, I am getting error 1896 (This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first.). How do I rectify it?
A) When SQL thread is active, one cannot change the filtering rules. Unlike the case with the server startup options, this statement does not require restarting the server to take effect, only that the slave SQL thread be stopped. As the error message indicates execute ‘STOP SLAVE SQL_THREAD’, execute CHANGE REPLICATION FILTER command and start the slave SQL thread using ‘START SLAVE SQL_THREAD’ to make the new rules effective.
|
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1); |
6) Can I change more than one replication filter rules at a time using one command?
A) Yes, You can set multiple filter rules in one command
Eg: CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1), REPLICATE_IGNORE_TABLE=(db1.t1);
7) Are brackets ( ) compulsory to set even one value?
A) Yes, You must use ‘(‘ ‘)’ to mention the list of values (even for one value).
Eg: CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(db1.t1)
8) How to reset the values for a particular filter rule?
A) To reset the filter value, use void brackets “()” syntax, i.e, empty list will clear the existing values
Eg: CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=(); clears all the existing values for REPLICATE_WILD_IGNORE_TABLE rule.
9) I have used only REPLICATE_IGNORE_TABLE in the command. What happens to other filter rules?
A) Unspecified filter rules will be unchanged.
Eg: CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db1, db2), (db3,db4)); will clear all the existing list for REPLICATION_REWRITE_DB rule and it will add two db_pairs to the list. And the rest of the filter rules will not be changed.
10) Are there any behavioral changes when compared to server startup options
–replicate*?
A) No, there are no behavioral changes when compared to server startup options.
11) Are these changes made through the new command persistent upon restarting the server?
A) No, the changes are not persistent. You have to change the values again after restarting the server. If you want these changes to be persistent, you can put them in my.cnf file.
12) What will happen if I use same rule multiple times in the same command, will they be appended together?
A) No, if any rule is specified multiple times, the latter list will be considered (the earlier list will be ignored)
Eg: CHANGE REPLICATION FILTER REPLICATION_DO_DB=(db1, db2), REPLICATE_DO_DB=(db3,db4); the first db list db1, db2 list will be ignored and the REPLICATE_DO_DB rule will be updated with the second list db3, db4.
13) I have mentioned filter rules in my.cnf and also changed the values using CHANGE REPLICATION FILTER command. Which one takes high priority?
A) The values changed through the new command will be considered and values read through my.cnf settings will be ignored completely.
14) Will the existing way of setting the replication filters work on MySQL-5.7.3?
A) Yes, there is no change in that behavior. The filter rules can be set using
- command line parameters at the time of server startup (–replicate-* )
- set it in my.cnf file before server startups
- set it dynamically using CHANGE REPLICATION FILTER COMMAND while the server is up and running
15) I have a db name with special characters, non utf-8 characters, eg: db1, däHow do I use it in the new command?
A) You have to specify the object name exactly the same way you have created the database using create database command. Note that db_names and table_names in db_list and table_list follow the metadata syntax exactly the same way they were created.
Eg: CHANGE REPLICATION FILTER REPLICATION_DO_DB=(db1, dä);and 'pattern' in wild_tbl_list follows exactly the same rule as it is used in 'LIKE' clause in 'SELECT' command.
CHANGE REPLICATE FILTER REPLICATION_WILD_IGNORE_TABLE=('db1.t1′);
16) When I am using this new command, I am getting 1794 error (Slave is not configured or failed to initialize properly), What is wrong?
A) This command cannot be executed on a stand-alone server where the replication is disabled. As the error message says, you must at least set –server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
17) I have used the command to change the filter rules. Now how to check the changed filter rules?
A) You can use “Show Slave Status” command.
18) What is the new syntax to mention –replicate-rewrite-db= db1->db2.
A) You can use CHANGE REPLICATION FILTER REPLICATION_REWRITE_DB=((db1,db2));
to rewrite all db1’s statements into db2’s statements. Just like other rules, you can mention more than one db_pair.
Eg: CHANGE REPLICATION FILTER REPLICATION_REWRITE_DB=((db1,db2), (db3,db4)) which tells the server to rewrite all db1 statements into db2 statements and all db3 statements into db4 statements.
19) Can I add or delete few values from the list?
A) No, this new command is used to replace the old values with the new values and there is no provision to add /delete values from the list.
20) Are there any test scripts to have a look at the usage in detail?
A) Yes, please look at $MYSQL_HOME/mysql-test/suite/rpl/t/rpl_filter_* for more details on the usage.
Summary:
This blog post provides a very brief insight about Filtered replication and the new command “CHANGE REPLICATION FILTER” introduced as part of MySQL-5.7.3. It has a short FAQ section about the usage of the command. I hope we have made your life simpler by avoiding few restarts of the server. Please let us know your feedback on the same. If you find any issues feel free to create bugs @ bugs.oracle.com. I cannot end this post without mentioning the contribution from Davi Arnaut (for Bug#67362) which inspired us to implement this new feature. Thanks Davi!
References:
http://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
http://dev.mysql.com/doc/refman/5.7/en/replication-rules-examples.html
http://dev.mysql.com/doc/refman/5.7/en/replication-rules.html
http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html