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.