Okay, so I have two new loaded (each has dual 2.3Ghz G5s, 8GB RAM, hardware RAID-5 w/ 1.2TB drive space, dual gigabit ethernet, etc.) Xserves thanks to those that donated, and I’ve been mucking around with them for a few days now.

One is going to be primarily a web server, and the other will be primarily a MySQL database server. Mac OS X Server makes IP fail-over really easy, so I setup rsync to keep a copy of the web site content on the MySQL server. If one server goes down (or maybe taken down for an upgrade), the other acquires it’s IP address and can continue to serve the requests. The tricky part is the MySQL database and keeping it in sync between the two machines in realtime.

The “ideal” option would be MySQL Cluster, but it requires your databases to be 100% memory resident, and that just didn’t appeal to me (too much memory and scary if someone decided to pull the plug on all the servers). Although I do know that MySQL is working on a disk based option for MySQL Cluster as well as asynchronous replication (that may turn out to be the same thing).

So instead what I did was setup both servers to be masters and both servers to be slaves to each other. This was in fact VERY easy. Add this to your /etc/my.cnf file on the servers (be sure to create a replication slave user named replicate):

So now I can send all SQL queries to the primary database server’s IP address. If that machine is down, the other server acquires it’s IP address. Once the server comes back online, it will automatically get back in sync.

The thing I didn’t like is a double master setup does not always execute the SQL statement in the same order, so there is a (slight) chance of the databases getting out of sync when the server comes back online and there is a lot of catching up to do. So what I did was setup a simple script to check every 5 seconds to see if the servers are in sync. Once they are in sync again, then it will relinquish the public IP address back to the rightful server (the MySQL servers talk on the private 10.0.0.x network which does not change).

My setup is for redundancy/fault tolerance, not load balancing. Although you could send SELECT statements to the slave db server without any problems (since there is nothing to replicate from that).

This will work for now, until MySQL finishes their native async replication. 🙂