1

I have currently two DB instanced with two-way replication. Each replicates other database. So both are master and slave. So every change in any of these databases is automatically replicated to the other. This works fine.

But now I need one read-only backup solution, which will have also a whole database. When I replicate just the first instance of my original DB, my new backup has just changed done on this instance all changes from the second one are missing.

So probably the only way is multi-source (multi-master) replication. This is possible by (MaridDB syntax)

CHANGE MASTER "A" TO MASTER_HOST = '192.168.8.2', MASTER_USER = 'replicator', MASTER_PASSWORD = '...', master_use_gtid = slave_pos;
CHANGE MASTER "B" TO MASTER_HOST = '192.168.8.3', MASTER_USER = 'replicator', MASTER_PASSWORD = '...', master_use_gtid = slave_pos;

And I need to setup multiple gtid_slave_pos by

SET GLOBAL gtid_slave_pos = "1-1-19124092,2-2-7060816";

And this is a problematic part.

I'm using this syntax to initial clone of the whole database to 3rd server

sudo mysqldump --gtid --single-transaction --master-data dbName | pv | gzip > db-dump-fixed.sql.gz

It automatically put there SET GLOBAL gtid_slave_pos='2-2-7027154';

But how I can get the current gtid_slave_pos for the second database instance? It must be exactly the same state as it was at the moment of sudo mysqldump execute. If I will do it manually I can miss some transactions in the second database.

Improve this question

1 Answer 1

Reset to default
2

I recommend you enable log_slave_updates on both of your masters. Then they will add the replicated changed to their own binary log, so both instances' binary logs will have all changes. Then you don't need multi-master replication.

See https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#log_slave_updates

Improve this answer
4
  • Thanks a lot. I will try. Do I really need to set up it on both instances?. Setup it just once should be enough. I'm just not sure on which one. Probably on the one which I want to use as a source for my backup, right?
    – ATom
    Jan 17, 2022 at 21:24
  • Setting it up on both source nodes allows you to make a replica that can change its source to either node. We do this at my company -- all nodes log all changes, and this allows us to rearrange replication any way we need to handle host failures.
    – Bill Karwin
    Jan 17, 2022 at 21:32
  • Thanks. It makes sense. There is even not clear if the inside of .cnf should be declared as log-slave-updates or log_slave_updates because in the documentation it is with _ and as command line switch by -. And currently I have inside of configuration everything with -. But it looks that both variants will work.
    – ATom
    Jan 17, 2022 at 21:34
  • Within option names, dash (-) and underscore (_) may be used interchangeably.
    – Bill Karwin
    Jan 17, 2022 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.