Need an advice related to high availability

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello,

Guys I need an advice. I am not an SQL geek but have some experience with MySQL. So Currently I am stuck with choosing the best solution. So I need an advice from proffesionals who really had hands on experience with their solution.
We have the following situation.
There is a PHP web application which uses and keep a lot of data in several mysql servers.
8 Mysql servers are keeping application settings and 1 MySql server keeps database with user accounts and data with information where is user app data stored (including which of 8 sql servers have current users app information).
So when new user registered he/she get 2 information schemas - 1 on the accounting mysql server and the second one on the app mysql sever.
Previously these SQL servers were hosted on Google Cloud SQL - it had limitation for schemas (not more than 10000 schemas per one server) so that’s why we had such weird quantity of servers.
Besides we had a lot of problems with dead-locks with Google cloud solution so we decided to migrate to simple VM hosted mysql servers very quick.
Everything is working fine now.
Each MySql 5.7 server now has master-(read-only)slave configuration.
Now I am planning to merge smoothly all these application MySql servers to one Percona 8.0 MySql server.
This server must have an High-Availabily (this is number 1 priority) with possibility to be scaled further on other datacenters.
Now I see 3 solutions

  1. Master - Master replication with HA Proxy - second master is not planned for write operations - it’s planned just for automated switching in case of 1st master failure. Further scaling will happen with read-only replicas
  2. Percona XtraDB Cluster implementation
  3. Galera Cluster implementaton

Which solution is the best taking into account a high reliability as the first priority?

2

Hello,

This is broad topic, a project, for which I am unable to provide fully comprehensive advice within this forum frames.
I’ll give you some hints instead:
[LIST]
[]MySQL 8.0 and solutions based on it, can deal with lots of tables/databases much better thanks to the new data dictionary - check out this experiment: [url]https://www.percona.com/blog/2018/10/22/one-billion-tables-in-mysql-8-0-with-zfs/[/url]
[]Galera replication solution for MySQL, so the one used in Percona XtraDB Cluster, MariaDB Galera Cluster, etc, is not designed to scale out writes - i.e. it does not offer you sharding out of the box, and each write has to reach all nodes in any case; it surely can be used for scaling reads though,
[]Master - Master asynchronous replication does not provide any conflict resolution mechanism and it is easy to end up with data consistency issues
[]asynchronous replication doesn’t have the commit response time overhead related to the network latency, while Galera-based solutions have it
[]asynchronous replication does not have anything to protect from reading stale data from the slaves, while Galera has Flow Control and even sync reads mechanisms for that
[]there is another solution in the ecosystem - MySQL Group Replication (part of InnoDB Cluster), which is very similar to Galera here
[/LIST]
I’d like to share these links for reading as well:
[url]https://www.percona.com/blog/2016/06/07/choosing-mysql-high-availability-solutions/[/url]
[url]https://www.percona.com/blog/2009/10/16/finding-your-mysql-high-availability-solution-–-the-questions/[/url]
[url]https://www.percona.com/blog/2014/11/17/typical-misconceptions-on-galera-for-mysql/[/url]
[url]https://www.percona.com/blog/2013/05/14/is-synchronous-replication-right-for-your-app/[/url]

3

Thanks, a lot.