Thank you both, for your helpful responses.
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.
I actually lied earlier about binlog_ignore_db (though not intentionally, was just looking in the wrong spot), it is in fact set to norep on all four servers. As I recall the problem we had when we set these up years ago was that it would only block the statements from the binlog if norep was the default database at the time the statement occurred. We rarely set norep as the default DB (just create directly via CREATE TABLE norep.someTable), thus the replicate_ignore and replicate_wild_ignore seemed to be the best alternative to make sure the statements didn't actually get applied on the slaves. So, it would seem to make it work in my case I'll need to change some code after all.
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.
Yah, it's at least similar if not the same. But I guess my question is, isn't the presence of these unwanted DROP TEMPORARY TABLE statements in and of itself enough to prevent the mistake-free possibility of redirecting a lower slave to a higher master at some point in the future? I.E., I want to point Slave D to Master B (because I've brought down Master C for maintenance) but doing so fails because Slave D currently happens to be pointing to a DROP TEMPORARY TABLE statement that was injected on Slave C? Does setting sql_log_bin=0 prior to the creation of the temporary table also prevent the creation of these DROP statements? Thanks! Dan