MariaDB: Selective binary logs eventsFrederic Descamps
In the first post in a series on MariaDB features we find interesting, we begin with selectively skipping replication of binlog events. This feature is available on MariaDB 5.5 and 10.
By default when using MySQL’s standard replication, all events are logged in the binary log and those binary log events are replicated to all slaves (it’s possible to filter out some schema). But with this feature, it’s also possible to bypass some events to be replicated on the slave(s) even if they are written in the binary log. Having those event in the binary logs is always useful for point-in-time recovery.
Indeed, usually when we need to not replicate an event, we set
sql_log_bin = 0 and the event is bypassed: neither written into the binlog, neither replicated to slave(s).
So with this new feature, it’s possible to just set a session variable to tag events that will be written into the binary log and bypassed on demand on some slaves.
And it’s really easy to use, on the master you do:
and on the slave(s) having
'FILTER_ON_SLAVE' the events skipped on the master won’t be replicated.
The valid values for replicate_events_marked_for_skip are:
REPLICATE(default) : skipped events are replicated on the slave
FILTER_ON_SLAVE: events so marked will be skipped on the slave and not replicated
FILTER_ON_MASTER: the filtering will be done on the master so the slave won’t even receive it and then save network bandwidth
That’s a cool feature but when this can be very useful?
For archiving this can be very interesting. Indeed most of the time when people is archiving data, they use something like pt-archiver that deletes the data and copy the removed data on an archive server.
Thanks to this feature, instead of having an archiving server where we copy the deleted data, it’s possible to have a slave where we won’t delete the data. This will be much faster (smarter?) and allows to have an archiving server always up to date. Of course in this case
sql_log_bin = 0 would have worked (if we ignore the point-in-time recovery).
But with a Galera Cluster? Yes that’s where this feature is really cool, if we would have used
sql_log_bin = 0 on a Galera Cluster node, all other nodes would have ignored the delete and the result would be inconsistency between the nodes.
So if you use an asynchronous slave as an archiving server of a Galera Cluster, this feature is really mandatory.
As illustrated below, you can have a MariaDB Galera Cluster node joining a Percona XtraDB Cluster that will be used to delete historical data using pt-archiver:
pt-archiver is started with