Skip Content

Articles

Adding Secondary MySQL Connections With PHP

Recently my host's MySQL server went down. I have an old remotely hosted MySQL account, so I threw my data there and put up some new connection parameters.
But then I figured I'd write a little something to make it more automatic. Let's say "mysql.server.one" goes down, the new code automatically connects to "mysql.server.two".
It's simple really, of course a quick search on Google revealed PHP Everywhere had a good starting point. I needed some extra things though like two different user / password combos and a different db name. So here's a rough sketch of what I came up with:
function db_connect_plus(){ // returns a link identifier on success, or false on error
    $primary = "localhost"; // Primary MySQL host info
    $primary_uid = "primary_mysql_username";
    $primary_pwd = "primary_mysql_password";
	
	$secondary = "backup.mysql.servername"; // Secondary MySQL host info
    $secondary_uid = "secondary_mysql_username";
    $secondary_pwd = "secondary_mysql_password";
	
	$timeout = 15;  // timeout in seconds
	
	error_reporting(0); // Turn off error reporting in case one is down.
	
	if ($fp = fsockopen($primary, 3306, &$errno, &$errstr, $timeout)) {
		fclose($fp);
		return $clm = mysql_pconnect($primary, $primary_uid, $primary_pwd);
	}
	
	if ($fp = fsockopen($secondary, 3306, &$errno, &$errstr, $timeout)) {
		fclose($fp);
		return $clm = mysql_pconnect($secondary, $secondary_uid, $secondary_pwd);
	}
return 0;
}

$current_db = db_connect_plus(); // Get connection

/*
* If the mysql host is localhost, set the primary server's db name
* Otherwise, set the db name for the secondary server
*/
if (strchr(mysql_get_host_info(), "localhost")) {
	$database_name = "primary_db_name"; 
} else {
	$database_name = "secondary_db_name";
}

Of course, you'll need to change the db info for it to work. There's always more than one way to skin a cat, so if you know a better way, let me know!

Added On: 2004-02-03

  

Homepage Usability: 50 Websites Deconstructed

Homepage Usability: 50 Websites Deconstructed

Marie Tahir

Pearson Education

Usually ships within 24 hours

Buy used for as low as $19.95

 

508 | XHTML 1.0 | CSS 2.0 | PHP 5.2.11 | MySQL 5.0.89 | RSS 2.0 | Print

Copyright © 2010 Chris Martin