MySQL Clustering

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.

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.

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.