maria-discuss team mailing list archive

[andrei.elkin@xxxxxxxxxx]

Hello.

> 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.%
>
...
> 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).

Indeed.

To this matter
   CHANGE MASTER {DO,IGNORE}_DOMAIN_IDS
could've been defined to block certain domain transaction from sending
by master. But it works conformly to the replicate db rules.

>
> 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.
>

This one must relate:
MDEV-17863 DROP TEMPORARY TABLE creates a transaction in binary log on read only server.

Cheers,

Andrei