[Maria-discuss] 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 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
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.%
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.
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?
The gtid_slave_pos on D is the current position _within the binlog of C_ (C being the master of D). The filtering you set up happens on the slave side D, not on the master side C. So even the norep transactions on C are still "replicated" in the sense that they are sent to D and processed (including updating the gtid_slave_pos value). The filtering just causes skipping the actual changes to tables or data. If D happens to disconnect from C at the point of a "norep" transaction, it will need to restart from that position when it reconnects later.
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.
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). 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. Hope this helps, - Kristian.
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
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
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
not work, as you suspect.
The simplest way is to just set sql_log_bin=0 when doing local
(Sending again, as I don't think my original reply went to the list; apologies if I duplicate.) Thank you both, for your helpful responses. this will 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
mariadb@Biblestuph.com writes:
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?
Well, it should prevent it. (But since it is a bug causing these redundant drop table statements to be created in the first place, it is hard to be sure...) - Kristian.
participants (4)
-
andrei.elkin＠pp.inet.fi -
Daniel Ragle -
Kristian Nielsen -
mariadb＠Biblestuph.com