maria-discuss team mailing list archive

[Antony Stone <Antony.Stone@xxxxxxxxxxxxxxxxxxxxxx>]

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.