maria-discuss team mailing list archive
Mailing list archive
gtid and current_pos vs slave_pos
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
/*!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
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:
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
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?