[Maria-discuss] How to replicate TO a HA pair of MariaDB servers?
Hi, I've got a bit of a different question regarding MariaDB replication. But first, let me say that I'm familiar with both master-master and master-slave replication. 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. I've tried to think of how I could do this using what I know. Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)? The idea is that the replication would fail from HA-1 when the VIP is on HA-2. In this case the single-host masters would only allow replication connections from the VIP. Or the connection out from the slave to the master would fail to bind to the VIP (and thus fail to connect to the master) if the VIP was not on a local interface. 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]? Are there other ways to solve this problems? Cheers.
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.
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.
On Tuesday 15 September 2020 at 12:39:16, martin doc wrote:
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.
Firewall rules? Antony. -- What do you get when you cross a joke with a rhetorical question? Please reply to the list; please *don't* CC me.
On Tuesday 15 September 2020, Anthony Stone wrote:
On Tuesday 15 September 2020 at 12:39:16, martin doc wrote:
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.
Firewall rules?
I was thinking about that ... it would requiring sync'ing iptables rules with VIP movement. Then I could selectively NAT to force outgoing connections to appear as if they come from the VIP. Is it possible to run a script when a VIP moves as some kind of event based action?
On Tuesday 15 September 2020 at 13:07:22, martin doc wrote:
On Tuesday 15 September 2020, Antony Stone wrote:
Firewall rules?
I was thinking about that ... it would requiring sync'ing iptables rules with VIP movement.
Why? If the connection comes from the VIP, it's constant as far as the single servers are concerned.
Then I could selectively NAT to force outgoing connections to appear as if they come from the VIP.
Is it possible to run a script when a VIP moves as some kind of event based action?
That depends on what HA system you're using to maintain the VIP, but the ones I've used (heartbeat, corosync, vrrpd, keepalived) all support running scripts as part of the failover process. Antony. -- 90% of networking problems are routing problems. 9 of the remaining 10% are routing problems in the other direction. The remaining 1% might be something else, but check the routing anyway. Please reply to the list; please *don't* CC me.
Martin, howdy.
Hi,
I've got a bit of a different question regarding MariaDB replication.
But first, let me say that I'm familiar with both master-master and master-slave replication.
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.
So these would be two slaves that alternate on actual connections with 'a number of single host instances' \footnote{ If that's correct, naturally I am guessing the two slave also replicate to each other: Host -> Slave_HA-[12] -> Slave_HA-[21] }
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.
I've tried to think of how I could do this using what I know.
Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)?
MASTER_BIND ?
The idea is that the replication would fail from HA-1 when the VIP is on HA-2. In this case the single-host masters would only allow replication connections from the VIP. Or the connection out from the slave to the master would fail to bind to the VIP (and thus fail to connect to the master) if the VIP was not on a local interface.
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]?
Yes it is above.
Are there other ways to solve this problems?
I hope this reply helps.
Cheers.
Likewise, /ndrei
On Wednesday 16 September 2020, andrei elkin wrote:
Martin, howdy.
Hi,
I've got a bit of a different question regarding MariaDB replication.
But first, let me say that I'm familiar with both master-master and master-slave replication.
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.
So these would be two slaves that alternate on actual connections with 'a number of single host instances' \footnote{ If that's correct, naturally I am guessing the two slave also replicate to each other:
Host -> Slave_HA-[12] -> Slave_HA-[21]
Yes.
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.
I've tried to think of how I could do this using what I know.
Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)?
MASTER_BIND ?
MASTER_BIND looks right except: - it isn't supported on mariadb yet - https://jira.mariadb.org/browse/MDEV-19248 - ? - it binds to an interface name, not an IP address. VIPs often share a network interface, so being able to specify the IP# is important.
Are there other ways to solve this problems?
I hope this reply helps.
Yes, it does, thanks. Every idea/suggestion helps. Another alternative I started looking at was if there was an equivalent of pg_chameleon (https://pgchameleon.org/) which allows for MySQL -> postgres replication. The theory here being that if that kind of replication is supported (replication is independent of what's configured in my.cnf and doesn't use 'CHANGE MASTER') then that would allow using a VIP too. Here the replication is initiated by the master and not the slave? Cheers.
participants (3)
-
andrei.elkin＠pp.inet.fi -
Antony Stone -
martin doc