I have 3 servers which there is a load-balancer in front of them and sends the requests to these servers randomly.
The script is the same in all of these 3servers, but I need to sync the DB in all of them, or replicate the DB or whatever you suggest, I don't know it's name exactly! There are a lot of things I've heard:
- MySQL Replication (master-master)
- MySQL Replication (master-slave)
- MySQL Cluster
- MySQL Replication (Group replication)
- Percona Server
- MySQL Galera
- Any other scripts like this PHP Package or this
what is the difference of these? which one is the best for my situation?
These are my needs and considerations:
I have 3 Geographically distributed servers which I want to sync them.
I want to use InnoDB (Not preferred to use NDB Cluster engine of MySQL Cluster), also I have some in-memory tables, but not that much important to use memory tables, I can switch to InnoDB.
I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.
I want something master/master replication or sync or whatever for when each of my master servers goes down no failure happens, So it must have automatic node failure detection in it.
Would you suggest me a solution, for example percona server or anything else? I think if I choose percona, I must go with Percona XtraDB Cluster, not normal Percona Server!