Use alternate database if one connect fails

[matthew_ellis24]

Hi, I've had a look for this, but I'm not sure it's possible without specialist software. So what I want to know is, is it possible to set up your queries in such a way that if your connection to one database fails (because your host is naff and keeps crashing) it connects to an alternate server?

 

I use this to connect to my database:

mysql_connect($host,$username,$password);

mysql_select_db($database) or die( "Unable to select database");

 

and I've tried replacing the "or die" command with a new mysql connect, but no luck.

 

Is it possible?

Thanks

Matt

[NiteCloak]

Not sure if this is what you are looking for but I also have considered this.

 

Article on DBForums about mirroring/replication

 

I also read somewhere another guy asking about realtime redundancy of his MySQL database and the response was thus:

 

Add something like this to the mysqld section your my.ini file

 

[mysqld]

server-id=2

master-host=miniserver

master-user=account

master-password=apass

master-port=3306

report-host=hostname

 

 

Create an account on the master and put the user and password in the section above and the correct servername.

You will also have to enable binary logging in the masters my.ini

[revraz]

I know you posted this in the MySQL forum, but if you are using a language like PHP, there should be no reason why you can't use another DB if your connect fails for one.

[matthew_ellis24]

I've been trying to get this working using the code below, but when the primary server goes down I get can't connect errors for both of them. I'm certain the login details for the other server are correct because they work fine from that site, but they don't seem to work from a different site. What did I miss?

 

Many thanks

 

  <?php
$username="";
$password="";
$database="";
$dberror="";

$link= mysql_connect('mysql2.freehostia.com',$username,$password);
mysql_select_db($database);

if (!$link) {
$username1="";
$password1="";
$database1="";
$dberror= "<b>WARNING</b>Our primary database is down. You are currently using our secondary database which may not be as up to date. We apologise for any inconvenience<br>";

$link1= mysql_connect('193.62.81.10',$username1,$password1);
mysql_select_db($database1) or die( "Unable to select database");
}
?>

[matthew_ellis24]

Obviously when I said "but when the primary server goes down I get can't connect errors for both of them" I meant I get connect errors from both and can't connect to either.

 

Sorry, couldn't see how to edit my original post!

[fenway]

Sounds like you might actually be on a different box/ip...

[matthew_ellis24]

the secondary database is on a different host to the website - is that a problem?

[fenway]

the secondary database is on a different host to the website - is that a problem?

Not a problem per se, but it's likely that it's been ste up to only allow localhost connections, either in the IP tables and/or in the mysql grant tables.

[matthew_ellis24]

I have access to these, but don't know which to change. Can you give me a push in the right direction? is that in the config.inc file?

[fenway]

Check the grant table first... that's more likely to be the issue, and it's easier to fix -- your user probably doesn't have remote access.