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
- 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
- Percona XtraDB Cluster implementation
- Galera Cluster implementaton
Which solution is the best taking into account a high reliability as the first priority?