From owner-freebsd-questions@FreeBSD.ORG Thu Jan 21 01:06:20 2010 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 6461E106568F for ; Thu, 21 Jan 2010 01:06:20 +0000 (UTC) (envelope-from steve@ibctech.ca) Received: from smtp.ibctech.ca (v6.ibctech.ca [IPv6:2607:f118::b6]) by mx1.freebsd.org (Postfix) with SMTP id C9B188FC1B for ; Thu, 21 Jan 2010 01:06:19 +0000 (UTC) Received: (qmail 65163 invoked by uid 89); 21 Jan 2010 01:06:58 -0000 Received: from unknown (HELO ?192.168.1.114?) (steve@ibctech.ca@::ffff:208.70.104.100) by ::ffff:208.70.104.210 with ESMTPA; 21 Jan 2010 01:06:58 -0000 Message-ID: <4B57A887.2030801@ibctech.ca> Date: Wed, 20 Jan 2010 20:06:15 -0500 From: Steve Bertrand User-Agent: Thunderbird 2.0.0.17 (Windows/20080914) MIME-Version: 1.0 To: "freebsd-questions@freebsd.org Questions -" X-Enigmail-Version: 0.96.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Subject: High availability SQL server setup X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 21 Jan 2010 01:06:20 -0000 Hi all, For some time, I've been considering consolidating all/most of our SQL databases (all MySQL) onto a single dedicated cluster setup. I'm looking for feedback on the best way to do this. All of the options I've considered so far have both their drawbacks and benefits. From what I can tell, there's no one single way to be able to have everything that I want. Off the bat, I haven't found a way to create a cluster that can have more than one host in the cluster writable. My objective would be to start with two very high end boxes. One would sit in my primary location, the other a few blocks away over a gi fibre link. I would want the remote box to pick up immediately if the master server fails. I figure I could achieve this using network trickery for IP failover, CARP or the like and span a couple of vlans across the fibre. I would want each SQL server connected to separate edge routers to ensure both server and network resilience. Each box has two GigE NICs, so off the bat, I'd have each box doing VRRP to two separate edge gear at each location. My concern is, is that I can't envision how both boxes could possibly stay in a continuous state that would allow such fail-over, and fail-back. (fail-back is less of a concern...if it comes to it, I'd rebuild by hand if necessary). I've considered ZFS replication, but there could be several minutes worth of snapshot missing if the primary fails. I already have MySQL replication in many spots, but that's only one write master and read-only slaves. Can you provide any details or new ideas that I'm missing in order to have the holy grail of SQL redundancy? Cheers, Steve