0

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!

Improve this question
2
  • 2
    This is not really a programming question, since you do not want to write code, you want to use existing features of MySQL or some 3rd party products. You may get more help at the DBA stackexchange site. However, I do not think they can tell you which solution to pick. That's something you have to decide.
    – Shadow
    Sep 6, 2018 at 17:38
  • 1
    Solution: Hire an experienced DBA. If you get this wrong you're going to destroy your company. Don't take chances.
    – tadman
    Sep 6, 2018 at 18:59

2 Answers 2

Reset to default
3

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!

Improve this answer
4
  • Thanks for your answer, Yes I know WAN replication adds some additional latency to my application, but my application is usually reading from the DB, not writing, write to read ratio is something about 1:100 In general, I need database clustering for HA and being always on, not for performance. (For performance I may use small memory tables in each node, which I found out that Percona XtraDB Cluster is only replicating InnoDB tables, so using memory tables can be a good trick here. Although I must do something for being data consistency in my application layer.)
    – behnamy01
    Sep 8, 2018 at 10:15
  • 2- And about being hidden to the application layer, I didn't understand why you do not agree with this part, My load-balancer is using round-robin method (so it doesn't check the heart-beat of my servers and my Database solution must have automatic node failure detection), and as I've mentioned before I have the same PHP script in all of these 3servers, so I can read from DB in each node like other nodes & with the help the of PXC which replicates data in other nodes for me I can make write queries too. So I don't need to do anything more and my application codes can be the same as before!
    – behnamy01
    Sep 8, 2018 at 10:17
  • 1
    Note with galera there is a finite delay between being committed and the data appearing visible on the other node. Keep this in mind for the web application that updates a value and in the next page/sql query retrieves the same value, potentially from a node that isn't updated yet. I'd try to maintain some sort of database affinity to the application though the load balancer.
    – danblack
    Sep 8, 2018 at 22:58
  • 1
    @danblack - re the 'finite delay' -- see Critical reads
    – Rick James
    Oct 3, 2018 at 22:33
0

Master-Slave and Master-Master are older technology; they have limitations, especially when it comes to recovering from a failed Master. These are not recommended since there are better ways. For 3 datacenters: S <- M -> S or M <-> M -> S, where each Master/Slave is in a different data center.

MySQL Cluster, aka NDB Cluster, is a specialized animal. It is not for most people.

Galera Cluster, aka Percona Cluster (PXC) and MariaDB Cluster, is reasonably mature and available.

Group Replication, aka InnoDB Cluster, is the newest offering. It is available with MySQL, not MariaDB, since Oracle invented it.

I am not ready to say which of the last two (Galera vs Group Replication) is best. Both have these features:

  • Virtually synchronous. With the exception of "Critical Reads", it is synchronous.
  • It is best to have at least 3 nodes, one (or more) in each of 3 physical locations. That way, if one datacenter goes dark, the cluster stays alive.
  • There are various proxies / load balancers that provide for distributing queries among the nodes. It is a 'necessary' add-on. (Round-robin is usually the only algorithm to consider.)
  • You use HA in a LAN. A WAN needs some special tuning due to added latencies.
  • Only InnoDB is fully supported. MyISAM and MEMORY may work in special cases. The Query cache is not available. You mentioned the use of MEMORY tables for performance -- I recommend you not do so until you have exhausted other techniques.

Here are some tips on dealing with Galera. (Some of them also apply to other replication technologies.)

I would go with PXC or MariaDB Cluster.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.