This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 @@skip_replicaton filtering of binlog events
Title
Task ID234
Queue
Version
Status
Priority
Copies toSergei
Mdcallag

Created byKnielsen01 Aug 2011Done
Supervisor   
Lead Architect    
Architecture Review 12 Aug 2011
Implementor 12 Aug 2011
Code Review 12 Aug 2011
QA  
Documentation 12 Aug 2011
 High-Level Description
This worklog is about introducing a new session variable
@@skip_replication

When this variable is set, an associated flag bit is set in events logged into
the binlog.

The first stage of this worklog is to introduce an option
--replicate-events-marked-for-skip. This defaults to REPLICATE, which
replicates all events irregardless of @@skip_replication. But when set to
FILTER_ON_SLAVE, the slave will skip any events received with the
@@skip_replication bit set.

The second stage is to do this filtering on the master. When
--replicate-events-marked-for-skip is set to FILTER_ON_MASTER on the slave,
the master will avoid sending these events to that slave at all, saving on
bandwidth but otherwise working the same way as stage 1.

The motivation for this worklog is to allow an application to do changes that
are replicated in a way external to the internal MySQL replication, but which
still keeps the events in the binlog for debugging/point-in-time recovery, or
for standard replication to other servers.

User documentation:

-----------------------------------------------------------------------
Selectively skipping replication of binlog events
-------------------------------------------------

Normally, all changes that are logged as events in the binlog are also
replicated to all slaves (though still subject to filtering by
--replicate-do-xxx, --replicate-ignore-xxx, and similar options). However,
sometimes it may be desirable to have certain events be logged into the
binlog, but not be replicated to all or a subset of slaves, where the
distinction between events that should be replicated or not is under the
control of the application making the changes.

This could be useful if an application does some replication external to the
server outside of the built-in replication, or if it has some data that should
not be replicated for whatever reason.

This is possible with two new system variables introduced in MariaDB 5.5:

Master session variable: @@skip_replication

When this variable is set true, following changes are logged into the binlog
with the flag @@skip_replication set. Such events will not be replicated by
slaves that run with --replicate-events-marked-for-skip set different from
its default of REPLICATE.

    * Variable Name: skip_replication
    * Scope: Session only
    * Access Type: Dynamic
    * Data Type: bool
    * Default Value: OFF

The @@skip_replication option only has effect if binary logging is enabled and
@@sql_log_bin is true.

Attempting to change @@skip_replication in the middle of a transaction will
fail; this is to avoid getting half of a transaction replicated while the
other half is not replicated. Be sure to end any current transaction with
COMMIT/ROLLBACK before changing the variable.

Slave option:: --replicate-events-marked-for-skip

This option tells the slave whether to replicate events that are marked with
the @@skip_replication flag. Default is REPLICATE, to ensure that all changes
are replicated to the slave. If set to FILTER_ON_SLAVE, events so marked will
be skipped on the slave and not replicated. If set to FILTER_ON_MASTER, the
filtering will be done on the master, saving on network bandwidth as the
events will not be received by the slave at all.

    * Variable Name: replicate_events_marked_for_skip
    * Scope: Global
    * Access Type: Dynamic
    * Data Type: enum: REPLICATE | FILTER_ON_SLAVE | FILTER_ON_MASTER
    * Default Value: REPLICATE

Note that replicate_events_marked_for_skip is a dynamic variable (can be
changed without restarting the server), however the slave threads must be
stopped when it is changed, otherwise an error will be thrown.

When events are filtered due to @@skip_replication, the filtering happens on
the master side; that is the event is never send to the slave. If many events
are filtered like this, a slave can sit a long time without receiving any
events from the master. This is not a problem in itself, but must be kept in
mind when inquiring on the slave about events that are filtered. For example
START SLAVE UNTIL <some position> will stop at the first event that is _not_
filtered is encountered at the given position or beyond. If the event at the
given position is filtered, then the slave thread will only stop when the next
non-filtered event is encountered. In effect, if an event is filtered, to the
slave it appears that it was never written to the binlog on the master.

Note that when events are filtered for a slave, the data in the database will
be different on the slave and on the master. It is the responsibility of the
application to replicate the data outside of the built-in replication or
otherwise ensure consistency of operation. If this is not done, it is possible
for replication to encounter eg. UNIQUE contraint violations or other problems
that will cause replication to stop and require manual intervention to fix.

The session variable @@skip_replication can be changed without requiring
special privileges. This makes it possible for normal applications to control
it without requiring SUPER privileges. But it must be kept in mind when using
slaves with --replicate-events-marked-for-skip set different from REPLICATE,
as it allows any connection to do changes that are not replicated.

See also the @@sql_log_bin variable. @@sql_log_bin and @@skip_replication are
somewhat related, as they can both be used to prevent a change on the master
from being replicated to the slave. The difference is that with
@@skip_replication, changes are still written into the binlog, and replication
of the events in only skipped on slaves that explicitly are configured to do
so, with --replicate-events-marked-for-skip different from REPLICATE. With
@@sql_log_bin, events are not logged into the binlog, and so are not
replicated by any slave.

When events in the binlog are marked with the @@skip_replication flag, the
flag will be preserved if the events are dumped by the mysqlbinlog program and
re-applied against a server with the mysql client program. Similarly, the
BINLOG statement will preserve the flag from the event being replayed. And a
slave that runs with --log-slave-updates and does not filter events
(--replicate-events-marked-for-skip=REPLICATE) will also preserve the flag in
the events logged into the binlog on the slave.
-----------------------------------------------------------------------
 Task Dependencies
Others waiting for Task 234Task 234 is waiting forGraph
 
 High-Level Specification
Allocate the new flag bit from the end of the flag word; this will help avoid
conflicts with any new flag allocated by MySQL@Oracle.

We need to ensure that @@skip_replication is not changed in the middle of a
transaction (a transaction must always be binlogged as a whole; either all of
it or none of it). We should be able to do this the same way the similar
problem is handled for @@sql_log_bin.

When --replicate-events-marked-for-skip is FILTER_ON_MASTER, the IO thread
will send the statement `SET SESSION skip_replication=1` to the master when
connecting (and ignore the error if the variable does not exist on the
master). (So we re-use this variable for a different, if related, purpose in
the dump thread). This ensures:

1. That the master will know it must filter skip_replication events for this
   slave.

2. That a new slave will work on an old master.

3. That the master will know when to filter events in a way that is compatible
   with old slaves (old slave will not set @@skip_replication, and so will
   just not be filtered).

--replicate-events-marked-for-skip can only be changed if the slave (both IO
and SQL threads) is stopped. This solves problems with communicating to the
master the new setting as well as avoids changing it in the middle of
replicating a group/transaction, which could cause 1/2 a transaction to be
replicated.

When --replicate-events-marked-for-skip is set to REPLICATE, and
--log-slave-updates is also set, the slave must preserve the
@@skip_replication flag in the events binlogged on the slave.

When filtering events on the master, we will be sending an event stream to the
slave with holes with respect to the embedded binlog positions. For this to
work, the slave code needs to be checked for places where this will break, and
the relevant code fixed.

Care is needed concerning the interaction between the @@skip_replication flag
and the @@sql_slave_skip_counter flag. When an event is skipped due to the
@@skip_replication flag, it will _not_ be counted towards the number of events
to be skipped in @@sql_slave_skip_counter. If this was implemented
differently, the behaviour would not be consistent between filtering on the
master (--replicate-events-marked-for-skip=FILTER_ON_MASTER) and filtering on
the slave (--replicate-events-marked-for-skip=FILTER_ON_SLAVE).


Changes for mysqlbinlog
-----------------------

mysqlbinlog needs to output the value of the @@skip_replication flag as SET
@@skip_replication, so that if used to do point-in-time recovery, the flag
will be correctly restored to events.

This is a bit tricky, as a statement SET skip_replication=0 will give an
error message on a server that does not have this feature. So we will do it
like this:

    /*!50400 SET skip_replication=1 */;

But this only works for MariaDB; it will still fail for Oracle versions of
MySQL. The error message on non-supporting servers is not fatal (unless
--abort--source-on-error), but it is still annoying if we would get this error
on _every_ mysqlbinlog file played against a MySQL@Oracle server.

So my suggestion is that mysqlbinlog will assume that @@skip_replication is
set to OFF, and will only output the /*!50400 SET skip_replication=1 */
statement if it sees an event with the flag set. This avoids the error in the
common cases (and when it does give the error, it indicates that the server
does not fully support the data being loaded, which is fine).

mysqlbinlog should probably have an option to omit events with the flag set
(by requesting filtering on server side or local filtering if reading binlog
files directly).
 Low-Level Design
Tests needed
------------

(List not yet exhaustive.)

 - Test behaviour when @@skip_replication is changed in the middle of a
   transactions, and in the middle of a statement. Different combinations of
   being set/unset at start/end of transaction, mutiple times changed in
   single transaction, etc. Check for both statement-based and row-based
   events.

 - Test that slave with --replicate-events-marked-for-skip set to
   FILTER_ON_SLAVE or FILTER_ON_MASTER really does ignore the events.

 - Test that @@skip_replication is preserved on slave with
   --replicate-events-marked-for-skip=1 and --log-slave-updates=1.

 - Test that @@skip_replication is preserved by mysqlbinlog|mysql

 - Test that @@skip_replication flag is preserved by BINLOG statement.

 - Test that master-side filtering is done (check relay logs).

 - Test that FILTER_ON_MASTER really does filtering on the master (events do
   not reach the slave).

 - Test that FILTER_ON_SLAVE really does filter on the slave (events reach the
   slave relay log, but are filtered by the SQL thread).
 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Knielsen5816 Aug 2011
Total58 
 Hrs WorkedProgressCurrentOriginal
This Task5800
Total5800
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Knielsen - Fri, 02 Mar 2012, 07:01
    
Version updated.
--- /tmp/wklog.234.old.21294	2012-03-02 07:01:58.000000000 +0000
+++ /tmp/wklog.234.new.21294	2012-03-02 07:01:58.000000000 +0000
@@ -1,2 +1,2 @@
-Server-9.x
+Server-5.5
 

(Knielsen - Fri, 02 Mar 2012, 07:01
    
Status updated.
--- /tmp/wklog.234.old.21294	2012-03-02 07:01:58.000000000 +0000
+++ /tmp/wklog.234.new.21294	2012-03-02 07:01:58.000000000 +0000
@@ -1,2 +1,2 @@
-Assigned
+In-Documentation
 

(Knielsen - Fri, 02 Mar 2012, 07:01
    
Documentation updated: Knielsen -> Dbart

(Knielsen - Tue, 16 Aug 2011, 10:41
    
Implement that user can choose between master-side and slave-side filtering.

(Knielsen - Tue, 16 Aug 2011, 10:07
    
Low Level Design modified.
--- /tmp/wklog.234.old.20840	2011-08-16 10:07:13.000000000 +0000
+++ /tmp/wklog.234.new.20840	2011-08-16 10:07:13.000000000 +0000
@@ -9,8 +9,8 @@
    single transaction, etc. Check for both statement-based and row-based
    events.
 
- - Test that slave with --replicate-events-marked-for-skip=0 really does
-   ignore the events.
+ - Test that slave with --replicate-events-marked-for-skip set to
+   FILTER_ON_SLAVE or FILTER_ON_MASTER really does ignore the events.
 
  - Test that @@skip_replication is preserved on slave with
    --replicate-events-marked-for-skip=1 and --log-slave-updates=1.
@@ -21,4 +21,10 @@
 
  - Test that master-side filtering is done (check relay logs).
 
+ - Test that FILTER_ON_MASTER really does filtering on the master (events do
+   not reach the slave).
+
+ - Test that FILTER_ON_SLAVE really does filter on the slave (events reach the
+   slave relay log, but are filtered by the SQL thread).
+
 

(Knielsen - Tue, 16 Aug 2011, 10:05
    
High-Level Specification modified.
--- /tmp/wklog.234.old.20807	2011-08-16 10:05:36.000000000 +0000
+++ /tmp/wklog.234.new.20807	2011-08-16 10:05:36.000000000 +0000
@@ -6,11 +6,11 @@
 it or none of it). We should be able to do this the same way the similar
 problem is handled for @@sql_log_bin.
 
-When --replicate-events-marked-for-skip is false, the IO thread will send the
-statement `SET SESSION skip_replication=1` to the master when connecting (and
-ignore the error if the variable does not exist on the master). (So we re-use
-this variable for a different, if related, purpose in the dump thread). This
-ensures:
+When --replicate-events-marked-for-skip is FILTER_ON_MASTER, the IO thread
+will send the statement `SET SESSION skip_replication=1` to the master when
+connecting (and ignore the error if the variable does not exist on the
+master). (So we re-use this variable for a different, if related, purpose in
+the dump thread). This ensures:
 
 1. That the master will know it must filter skip_replication events for this
    slave.
@@ -27,9 +27,9 @@
 replicating a group/transaction, which could cause 1/2 a transaction to be
 replicated.
 
-When --replicate-events-marked-for-skip is set, and --log-slave-updates is
-also set, the slave must preserve the @@skip_replication flag in the events
-binlogged on the slave.
+When --replicate-events-marked-for-skip is set to REPLICATE, and
+--log-slave-updates is also set, the slave must preserve the
+@@skip_replication flag in the events binlogged on the slave.
 
 When filtering events on the master, we will be sending an event stream to the
 slave with holes with respect to the embedded binlog positions. For this to
@@ -41,7 +41,8 @@
 @@skip_replication flag, it will _not_ be counted towards the number of events
 to be skipped in @@sql_slave_skip_counter. If this was implemented
 differently, the behaviour would not be consistent between filtering on the
-master (stage 2 of the implementation) and filtering on the slave (stage 1).
+master (--replicate-events-marked-for-skip=FILTER_ON_MASTER) and filtering on
+the slave (--replicate-events-marked-for-skip=FILTER_ON_SLAVE).
 
 
 Changes for mysqlbinlog

(Knielsen - Tue, 16 Aug 2011, 10:02
    
High Level Description modified.
--- /tmp/wklog.234.old.20651	2011-08-16 10:02:25.000000000 +0000
+++ /tmp/wklog.234.new.20651	2011-08-16 10:02:25.000000000 +0000
@@ -5,14 +5,15 @@
 the binlog.
 
 The first stage of this worklog is to introduce an option
---replicate-events-marked-for-skip. This defaults to true, but when set to
-false, the slave will skip any events received with the @@skip_replication bit
-set.
+--replicate-events-marked-for-skip. This defaults to REPLICATE, which
+replicates all events irregardless of @@skip_replication. But when set to
+FILTER_ON_SLAVE, the slave will skip any events received with the
+@@skip_replication bit set.
 
 The second stage is to do this filtering on the master. When
---replicate-events-marked-for-skip is false on the slave, the master will
-avoid sending these events to that slave at all, saving on bandwidth but
-otherwise working the same way as stage 1.
+--replicate-events-marked-for-skip is set to FILTER_ON_MASTER on the slave,
+the master will avoid sending these events to that slave at all, saving on
+bandwidth but otherwise working the same way as stage 1.
 
 The motivation for this worklog is to allow an application to do changes that
 are replicated in a way external to the internal MySQL replication, but which
@@ -43,7 +44,8 @@
 
 When this variable is set true, following changes are logged into the binlog
 with the flag @@skip_replication set. Such events will not be replicated by
-slaves that run with --replicate-events-marked-for-skip set to false.
+slaves that run with --replicate-events-marked-for-skip set different from
+its default of REPLICATE.
 
     * Variable Name: skip_replication
     * Scope: Session only
@@ -62,15 +64,17 @@
 Slave option:: --replicate-events-marked-for-skip
 
 This option tells the slave whether to replicate events that are marked with
-the @@skip_replication flag. Default is TRUE, to ensure that all changes are
-replicated to the slave. If set to FALSE, events so marked will not be
-replicated to the slave.
+the @@skip_replication flag. Default is REPLICATE, to ensure that all changes
+are replicated to the slave. If set to FILTER_ON_SLAVE, events so marked will
+be skipped on the slave and not replicated. If set to FILTER_ON_MASTER, the
+filtering will be done on the master, saving on network bandwidth as the
+events will not be received by the slave at all.
 
     * Variable Name: replicate_events_marked_for_skip
     * Scope: Global
     * Access Type: Dynamic
-    * Data Type: bool
-    * Default Value: ON
+    * Data Type: enum: REPLICATE | FILTER_ON_SLAVE | FILTER_ON_MASTER
+    * Default Value: REPLICATE
 
 Note that replicate_events_marked_for_skip is a dynamic variable (can be
 changed without restarting the server), however the slave threads must be
@@ -97,24 +101,25 @@
 The session variable @@skip_replication can be changed without requiring
 special privileges. This makes it possible for normal applications to control
 it without requiring SUPER privileges. But it must be kept in mind when using
-slaves with --replicate-events-marked-for-skip=0, as it allows any connection
-to do changes that are not replicated.
+slaves with --replicate-events-marked-for-skip set different from REPLICATE,
+as it allows any connection to do changes that are not replicated.
 
 See also the @@sql_log_bin variable. @@sql_log_bin and @@skip_replication are
 somewhat related, as they can both be used to prevent a change on the master
 from being replicated to the slave. The difference is that with
 @@skip_replication, changes are still written into the binlog, and replication
 of the events in only skipped on slaves that explicitly are configured to do
-so, with --replicate-events-marked-for-skip=1. With @@sql_log_bin, events are
-not logged into the binlog, and so are not replicated by any slave.
+so, with --replicate-events-marked-for-skip different from REPLICATE. With
+@@sql_log_bin, events are not logged into the binlog, and so are not
+replicated by any slave.
 
 When events in the binlog are marked with the @@skip_replication flag, the
 flag will be preserved if the events are dumped by the mysqlbinlog program and
 re-applied against a server with the mysql client program. Similarly, the
 BINLOG statement will preserve the flag from the event being replayed. And a
 slave that runs with --log-slave-updates and does not filter events
-(--replicate-events-marked-for-skip=1) will also preserve the flag in the
-events logged into the binlog on the slave.
+(--replicate-events-marked-for-skip=REPLICATE) will also preserve the flag in
+the events logged into the binlog on the slave.
 -----------------------------------------------------------------------
 
 

(Knielsen - Fri, 12 Aug 2011, 12:37
    
Implement stage 2, master-side filtering.
More testing, cleanup.
Get review, after-review fixes. Including better names for the new system variables.
Protect against changing @@skip_replication inside of a transaction or statement.
Write documentation.
Merge and push to appropriate trees.
Worked 24 hours and estimate 0 hours remain (original estimate increased by 24 hours).

(Knielsen - Fri, 12 Aug 2011, 10:12
    
Documentation signoff

(Knielsen - Fri, 12 Aug 2011, 10:12
    
High Level Description modified.
--- /tmp/wklog.234.old.4320	2011-08-12 10:12:21.000000000 +0000
+++ /tmp/wklog.234.new.4320	2011-08-12 10:12:21.000000000 +0000
@@ -19,4 +19,102 @@
 still keeps the events in the binlog for debugging/point-in-time recovery, or
 for standard replication to other servers.
 
+User documentation:
+
+-----------------------------------------------------------------------
+Selectively skipping replication of binlog events
+-------------------------------------------------
+
+Normally, all changes that are logged as events in the binlog are also
+replicated to all slaves (though still subject to filtering by
+--replicate-do-xxx, --replicate-ignore-xxx, and similar options). However,
+sometimes it may be desirable to have certain events be logged into the
+binlog, but not be replicated to all or a subset of slaves, where the
+distinction between events that should be replicated or not is under the
+control of the application making the changes.
+
+This could be useful if an application does some replication external to the
+server outside of the built-in replication, or if it has some data that should
+not be replicated for whatever reason.
+
+This is possible with two new system variables introduced in MariaDB 5.5:
+
+Master session variable: @@skip_replication
+
+When this variable is set true, following changes are logged into the binlog
+with the flag @@skip_replication set. Such events will not be replicated by
+slaves that run with --replicate-events-marked-for-skip set to false.
+
+    * Variable Name: skip_replication
+    * Scope: Session only
+    * Access Type: Dynamic
+    * Data Type: bool
+    * Default Value: OFF
+
+The @@skip_replication option only has effect if binary logging is enabled and
+@@sql_log_bin is true.
+
+Attempting to change @@skip_replication in the middle of a transaction will
+fail; this is to avoid getting half of a transaction replicated while the
+other half is not replicated. Be sure to end any current transaction with
+COMMIT/ROLLBACK before changing the variable.
+
+Slave option:: --replicate-events-marked-for-skip
+
+This option tells the slave whether to replicate events that are marked with
+the @@skip_replication flag. Default is TRUE, to ensure that all changes are
+replicated to the slave. If set to FALSE, events so marked will not be
+replicated to the slave.
+
+    * Variable Name: replicate_events_marked_for_skip
+    * Scope: Global
+    * Access Type: Dynamic
+    * Data Type: bool
+    * Default Value: ON
+
+Note that replicate_events_marked_for_skip is a dynamic variable (can be
+changed without restarting the server), however the slave threads must be
+stopped when it is changed, otherwise an error will be thrown.
+
+When events are filtered due to @@skip_replication, the filtering happens on
+the master side; that is the event is never send to the slave. If many events
+are filtered like this, a slave can sit a long time without receiving any
+events from the master. This is not a problem in itself, but must be kept in
+mind when inquiring on the slave about events that are filtered. For example
+START SLAVE UNTIL <some position> will stop at the first event that is _not_
+filtered is encountered at the given position or beyond. If the event at the
+given position is filtered, then the slave thread will only stop when the next
+non-filtered event is encountered. In effect, if an event is filtered, to the
+slave it appears that it was never written to the binlog on the master.
+
+Note that when events are filtered for a slave, the data in the database will
+be different on the slave and on the master. It is the responsibility of the
+application to replicate the data outside of the built-in replication or
+otherwise ensure consistency of operation. If this is not done, it is possible
+for replication to encounter eg. UNIQUE contraint violations or other problems
+that will cause replication to stop and require manual intervention to fix.
+
+The session variable @@skip_replication can be changed without requiring
+special privileges. This makes it possible for normal applications to control
+it without requiring SUPER privileges. But it must be kept in mind when using
+slaves with --replicate-events-marked-for-skip=0, as it allows any connection
+to do changes that are not replicated.
+
+See also the @@sql_log_bin variable. @@sql_log_bin and @@skip_replication are
+somewhat related, as they can both be used to prevent a change on the master
+from being replicated to the slave. The difference is that with
+@@skip_replication, changes are still written into the binlog, and replication
+of the events in only skipped on slaves that explicitly are configured to do
+so, with --replicate-events-marked-for-skip=1. With @@sql_log_bin, events are
+not logged into the binlog, and so are not replicated by any slave.
+
+When events in the binlog are marked with the @@skip_replication flag, the
+flag will be preserved if the events are dumped by the mysqlbinlog program and
+re-applied against a server with the mysql client program. Similarly, the
+BINLOG statement will preserve the flag from the event being replayed. And a
+slave that runs with --log-slave-updates and does not filter events
+(--replicate-events-marked-for-skip=1) will also preserve the flag in the
+events logged into the binlog on the slave.
+-----------------------------------------------------------------------
+
 
-- View All Progress Notes (27 total) --


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB