3

I have a multi-master replication setup up and running well in MariaDB. Each node has its own server-id and gtid_domain_id and is set up with its own master-slave and slave-master (two-way) replication. All working well.

I'm running into an issue now though as servers come online and offline, the Gtid_Slave_Pos tends to get 'stuck'. The first set of servers set up, the Gtid_Slave_Pos looked reasonable Gtid_Slave_Pos: 0-1-45678,1-1-12312,2-2-45645,3-3-12312

Then, for hardware reasons, server-id 3 was taken offline and rebuilt. When restoring the database from the master, the GTID changed. The backup taken with mysqldump -v --master-data=2 --single-transaction --lock-tables=false production_db now has GTID 3-3 at a different value.

Attempting to reset the gtid_slave_pos, now throws off the other nodes replication since the GTIDs don't match. My only workaround is to ditch server-id 3, and create a new server-id 4. Which worked, but isn't really sustainable.

Here's the specific example: mysqldump -v --master-data=2 --single-transaction --lock-tables=false apps_production > dump.sql Includes the GTID information: -- SET GLOBAL gtid_slave_pos='9-9-137474,8-8-40418,7-7-82178,2-2-6743,1-1-4836503,0-1-2805613,3-3-12317,6-6-202460,5-5-40139';

Then querying select * from mysql.gtid_slave_pos; at the same time gives: +-----------+--------+-----------+---------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+---------+ | 0 | 556017 | 1 | 2805613 | Match | 1 | 556023 | 1 | 4836503 | Match | 2 | 556019 | 2 | 6743 | Match | 3 | 556024 | 3 | 12317 | Match | 5 | 556020 | 5 | 20832 | | 6 | 556021 | 6 | 202460 | Match | 9 | 556022 | 9 | 140055 | +-----------+--------+-----------+---------+ Notice 5-5 and 9-9 are different, and basically prevent replication from restarting when I set the GTID for those servers.

Onto the Question: Is there a safe/easy way to remove no longer used Gtid domain/servers? In this case, I'd like to kill 3-3, and have the new slave start the gtid from scratch. It looks like it's all stored in mysql.gtid_slave_pos, but I'm a bit terrified to touch that table.

Any ideas/suggestions?

  • Were you ever able to answer this question to your own satisfaction? – Ben Johnson May 23 '18 at 17:56
  • @BenJohnson nope, never really got it resolved. Ended up talking to a few MariaDB consultant-types, and they recommended moving away from the two-way master/slave ring setup; as it's not a 'recommended' topology. We ended up re-architecting our setup to simplify the database architecture and rely more on application-level sync where needed. – Mike May 24 '18 at 18:48
  • Thanks for the follow-up; I appreciate it! We have an app running on a number of devices "in the field", and it needs to work seamlessly, with or without network access, and require no user interaction for syncing records among all devices whenever online. We can't use Galera Cluster because a portion of the databases/tables on any given machine should not be replicated (and I don't like the "convert those to MyISAM" hack). I've exhausted all other replication topologies and am bound to encounter the scenario you describe sooner or later. I'll post an answer if I figure it out. ;) – Ben Johnson May 24 '18 at 20:05
  • @BenJohnson That's a similar setup to what we needed. The 'in the field' is the hard part. We learned the hard way that rebuilding remote databases in a two-way setup is scary, and can easily result in the core database being dropped. It worked well when it worked, but was a nightmare at other times. That's why we went to an application-level sync, it wasn't worth the risk and the admin time it was taking. Now we have a stable and working process that just needs the app running on the remote machines, they catch-up, and while we made some compromises (conflict records, etc), it works! – Mike May 29 '18 at 12:54
  • Thanks for the valuable insights! Precisely the same issue has me worried: it seems all too easy to drop a database or tables across all masters in the ring due to a botched rebuild on one of the in-the-field machines. I wonder if Galera Cluster would mitigate that potential, even if it means foregoing the ability to replicate data selectively; presumably, it would be as simple as a node joining or leaving the cluster, and no data would be dropped. Some form of app-level sync is looking more attractive by the minute! I'll post a follow-up once I get my feet wet... – Ben Johnson May 30 '18 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.