Database Failover example

[inspireme]

If you have 2 hosting accounts, its a good idea to replicate or backup your database between them. I have been doing this for a while, a cron jobbacks up between servers each hour. Sometimes my database goes down, and i figured it would be a good idea to make indexU use the other database if a connection fails.

This is a simple way to achive this :

In application.php do this :


// primary :
$dbHostname = "existingdatabase";
$dbUsername = "existingusername";
$dbPassword = "existingpass";
$dbName = "existingname";

$dbconnect=mysql_select_db($dbName, mysql_connect($dbHostname,$dbUsername,$dbPassword) );

if (!$dbconnect) {
$dbServer = "mysql";
$dbHostname = "yourdomain.com";
$dbUsername = "username 1";
$dbPassword = "password 1";
$dbName = "database name 1";
}

as soon as database 1 fails to connect, database 2 is used. One small problem is any database changes made during the time DB1 is down will be lost, however if you added some code to add.php that checked which hostname you were using (if != whatever) then you can just send users to an error page to avoid this.

Just thought it maybe useful to someone.

[Antony]

Nice idea....
Antony

[FSGDAG]

This is GREAT Inspireme! Thanks for the tip

[msprate]

Are you doing any checking to make sure they are always in sync?

Would be a great module.

[Bruceper]

To synchronize the databases requires a lot more work, and in some cases either root access or the ability to install software as root. If you're on a shared server this is out of your reach.

[dody]

how do you sync?

[Bruceper]

Clustering is the answer, but now you're stepping up into the world of "you better know what you're doing".

http://www.davz.net/static/howto/mysqlcluster

You can also go for replication as well.

http://www.netexpertise.eu/en/Mysql/Failover.html

[inspireme]

I have 1 server that i have root access with but the other one i dont. Heres what i have done :

1) I Have phpmybackuppro, set up to bacup he database each 4 hours, this is emailed to a gmail account and uploaded to web server 2.
2) on a cron, each 5 hours the mysql backup thats been FTP's is executed on server 2, so i get the database backed up every 6 hours.


Its not a perfect syncronization, and it really isnt something you would use on site sthat have constant database changes. Its perfctly fine for my needs though, I get lots of backups, and a fall back solution.