Saltare selettivamente la replica degli eventi del binlog
Note: This page describes features in the source repository for MariaDB 5.5. There are currently no official packages or binaries available for download which contain the features. If you want to try out any of the new features described here you will need to get and compile the code yourself.
Contents
Normalmente, tutte le modifiche sono registrate come eventi nel binlog e sono anche replicate in tutti gli slave (anche se possono ancora essere filtrate con le opzioni --replicate-do-xxx, --replicate-ignore-xxx
e simili). Tuttavia, a volte può essere desiderabile che alcuni eventi siano registrati nel binlog, ma non replicati dagli slave, dove la distinzione tra gli eventi non dovrebbe essere replicata o non è sotto il controllo dell'applicazione che esegue le modifiche.
Questo può essere utile se un'applicazione effettua una replica esterna al server, non attraverso la replica built-in, o se ha dei dati che non devono essere replicati per una qualsiasi ragione.
Questo è possibile grazie a due nuove variabili di sistema introdotte in MariaDB 5.5:
Master session variable: @@skip_replication
When this variable is set to true, the 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: replicate_events_marked_for_skip is a dynamic variable (it 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; in other words, the event is never sent 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 when 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, for example,
UNIQUE contraint violations or
other problems which 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.
skip_replication and sql_log_bin
@@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 is
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.
skip_replication and the binlog
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 which 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.
