We are looking into ways of implementing a “Failover Cluster” with three servers spread around two DataCenters (S1 and S2 on the primary DataCenter and S3 on the secondary).
Currently we have a master master between S1 and S2 (with log-slave-updates = OFF) and S3 replicating from both of them. We know it isn’t a ideal setup and it has some big flaws when it comes to promote S3 as a Master.
We will soon be conducting Business Continuity test switching the load from one server to the other and checking that all our services continue working. For this we would love to have a master-master-master kind of replication. (Our services are more than just a website and a database, so maybe we switch the load to Sever 3 and then because of other issues decide to fall back to Sever 1 while we work the issues).
We are toying the idea to set up a topology using GTID, but we aren’t sure we understand GTIDs fully and we found a few things we couldn’t find answers for.
Are we doing something that makes sense (Using MariaDB w/ GTIDs for a “failover cluster”)? Or we should switch to something like Galera Cluster? (We were hesitant to do such a big change) (Question 1)
The setup we are thinking is;
S1: Server_id:1, Domain_id:1
S2: Server_id:2, Domain_id:2
S3: Server_id:3, Domain_id:3
Server 1 is a slave from 2 and 3
Server 2 is a slave from 1 and 3
Server 3 is a slave from 1 and 2
gtid_ignore_duplicates = ON (We figured as this is a must, right?) (Question 2))
gtid_strict_mode = ? (Isn’t this redundant with ignore_duplicates? What should we set this to?) (Question 3))
log_slave_updates = ON (We do need this so all the domain streams are recorded in every server right? (Question 4))
If we were to add a fourth server just as a slave, say for backup purposes, if we just “connect it” to replicate from Server 3, will it also get all the transactions happening in Server 1 and 2 right? We aren’t sure if it will just replicate S3’s Domain_id (3) and skip the other two (1,2) (Question 5)
Thanks in advance