maria-discuss team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

mariadb@xxxxxxxxxxxxxx writes:

> I have four servers all running 10.3 as follows:
>
>    A <=> B => C => D

> and C is a master to D. In addition to their actual replicating DBs,
> all four servers also have a "norep" DB that is used to create
> temporary tables for local report processing (as well as any other
> possible writes we might want to make locally without affecting the
> slave chain). Historically we've prevented replication for the norep
> DB via:
>
>     replicate_ignore_db = mysql,norep
>     replicate_wild_ignore_table = mysql.%,norep.%

> binlog_do/ignore flavor of filters are all unset). Writes to the A and
> B servers are programmatically controlled such that only one of the
> two servers will accept writes at any given moment.

> Specifically, when I look at the gtid_slave_pos on server D, which I
> thought was only supposed to reflect transactions that were actually
> replicated, I sometimes see statements coming from server C; these are
> temporary tables being written into norep on C. They are not actually
> replicating on D (at least as far as I can tell), and they don't show up
> D's binary log. So why would they be reflected in D's gtid_slave_pos?

The gtid_slave_pos on D is the current position _within the binlog of C_ (C
being the master of D). The filtering you set up happens on the slave side
D, not on the master side C. So even the norep transactions on C are still
"replicated" in the sense that they are sent to D and processed (including
updating the gtid_slave_pos value). The filtering just causes skipping the
actual changes to tables or data. If D happens to disconnect from C at the
point of a "norep" transaction, it will need to restart from that position
when it reconnects later.

> For example, just a moment ago SELECT @@GLOBAL.gtid_slave_pos on D
> showed this:
>
>     1-303-48758339
>
> This transaction does not appear in D's binlog, which I would expect
> since it should not in fact actually be replicated. But because it is
> reflected in gtid_slave_pos, it seems to me that in my setup I cannot
> reliably use gtid_current_pos or gtid_slave_pos, since either may at
> any given time point to an entry on C that of course won't exist on B
> should I ever want to redirect D to B.

Yes. Using replicate_ignore_db is not appropriate for doing local changes on
one server that should be invisible to the replication chain. So this will
not work, as you suspect.

The simplest way is to just set sql_log_bin=0 when doing local transactions
on a slave - this avoids the statements being written to the binlog in the
first place. No replicate_ignore_db options are needed then.

It's possible you can achieve something similar using binlog_ignore_db
instead (I don't 100% recall all details, but from the documentation it
looks like it might work).

Your current setup is effectively multi-master from the point of view of
GTID (all servers written concurrently), even though you then
replicate_ignore_db changes from all but one server. As described in the
documentation, GTID can handle multi-master setups using gtid_domain_id, but
I think that is much more complicated than needed for your actual usecase.
Just using sql_log_bin=0 (or possibly binlog_ignore_db) should be fine.

> DROP TEMPORARY TABLE IF EXISTS `norep`.`locations` /* generated by server */
> /*!*/;
>
> How is it that that statement made it all the way through to server D
> from B? Shouldn't it have been filtered out by server C?

I vaguely recall an old bug that causes in particular redundant DROP
TEMPORARY TABLES statement to be unnecessarily written to the binlog. Maybe
this bug is still there and causing this.

Hope this helps,

 - Kristian.