| Bug #67362 | Make Replication filter settings dynamic | ||
|---|---|---|---|
| Submitted: | 24 Oct 2012 16:23 | Modified: | 1 Oct 2013 10:01 |
| Reporter: | Davi Arnaut (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S4 (Feature request) |
| Version: | 5.5,5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | dynamic, replication filters | ||
[24 Oct 2012 16:23]
Davi Arnaut
[24 Oct 2012 16:25]
Davi Arnaut
Combined patches for dynamic replication filter options. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: Bug#67362-Make-Replication-filter-settings-dynamic.patch (application/octet-stream, text), 69.02 KiB.
[22 Dec 2012 12:23]
MySQL Verification Team
Thanks for the contribution Davi!
[1 Oct 2013 10:01]
Jon Stephens
This is fixed by the addition of the CHANGE REPLICATION FILTER statement in MySQL 5.7. Documented in the 5.7.3 changelog as follows:
Replication filtering rules can now be set dynamically using
the SQL statement CHANGE REPLICATION FILTER introduced in this
release. This statement has the same effect as starting the
slave mysqld with one or more of the options --replicate-do-db,
--replicate-ignore-db, --replicate-do-table,
--replicate-ignore-table, --replicate-wild-do-table,
--replicate-wild-ignore-table, and --replicate-rewrite-db.
For example, issuing the statement CHANGE REPLICATION FILTER
REPLICATE_DO_TABLE = (d1.t2) is equivalent to starting the slave
mysqld with --replicate-do-table='d1.t2'.
CHANGE REPLICATION FILTER differs from the server options in
that, to take effect, the statement requires only that the slave
be stopped beforehand and restarted afterwards, using STOP SLAVE
and START SLAVE, respectively.
This statement leaves any existing replication filtering rules
unchanged; to unset all filters of a given type, set the filter
to an empty list, as shown in this example:
CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();
You can list multiple replication filtering rules in the same
statement, separated by commas. When multiple instances of the
same rule are found, only the last instance is used.
For more information, see
http://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
[*which will appear shortly in the online Manual* --js];
see also http://dev.mysql.com/doc/refman/5.7en/replication-rules.html.
Closed.
[4 Dec 2013 10:36]
Laurynas Biveinis
mysql-server$ bzr log -r 6568 ------------------------------------------------------------ revno: 6568 committer: Venkatesh Duggirala<venkatesh.duggirala@oracle.com> branch nick: mysql-trunk timestamp: Wed 2013-09-25 18:46:07 +0530 message: Bug#15877941-MAKE REPLICATION FILTER SETTINGS DYNAMIC WL#7057: MAKE --REPLICATION-* FILTER SETTINGS DYNAMIC Problem: The slave options --replicate-* are not dynamic. Hence these options cannot be changed while the server is running. Fix: Introduced "CHANGE REPLICATION FILTER" command which enables users to modify replication filtering rules without having to stop and restart the server. This is accomplished by just stopping the sql thread alone when these options are set dynamically. Since filtering rules are only used by the slave SQL thread, setting them while the thread is not running avoids the need for locking. Eg: CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1,db2,`db3`); CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(db1,db2,`db3`); CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(db1.t1); CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(db1.t1); CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('db.t%'); CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('db.t%'); CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((db1,db2), (from_db, to_db)); Following are the few behavioral points about the command: a) Users should be able to set multiple filter rules in one command like CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1), REPLICATE_IGNORE_TABLE=(db1.t1), ..; b) To reset the filter value, they have to use void brackets "()" syntax, i.e, empty list will clear the existing values and set it to an empty value For example: CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (); c) The non-empty list filter rules will clear the existing values and set the value to new list. CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db1.t1); will reset all existing values and set it the rule to db1.t1 value. d) Unspecified filter rules will be unchanged. For example: CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db1.t1); will change REPLICATION_IGNORE_TABLE rule only and rest of the filter rules will not be changed and existing values will be continued. e) If some rule is specified multiple times, the latter list will be considered and the earlier list will be ignored. For example: CHANGE REPLICATION FILTER REPLICATION_DO_DB=(db1,db2), REPLICATE_DO_DB=(db3,db4); db1 and db2 list will be ignored and REPLICATE_DO_DB list will be updated with db3 and db4 values. f) In case of OUT OF MEMORY error, the command might get executed partially i.e., few of the filter rules might get executed and few of them might not get executed. User needs to verify them manually to see which ones are executed.
[4 Dec 2013 10:37]
Laurynas Biveinis
mysql-server$ bzr log -r 6569 ------------------------------------------------------------ revno: 6569 committer: Venkatesh Duggirala<venkatesh.duggirala@oracle.com> branch nick: mysql-trunk timestamp: Thu 2013-09-26 06:34:54 +0530 message: Bug#15877941-MAKE REPLICATION FILTER SETTINGS DYNAMIC WL#7057: MAKE --REPLICATION-* FILTER SETTINGS DYNAMIC Forgot to update one .result file in the previous push.
[16 Apr 2014 16:25]
Venkatesh Duggirala
Posted by Developer: Please have a look at http://mysqlserverteam.com/mysql-5-7-3-making-mysql-slave-replication-filters-dynamic/ for more information on this work.
