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:
None 
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
Description:
Make the slave options --replicate-* dynamic variables so that these
options can be changed dynamically while the server is running,
which enables users to modify replication filtering rules without
having to stop and restart the server.

How to repeat:
Attempt to set any of the replicate-* options at runtime.

Suggested fix:
This can be accomplished by just requiring that the slave threads are
stopped when these options are set dynamically. Since filtering
rules are only used by the SQL slave thread, setting them while the
thread is not running avoids the need for locking.
[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] Shane Bester
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.