Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to see if the following plan is viable. Goal here is to be able to do HA (uptime) and not necessarily for load -- writes are fine on one MySQL 5.5 server (with innodb) but not really possible when the database is down.

Currently, I have a master-slave replication setup which works fine except it doesn't have automatic promotion (obviously). what I am planning on doing is setup master-master replication to possibly do this "automatic promotion" using Amazon Route 53 DNS Failover (Health checks). What I am trying to avoid is to NOT have to do the auto-increment trick because the "business folks" got used to the auto-incrementing PK as consecutive numbers (yeah, I know this is bad but data is from 2004).

So, setup the master-master replication WITHOUT the auto-increment collision prevention bit. The primary master is db1.domain.com and secondary master is db2.domain.com

In Amazon Route 53, setup DNS Failover record for db.domain.com -> primary failover is db1.domain.com -> with a TCP healthcheck on IP address port 3306 -> secondary failover is db2.domain.com -> with a TCP healthcheck on IP address port 3306

Most of the time (99%), unless tcp://db1.domain.com:3306 is dead, db1.domain.com will be served up on DNS hits to db.domain.com. In fact, hopefully this is 100%. The possible downsides of this is the loss of a primary key (collision) and I think I am OK with losing one order. We are a low data volume B2B business and can just call our client up if this occurs (like an order disappearing).

Does this sound like a good plan?

Then I will also run another slave replication on db1.domain.com as "master" to a slave-db1.domain.com -- not sure why, maybe for heavy SELECTs?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

It's not really that simple to do a DNS Failover for a database. There are many reasons, but here are a few that might cause problems.

  • A lot of applications use connection pooled libraries, so they might create persistent connections to the database, so the assumption that a DNS failover might actually cause all application traffic (reads and writes) to go to the new server, and prevent situations where writes might happen to both and cause primary key collisions.

  • Now, the situation described above might not be a problem if the primary database were to actually go down, as that will kill any SQL connections present, and hence cause any dual write issues to be mitigated. The problems will happen when under high load, the MySQL server starts rejecting new connections. The DNS failover will trigger, existing connections remain on the current server, and new connections are created to the failover target. Now you're in for trouble!

  • Replication lag, and multi-master replication can add another tangent altogether to this equation. You don't really want to be too far behind from the primary when doing a safe failover; the issues that can happen as a result of that are too innumerable to list here.

Take a look at a solution like ScaleArc. It's state aware and understands stuff like replication lag, and offers some neat HA options, along with many other features like caching, analytics etc.

share|improve this answer
    
Also, DNS failover won't result in clean cut-overs due to DNS caching. You could easily end up with a few minutes of split-brain behaviour, which you'll have to remedy manually by analyzing the replication logs. –  200_success Aug 25 '13 at 18:08
add comment

This sounds like a workable plan. I wouldn't use dns for the failure. I'd use something like LinuxHA or ucarp to manage a floating IP which will determine your writer DB. This is especially true if you have multiple clients using these DBs.

share|improve this answer
    
Not sure if I can handle LinuxHA or ucarp -- it may be yet another thing to fail. I've been playing around with Amazon's Route53 DNS failover and it seems like if it works, the DNS will make sure that only one master is being written to AND it will always try to get the primary to the front of the line. –  Chris Go Jun 28 '13 at 2:20
add comment

trying to avoid is to NOT have to do the auto-increment trick

Get over it.

So presumably you have no transactions either, and are happy with the downtime for schema updates.

If your "business folks" want auto generated IDs to be consecutive then ask them how to implement a secure high availability system system without this. It is quite possible but it's very, VERY slow and fails to deal with all the other bad things which master-master replication fixes.

You'll note that the Amazon documentation only talks about using their failover services for handling webservers - there's a reason for that (and arguably its not even a good strategy for webservers). There are contexts where implementing high availability at the client is a good idea (and these rely on round-robin addressing - not failover).

I think I am OK with losing one order

Even with a 0s TTL you can reasonably expect propagation to take around 2 hours. You've given much detail about your software stack nor where it is located. With PHP/non-persistent running inside AWS then you'll get a faster recovery, but with persistent connections (e.g. Java) then you could have a very long outage.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.