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 have two ISP's that provide me hosting via apache / php / mysql. I am running drupal on them. On occasion the mysql server will go away (crash), so I was hoping to find a reasonable way to have a fail over, if server A SQL is down, all traffic is sent to server B.

I know traditionally this is handled in DNS where a second alternate ip is given if there is a problem - or similar. But I do not have control over the isp, other than I can run php, perl and the usual apache stuff. Also, I have static ip's on each isp, and I can create dns entries (A/CNAME/TXT).

So, I was hoping there might be a way for me to have a script that checks if drupal has a problem, and if so, somehow alter dns, or ?

Or, any other ideas? (other than spending lots more $ on a better isp)

share|improve this question

migrated from stackoverflow.com Apr 20 '10 at 14:28

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

Round robin DNS isn't going to solve your problem - it's a great way to provide load balancing of webservers - but failover occurs when the client attempts to connect to a port and receives no reply (it then goes on to try the next DNS entry for the host). Obviously, if the MySQL database fails, then this will have no direct impact on the webserver (i.e. the webserver will respond to TCP requests).

Although it would be possible in principle to get the PHP code to detect a database failure and shutdown the webserver or block incoming connections - this is a rather dangerous approach - even if your hosting would allow for it to happen.

The only practical way I can think of to deal with this scenario is to redirect to a specific hostname in the event of a failure detection, so if you've currently got both hosts set up as www.example.com then add records for www1.example.com and www2.example.com, then add in an auto-prepend include file to do something like:

(on www1.example.com)
check_db();
// if check_db returns, then continue with normal processing...

function check_db() {
  if (request is for www.example.com) { // avoid loops when both sites fail
     if (last check more than 10 secs ago) {
         if (database status bad) {
            raise a database failed flag on the filesystem
            redirect to www2.example.com
            end
         }
     } else {
         if (database failed flag set) {
             redirect to www2.example.com
             end
         }
         return OK
     }
   } else { // request is for www1.example.com i.e. we are already in failover mode
     if (database failed flag set) && (last check more than 5 secs ago) {
         if (database status good) {
            remove database failed flag
            return OK
         } else { // oh no! both hosts down!
            print sorry message and exit
         }
     } else if (last check more than 5 secs ago) {
         if (database status bad) {
            raise a database failed flag on the filesystem
            print sorry message and exit
         }
     }
   }
   return OK
}

However you still need some way of testing if the database is working without using a blocking call.

HTH

C.

share|improve this answer
    
I like your approach the best, thanks. I guess another possibility would be to have a third host that has very simple code - no db, etc. for reliability and have it check the other hosts regularly to make sure they are up, and it redirects to the available host(s). This would probably allow both load balacing and fail over...? –  Scott Szretter Feb 27 '11 at 14:44

You are asking for fail over, not load balancing, methinks.

Fail over is extremely difficult to do well, especially with equipment which is not within the same infrastructure. Best case would be to get a redundant geographic load balancer at your ISP which tests your sites and handles failover seamlessly. Since my guess is that's out of your budget, let's go for the sticks and chewing-gum method.

Since your issue apparently is MySQL, not the web server, then let's solve the problem at hand.

Given:

  • Host A: Web server and MySQL database
  • Host B: Web server and redundant MySQL database

The method I would use would be:

  1. Store your database connection credentials on disk somewhere (obviously not within the web root) and load them upon each page connection. You can edit sites/default/settings.php like so:

    $db_url = file_get_contents('/some/private/dir/drupal-db.url');

  2. Write a background daemon which connects to the database every 5 seconds (or so), and logs failures in a machine-readable way on disk. After the connection fails after 30 seconds (or so), then "swap" the credentials stored on disk with those of the backup server. This will cause your web server to serve content from the alternate database server. If it comes back, reverse the process. Logging is essential in this case for debugging, etc.

  3. If you want to get fancy, you can try to log all INSERT and UPDATE to disk on the web server, so you can potentially re-sync the databases after a failover. If it's mostly "read-only" then this may not be necessary.

The primary point here is to decouple fail over from the operation of the web app. It's more modular, and simplifies changes to the web app.

Finally, make sure that you can connect to your backup database from the primary server. You can do this on the command line, and using grant commands:

On Host B:

mysql> GRANT ALL PRIVILEGES ON drupaldb.* TO username@'12.34.56.78' IDENTIFIED BY 'mypassword';

Where the IP is of Host A.

And don't forget to flush!

mysql> FLUSH PRIVILEGES:

Then test:

bash> mysql -u username -pmypassword -h hostb drupaldb
share|improve this answer

No way, there is no decent technology for that - given your infrastructure.

  • DNS won't work unless you keep your DNS domain timeout SMALL (seconds range) which is fronws upon. IF you can do that, you could script it properly (script on server 2 can not reach server 1, thus changes the DNS entries). That pretty much is the only way to do it.

  • Depending on how your DNS setup is, this would be either there, OR - registering a host entry using something like dyndns.org and replace your IN A entry with a CNAME. DynDNS.org has a nice API that you could call via HTTP to change the entry, and the CNAME would never change. They also keep their domains on short TTL's.

That pretty much are all options. THere are others for that, but they require a lot of infrastructure that you don't have in place.

share|improve this answer
    
The DNS refresh time has nothing to do with failover - at best you cannot get an effective TTL of less than 3 hours regardless wht you put in your config files. –  symcbean Apr 20 '10 at 11:59
    
Crap answer. FIrst, DynDNS gives me efficiently about 10 seconds TTL. Bad news - it is possible. Second, without killing the DNS you can not redirect.... the domain. And any IP based redirection requires more infrastructure like a proper front end router. –  TomTom Apr 20 '10 at 12:44

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.