MariaDB: Selective binary log event

MariaDB: Selective binary log event

As the first article on MariaDB's series of interesting features, we started from selectively skipping the replication of binlog events. MariaDB 5.5 and 10 support this feature.

By default, when the MySQL standard replication function is used, all events are recorded in binary logs and copied to all slave servers (some modes can be filtered out ). However, with this feature, you can skip copying some events from the machine, even if these events are written to binary logs. Saving events in binary logs is always useful for time point recovery.

Indeed, when you do not need to copy an event, we usually set SQL _log_bin = 0, and the event will be skipped: neither the binlog nor the slave will be written.

However, to use this new feature, you only need to set a session-level variable to mark some events so that these events can be written to binary logs and skipped on some slaves.

This feature is really easy to use. You need to set it on the host:

Set skip_replication = 1;

And set replicate_events_marked_for_skip = 'filter _ ON_MASTER 'or 'filter _ ON_SLAVE' on the slave machine. The events skipped on the host will not be copied.

Valid values of replicate_events_marked_for_skip include:

 

• REPLICATE (default): Copy skipped events from the opportunity

• FILTER_ON_SLAVE: skips the marked events from the opportunity and does not copy

• FILTER_ON_MASTER: the filtering operation will be completed on the host, so the slave will not receive the skipped event, thus saving network bandwidth.

This is a cool feature, but when will it be particularly useful?

Use Case:

It is very interesting to use archive. Indeed, many times when people archive data, they use tools such as pt-archiver to delete the data and copy the deleted data on the archiving server.

With this feature, we can prevent a slave from deleting the data, instead of using the archive server to copy the deleted data. This will be much faster (more intelligent ?), In this way, the archive server is always up-to-date. Of course, in this case, SQL _log_bin = 0 will work (if we ignore the time point recovery ).

But what about Galera Cluster? Yes, this is a really cool feature. If we use SQL _log_bin = 0 on a Galera Cluster node, all other nodes will ignore the delete operation, in this way, nodes are inconsistent.

Therefore, if you use an asynchronous slave machine as the archiving server of the Galera Cluster, this feature is really necessary.

As shown in, you can add a MariaDB Galera Cluster node to Percona XtraDB Cluster to delete historical data with pt-archiver:

Pt-archiveris is started with the -- set-vars "skip_replication = 1" parameter.

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

MariaDB details: click here
MariaDB's: click here

This article permanently updates the link address: