RE: HA topology and replication techniques

Issues:

• For HA, you need machines in geographically distinct places.
• To deal with corruption, you need to have a slave that is deliberately a few hours behind on replication so you can catch the data before all copies are corrupt.
• Reads can be scaled by having more slaves; writes cannot be scaled beyond what one machine can handle.
• Suggest having more than one slave (if using replication) so that you can take on offline (out of load balancer) to do maintenance, etc
• Consider dual-master-single-writer. This is the closes to hot-hot in a replication environment. The tricky part is knowing for sure that the main master is dead and you should fail over to the other master. Controlled failover takes less than a minute and loses no data; drastic failover could lose some data (eg, binlog not yet replicated when disk crashes).
• Suggest having more than 100G for a 40G db -- allow room for ALTER TABLE, REPAIR, dumps, etc.
• Put db clients on machines other than the db machines. (You might include a web server on the db machine, but just for monitoring.)
• Cluster (IIRC) requires down time to do an ALTER TABLE. Not good for 24/7. With Dual-master, you can alter backup master, flip, alter other master -- a couple minute's downtime.

> -----Original Message-----
> From: Todd Reed [mailto:treed@astate.edu]
> Sent: Tuesday, July 03, 2007 1:44 PM
> To: replication@lists.mysql.com
> Subject: HA topology and replication techniques
>
> I have been doing a lot of reading on Replication and High
> Availability,
> but still cannot determine which pathway I should choose. I would
> appreciate any comments you may have.
>
>
>
>
>
> I think I've narrowed it down to two approaches:
>
>
>
> 1) MySQL HA Cluster with Heartbeat (Master/Master)
>
> 2) MySQL Replication with DRBD
>
>
>
> My primary goal
>
> High availability
>
>
>
> Other goals
>
> Would be nice to scale to a load balancing setup, if
> needed.
>
>
>
> Storage & Hardware
>
> Each server has its own hardware and storage unit
>
> 100G of database storage (current single server has 35-40G of data)
>
>
>
>
> Application requirements
>
> We use MyISAM tables and LAMP style web applications.
>
>
>
> Other information
>
> We will have a 3rd server at a remote location (out of
> state) that replicates the data for pure data redundancy. Will not be
> used for high availability.
>
>
>
>
>
> Main concerns
>
> 1) I can replicate the data to the other nodes, but I want to
> ensure that the web applications can write to those nodes and the it
> replicate to the "master" when it comes back on line.
>
> 2) I understand that some data may be lost during the actual
> failover. Data loss is not a primary concern, but database corruption
> is.
>
>
>
> Any thoughts on which method I should choose? How to professional web
> hosting companies do it? I'm really tempted to just decided
> with a coin
> toss!
>
>
>
> -Todd
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com