On Tuesday 15 September 2020, Anthony Stone wrote:
On Tuesday 15 September 2020 at 11:45:05, martin doc wrote:
What I'm after advice on is how to replicate tables from a number of single host instances of MariaDB to a single HA pair of MariaDB servers (HA-1/HA-2) that have a VIP that moves between them.
One way of looking at this might be to say that the single host instances of MariaDB are satellite servers that I need to pull table data back into a primary pair. Kind of like a backup.
Do you need this to be immediate replication (data written to a single host must be replicated immedately to the HA pair) or is a delay of some minutes acceptable?
Good question. A delay of 1 minute or two would be acceptable. More than 5 minutes, not so much.
That would be your other problem - you say you have a number of single host instances which you want to replicate from, but you can't point a slave at more than one master in standard M-M or M-S replication.
I've also used multi-master configurations with GTID before but there's still the problem of how to restrict the connection accepted on the slave to only be the host with the VIP.
a) runs a query on the single hosts to extract updates since the last time the query was run, and send this over to the HA pair (eg: rsync and then an equivalent script to write into the HA pair, or an SSH tunnel streaming the query output into a MariaDB client instance on the HA pair), or
Ok, sounds like I need to write some python to run as a background service on each of the "satellite" servers that does SELECT/INSERT manually.