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?
Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)?
Assuming your HA pair is doing "standard" master-master replication (ie: not using Galera), then no, you can't point either of them at an external master to replicate from, because they are already pointing at each other, and one slave can only point at one master. 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.
The idea is that the replication would fail from HA-1 when the VIP is on HA-2.
I can't help feeling you've used to word "from" where you mean "to" in that sentence.
Of course I want both servers (HA-1/HA-2) in the redundant pool to be able to serve queries, so I'm not too keen on setting the bind address in [mysqld] to be the VIP and using net.ipv4.ip_nonlocal_bind - but I could be open to that.
Is it possible to specify a bind address for replication that is separate to that used in [msyqld]?
I believe not.
Are there other ways to solve this problems?
I can't think of how to do it if you need immediate replication of data. If a delay of a few minutes between the single hosts and the HA pair is acceptable, I would try setting up a cron job which either (depending on the sort of updates you're doing on the single hosts): 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 b) runs a mysqldump on the single hosts and similarly feeds the result into the HA pair. You *might* be able to use the federated storage engine to be able to synchronise tables between a single host and the HA pair, but in my limited experience of trying to use this, it's very slow and inefficient. That's what I can think of, anyway. Antony. -- Don't procrastinate - put it off until tomorrow. Please reply to the list; please *don't* CC me.