MySQL Solution to Sync 3 servers data

MySQLmysql-clusterperconareplication

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:

what is the difference of these? which one is the best for my situation?

These are my needs and considerations:

  1. I have 3 Geographically distributed servers which I want to sync them.

  2. 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.

  3. I want something hidden to the application layer, I want to INSERT/SELECT and etc… like before, without changing any line of my codes.

  4. 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!

Best Answer

You are asking some good questions here but you are at an important crossroads and undoing an architectural mistake after implementing it is extremely difficult and costly.

The first question you should ask yourself is do you really need a multi-master topology? Most organizations and applications function well with MySQL asynchronous replication with a failover script in the event failover is necessary.

If you decide a multi-master topology is the right choice, you need to confirm that your application and requirements are a fit. I see a red flag already with this.

I have 3 Geographically distributed servers which I want to sync them.

WAN replication with multi-master solutions like Group Replication (MySQL) or Galera (which is used by Percona XtraDB Cluster and MariaDB Cluster) have similar limitations. There are workarounds if your application is tolerant of additional latency and your application also does not process large, long-running write transactions. Which brings us to our next point...

I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.

This is not always possible with multi-master database solutions. The way your application interacts with the database is critical to the success of the database layer. If your application code is written poorly, the database will behave poorly.

I highly suggest you reach out to an expert to help you design an ideal solution for your situation.

FULL DISCLOSURE: I work for Percona, but other vendors could assist here as well.

Good luck!

Related Question