5

I run a mysql server that hosts around 50Gb of data (primarily for around 250 websites), and am wondering what my options are for setting up a redundant MySQL cluster? The primary purpose would be that I could take one server down for maintenance or reboot, without affecting database availablity - and secondarily that there would be some kind of hot failover in case of problems with the live server.

My understanding is that mysql-cluster requires that the DBs be entirely contained in memory, and with so much data, that is not a practical option.

Brent
  • 22,857
  • 19
  • 70
  • 102

4 Answers4

3

Check out mmm for automated failover. Be sure to set up the two servers as masters, so you have bidirectional replication. Also if you are using autoincrement, make sure you set it up so that you don't have clashes for entries (see this article for details).

Finally, use Maatkit to ensure that there are no inconsistencies between the servers.

Tim
  • 1,879
  • 3
  • 18
  • 16
3

What you need is replication. While a lot of people use MySQL replication, I've dealt with it enough (dozens of high capacity production MySQL instances) to know it's not a winning option. It's pretty fragile, and will fail at inconvenient times. Now, I'm leaning towards using a block replication solution such as DRBD to make the MySQL stores consistent.

As far as the failover goes, again MySQL replication doesn't deal with this particularly well. While failing over from master to slave is a fairly automatable operation, dealing with the aftermath (getting replication running again the other way) is always a manual process, requiring poking and prodding to ensure that everything works right. Whichever replication method you choose, I use heartbeat to detect whether everything's working right and when the currently active server falls over, making sure that an orderly takeover of resources occurs.

womble
  • 96,255
  • 29
  • 175
  • 230
2

We have been using DRBD / Heartbeat for awhile now, and it's pretty good, but not ideal for a virtual-server situation (which we have), because it should have multiple connections between the servers, and in a virtual environment, all the connections (serial, ethernet, etc.) are essentially carried over the same wire - so when one times out, they all time out. (I realize I could put a real serial cable on there, but then I would no longer be able to migrate my VMs between hosts - an ability which I don't want to give up)

In this situation I occasionally get a case where neither server can see the other, and they both become "Primary" at the same time (a situation called split-brain, which is a headache to recover from)

But it's definitely a better solution than binlog replication which we were using before that - and from the comments so far it sounds like a more robust solution than mysql-cluster as well.

Brent
  • 22,857
  • 19
  • 70
  • 102
0

As of mysql 5.1, mysql cluster data (although not its indices) can be stored on disk: What's new in MySQL 5.1

However I would agree with womble that given many of the limitations of mysql-cluster/ndb and the frailty of replication, I would hesitate it to consider either to a general purpose solution. For a single database with a well understood usage model and a real DBA that works with the developers? possibly. But not for a general purpose database server cluster that supports many different applications.

Instead I would want to use either the DRBD approach or just throw money at the problem and use a SAN. No solution that I am aware of is perfect.

dotplus
  • 1,230
  • 7
  • 12
  • Tell me what you mean by "throw money at the problem and use a SAN" - as this is already running on a SAN – Brent May 05 '09 at 13:42