Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some clarification on MySQL-Cluster (NDB) setup

I want to achieve full HA but I'm low on server budget and can't afford to rent a dozen of servers for simple solution that I'm working on.

What's the minimum amount of hosts to achieve full HA in case of one node failure?

I mean situation that cluster should continue to work with no errors when any single physical servers fails. Failed server can be any of MySQL Cluster components (management, sql or data)

I don't need extreme performance, just fully automated server failure handling on small number of servers

From the MySQL Cluster documentation I know that I need at least two full copies of data (replicas). Does one replica need to be on more than one host?

Can HA be achieved on 4 servers? 2 management nodes (kind of small VPS) + 2 physical servers each running SQL and data node?

I'm aware of MySQL master-slave, but it doesn't provide automated failover and requires application rewrite

share|improve this question

migrated from serverfault.com Sep 20 '12 at 23:50

This question came from our site for system and network administrators.

    
It all depends on what type(s) of failure modes you're trying to protect against. –  EEAA Aug 25 '12 at 12:48
    
I'm asking about dealing with physical failures (broken RAM, PSU, motherboard, etc) –  Matthias Aug 25 '12 at 13:10
1  
Also think about maintenance, if you have to take one node down for updates etc. you have only one node left online and a spf (single point of failure). I would put at least 3 nodes in the cluster. –  Izac Aug 25 '12 at 13:25
    
@lzac thats actually very true, you need one spare at any time to switch over –  Andrew Smith Aug 25 '12 at 13:30
    
I just want to get 'can-sleep-at-night' or 'can-go-for-holidays' level of HA. Probability of multiple server failure at the same time in such small setup is low and I accept it. It's also acceptable to have temporary SPoF during scheduled downtime of one host –  Matthias Aug 25 '12 at 13:34

2 Answers 2

You really need to define what level of "HA" you are looking for quantitatively -- one man's "can-sleep-at-night' is another man's "this-thing-is-a-house-of-cards".


The minimum number of systems is two (2) -- An active and a standby replica (with something like heartbeat or custom-grown scripts to handle the failover).

With a MySQL cluster this means at least two SQL nodes and two Data nodes (to continue serving requests in the event of a failure of any one node). (If you also need redunancy on the management server's functions you would need two of those as well).

The key part here is testing the failover in a development environment -- which means you need at least two more machines (or a virtual machine). You also want to test upgrade and maintenance processes to ensure they won't trigger unintended consequences (Ideally you should do nothing to production that hasn't been tested and proven in Development).

If you fail to properly test you may trigger failover, which means you incur the procedural (and possibly business) cost of a failover -- typically having to rebuild the former active server to be a new standby server.

This protects against hardware failures (power supply, NIC, disk, switch (if they're on separate switches).

Note that this doesn't just apply to your DB servers -- You need two of *EVERYTHING: Web servers, DBI servers, Firewalls, DNS servers...
Redundancy of one component is meaningless if you still have a bunch of single-points-of-failure in your stack.


The next level of protection is network failures ("What if my ISP goes down?") - this requires replicating your whole redundant environment above to a remote datacenter.

What's important here is that you diversify network connections, power, etc -- You don't want your standby datacenter across the street where it's fed by the same power and fiber as your main facility.
A company I consulted for had a requirement that any remote facility used for DR be "at least 15 degrees of longitude away" (i.e. "In the next time zone"). A common practice in the US is East-Coast/West-Coast, or NY/Chicago LA/Texas.


The next level above that is truly distributed resources (think Google) which requires a database system that supports replication and sharding (think MongoDB).
If implemented properly there's almost no chance of a true "outage", though service may occasionally be degraded and recovery can take a while.

share|improve this answer
    
This question is only about HA of MySQL-Cluster. I'm aware of HA issues with all other elements of my infrastructure but the database is hardest one. So again - my question is only about minimum number of MySQL-Cluster servers, not web servers, ISP links or anything other –  Matthias Aug 25 '12 at 15:58
    
@Matthias There is no relevant difference. N+1 is always the minimum number for redundancy. The minimum value of N for any required component in a system is always 1. Therefore 2 is always the minimum number of servers required for redundancy. That minimum number grows as your definition of "redundancy" expands to encompass more than just "some idiot unplugged one of the servers", per my answer. –  voretaq7 Aug 25 '12 at 16:01
    
Yes you are right, but still my question is strictly about MySQL-Cluster specifics. I still don't know if I need 2 managers + 2 sql nodes + 2 data nodes = total of 6 hosts. More or less? Two replicas are OK or I need more? Thats 6 servers to host one database and still I'm not shure is it OK –  Matthias Aug 25 '12 at 16:29
    
@Matthias See my edit in the answer - You need a minimum of two of every component for which you want redundancy (if you could live without the management server's functionality for a few days there's no need to have a redundant one, but if you want to keep serving database requests you need at least (2) data nodes and (2) SQL "front-end" nodes). Note that there's no rule that says the standbys have to be separate physical systems: You could theoretically have a machine that can fail into either a data node or a sql front-end node. I don't know if MySQL officially supports that though. –  voretaq7 Aug 25 '12 at 16:45
1  
@Sandman4 I am familiar with MySQL-Cluster. The "generic (and well known)" advice is the answer to the question -- In fact it's the architecture described in the documentation. –  voretaq7 Aug 26 '12 at 1:39

Answer to your question: 2.

All these data, sql and management nodes can run on a single machine. With two such 3-node machines you have full redundancy.

Keep in mind that NDB is in-memory. Failure of all nodes means not "service temporarily unavailable", but "all data is gone forever". I don't know how many nodes I need in such a setup to sleep at night. At least add some replication to disk database.

My personal conclusion was to not use MySQL cluster at all. There's a plenty of clustered databases today.

share|improve this answer
    
You are wrong, NDB is memory based but has also built-in HDD persistance and data continues to live even after restart of all nodes –  Matthias Aug 27 '12 at 16:41
    
@Matthias Carrier-grade, or community edition ? –  Sandman4 Aug 27 '12 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.