maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

I have four servers all running 10.3 as follows:

   A <=> B => C => D

where server_ids are:

   A => 301
   B => 302
   C => 303
   D => 304

I.E., A is a master to B, B is a master to A, B is also a master to C, 
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.%

Which is set as such on all four servers (the replicate_do and 
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.

Server D writes binlogs for potential backup/disaster recovery, but has 
no attached slave.

All that in mind, looking at the binary logs on D, I see this statement:

# at 51363132
#190423  9:07:51 server id 302  end_log_pos 51363170    GTID 
1-302-47886064 ddl
/*!100001 SET @@session.server_id=302*//*!*/;
/*!100001 SET @@session.gtid_seq_no=47886064*//*!*/;
# at 51363170
#190423  9:07:51 server id 302  end_log_pos 51363324    Query 
thread_id=345449        exec_time=1800  error_code=0
SET TIMESTAMP=1556024871/*!*/;
SET @@session.pseudo_thread_id=345449/*!*/;
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?

Next, I'm reading up on gtid-based replication with the thought that I 
would love to be able to do things like bring down C for maintenance and 
temporarily point D to B. I read the docs here:

    https://mariadb.com/kb/en/library/gtid/

however, in looking at the actual binary logs of the servers they don't 
seem to reflect the explanations that I read there especially in regards 
to gtid_current_pos vs. gtid_slave_pos.

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?

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.

Am I missing or mis-understanding something here?

Cheers,

Dan