We are looking into migrating our infrastructure including Aurora Clusters and RDS instances from AWS to on-prem,
Before we went from on-prem to AWS we had percona clusters running, with simple primary-secondary setup, so it makes total sense to look this way again.
One of the options would be Perconas Server or XtraDB, and im kinda split on what we should continue with.
Data consistency is of most importance, and I do not really see us from benefitting from balancing writes between nodes using HAProxy or SQLProxy
The Current DC is dual site, and we would need the ability to change between DCs seamless, and we would also need to be able to switch between primary nodes on each DB without interruption.
Using multi-master-replication, is not an must-have, but nice-to-have. But I see an issue with us only having 2 DCs and not 3, with the potential for causing split-brain. (Can be somehow worked-around with an abritor)
As said we “only” have 2DCs and im a bit worried about the latency that XtraDB provides using multi-master-replication, especially for the 2nd DC if we have issues with the uplink. And that its an major tech-stack change.
But with XtraDB it would make sense to have an VIP in each DB, which makes failover/migration for our application between DC easier and more smooth, since we can “hardcode” the VIP for our application depending on which DC it runs at.
Secondly im currenlty looking at the best options for implemententing the HA. I know it depends on which replication-method we are using.
Our application currently only support the use of one IP address. Rewriting the application will not be possible within time.
What would best practices be for assigning/aligning an VIP? We would need to kinda, maybe at least once per month to switch the primary for maintenance wihtout any interruptions at all.
Keepalived (Server/XtraDB) with VIP would be an option, but im not sure if this option is outdated? I know this options works though with “plain” galera.
Pacemaker (Server)
Using HAProxy or SQLProxy (XtraDB)? It lean towards HAProxy, since we already have this for other purposes, and we currently do not need the layer7 options SQLProxy provides.
Creating VIPs on an network level, e.g. firewall, and somehow add the primary SQL server(s) here? This would likely only work if we use multi-master-replication, where all servers are master. Im not sure if we would need e.g HAProxy here as well ?. firewawall(vip)->haproxies->MySQL-nodes(s)
This topic leans more toward architecture and design considerations. For in-depth guidance on such projects or proof of concepts, we recommend exploring our consulting services, where our experts can provide tailored advice and support.
Discussions of this nature can be complex and are often difficult to initiate and conclude effectively within a community forum platform.
Nobody does this. We would never recommend it. This is not the default behavior of HAProxy, nor of ProxySQL.
Configure two independent 3-node PXC clusters. Have them replicate async to each other, forming a Source/Source topology. Use DNS and your app to pick whichever is the current “active side.”
You would use ProxySQL for that to change the current writer.
This is no different than using traditional async replication between the two DCs. For clarification, you would not configure 1 PXC with 6 nodes between two DCs. That could be laggy.
DNS. Don’t use hardcoded IPs. Use something that can change without needing to notify the application. Additionally, this DNS entry would resolve to ProxySQL, which handles the actual HA. If current writer goes away, ProxySQL switches writes to the next member. Application has no knowledge this occurs.
Tell me you are an old sysadmin without telling me you are an old sysadmin These are outdated methods that nobody really uses anymore.
PXC+ProxySQL is the ultimate HA solution for MySQL.
Thanks for the very detailed and clear answer. This is great. This is just what im looking for.
Unfortunately with + ~15 standalone DBs, it increases the number of DB nodes extremely. It means we would have an minimum of 90 database instances across 2 sites. Im not sure this is doable at all (Storage and hypervisor wise).
But for the ProxySQL part how should this be done ? Im am all new to ProxySQL.
I read that the recommended way is to have proxysql running on each application server, and im not sure im a big fan of this.
We have the servers kinda tight and hardened, and it would require is to again to further changes to the application, which we at this time cannot do.
Im not sure this would work well with docker containers and kubernetes ?
Would it be possible to do something like, where we have an DNS entry that points to the VIP?
ProxySQL(VIP) → PCX ?
Yes, this is a common solution. 2 or more ProxySQL behind a VIP. ProxySQL cluster is nothing fancy; it’s just a way of sharing configuration between multiple ProxySQLs; that’s it.
You mention async replication between the 2 sites?
I found another post, where you mention kinda a similar setup. But in our case this should not be for DR, but we need it as an secondary working site, so the solution does not fully apply.
Ref: Cluster achitecture - #4 by bthnklc
How should det async setup be done between the 2 clusters? Delegate 2 nodes as “primary” for this purpose, and make them multimaster sync ? Im aware that if we do maintenance on this node then, or if an site dies, the replication will be interrupted and/or fall behind.
We would like to have both DCs available at the same time.
E.g. Applications on DC1 should use MySQL on DC1, but with the option to failover to the DB on DC2 in case of local clusters issues and/or major maintenance work.. And like verse for DC2.
Is this recommended ?
I see two potential issues.
1 - The replication between the 2 DCs, e.g. making sure that the replication is in sync before the switch.
2 - The latency betweent the 2 DCs. Which is potentially not an “MySQL” issue, but more an networking layer.
No, this is not ever recommended (one exception below). You can find many posts here, and maybe even a blog or two on our site where active/active clusters are desired and all the reasons not to.
Applications in DC1 can use local MySQL in DC1 for READS, but any writes should go to DC2, the current active cluster. Apps in DC2 should use MySQL DC2 for READS, and WRITES.
Async replication takes the writes from DC2 and replicates to DC1.
Exception: If the apps in DC1 use only the tables in database1, and the apps in DC2 use only the tables in database2, then you can do active/active. But if the apps in DC1 and DC2 both use the same tables, then no, you will never find a recommendation to do active/active cross-DC clusters.
These are outdated methods that nobody really uses anymore.