Master-Master or Group Replication to keep 2 databases on 2 servers in sync?
Best way? Preferred way? Discuss.
Whats the goal ?
Hot failovers ?
Oui
I’ve never done it with mysql (and its been a long time since I had to configure this with sybase etc)
But I’d follow their recommendations about this
https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-switch.html
I’m sure the DBA forum on stackexchange would be a good place to find answers
Like
Ok, well, it SEEMS like Group Replication is the way to go, so I gave that a try.
Following these instructions (even though they are a little old)
It worked fine up until I tried to start GROUP_REPLICATION on the 2nd node.
(“Startup remaining nodes” section of the document)
I get the following error:
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
The error log shows:
2021-02-27T19:05:45.079426Z 16 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:10.874606Z 16 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-02-27T19:06:10.875454Z 17 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2021-02-27T19:06:10.878134Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.878182Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.881114Z 18 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:13.300659Z 0 [ERROR] [MY-011516] [Repl] Plugin group_replication reported: 'There is already a member with server_uuid 6227f63c-dd97-11ea-8989-86fbcb300464. The member will now exit the group.'
Instructions say that the UUID is supposed to be the same for all nodes.
I’ve checked and double-checked my settings, I’ve opened the firewall ports, which wasn’t detailed in the document, googled that specific error but no joy.
No errors on node #1.
node #1 log:
2021-02-27T18:11:32.346858Z 9 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=do-rp-web2-relay-bin' to avoid this problem.
2021-02-27T18:11:32.353977Z 9 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T18:18:07.376693Z 9 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-02-27T18:18:07.377921Z 10 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2021-02-27T18:18:07.381930Z 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T18:18:07.381977Z 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T18:18:07.396058Z 11 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T18:18:09.562078Z 0 [System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Incremental recovery from a group donor'
2021-02-27T18:18:09.562459Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:1.'
2021-02-27T18:18:09.564875Z 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
2021-02-27T18:18:09.565577Z 19 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2021-02-27T18:22:19.820960Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:2.'
2021-02-27T18:22:21.517730Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: '
2021-02-27T18:22:21.517815Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:3.'
2021-02-27T18:31:44.346482Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:4.'
2021-02-27T18:31:46.255619Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: '
2021-02-27T18:31:46.255789Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:5.'
2021-02-27T18:51:02.396452Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:6.'
2021-02-27T18:51:03.969466Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: '
2021-02-27T18:51:03.969542Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:7.'
2021-02-27T19:06:13.300291Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:8.'
2021-02-27T19:06:15.162378Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: '
2021-02-27T19:06:15.162459Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 10.120.0.3:3306 on view 16144498885610770:9.'
It looks like it sees the attempt to add the group member, but it doesn’t actually happen. (tried several times as you can see)
need more information about what you are trying to do to be able to answer your question(s).
so until then… “it depends…”
Thanks, @Scott. Not having much luck here.
I’m trying to set up group replication as per this document:
Let me know what other information you need.
Ok, turns out it’s a stupid newbie error.
One server was cloned from the other, so they both had the same server_uuid. Deleting auto.cnf on one server and restarting it fixed that problem.
However, it’s not all smooth sailing just yet. Seems that if there are changes (like adding a database) to the master database and then you try to start group_replication on the 2nd server, it fails complaining that the database doesn’t exist on the 2nd server.
I thought that was the whole point, that changes on one server would propagate to the other when they reconnected?
N/M. Messed around with it until I got it to work.
This is pretty advanced stuff. Not easy! 
Ok, this really doesn’t work very well. I was hoping that if a server went offline, when it went back online it would automatically update itself. Well, it doesn’t. You have to manually backup the running server and import it to the 2nd server and then start the replication system again. It’s pretty terrible.
There’s TONs of info about master->slave setups where only the master has write access and the slaves are read-only, but very little about setting up completely redundant servers where both databases mirror each other and if one stops the other keeps going. I guess this is simply not something that’s done very much, or because it’s so advanced, there’s not a lot written about it. The instructions I have seen are very complicated indeed.
So, in hopes of simplifying things, I’m thinking about just a cron job that backs up one database and then updates the other. I guess using mysqldump and mysqlimport unless someone has a better suggestion.
why not have a daemon on each server that monitors the upstate of the other one… if one goes off line, have it tagged, and when it is seen to come back online, its companion does what ever is required to re-synch them…
Thanks, @DaveS .
The down detection is kinda the easy part.
Keeping the databases in sync in semi real time is the part I’m trying to figure out atm.