Hello.
mariadb@Biblestuph.com 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