load balancing two web servers each on two different isp's?

Question

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)

Answer 1

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.

Answer 2

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

Answer 3

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.