CodeIgniter Forums
Connect to alternate DB when default DB is down? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Connect to alternate DB when default DB is down? (/thread-10391.html)



Connect to alternate DB when default DB is down? - El Forum - 07-29-2008

[eluser]bradym[/eluser]
On our sites that are not using CodeIgniter we have code like the following:

Code:
$db = mysql_connect('localhost', 'username', 'password');

if(!$db){
     $db = mysql_connect('remote_server', 'username', 'password') ;    
}

to connect to a backup server if the local mysql server is down for some reason.

Has anyone done this kind of thing with CodeIgniter? If so, would you mind sharing code or ideas?

Thanks!

Brady


Connect to alternate DB when default DB is down? - El Forum - 07-29-2008

[eluser]Randy Casburn[/eluser]
Hey Brady -- how "production" is this server setup you're working with? I mean are the servers replicated? How is the data kept synchronized? between local and remote, etc.

If these are not production servers and you want to investigate the 'next' way to do this with MySQL, there is an ALPHA version of something very very cool that will take of this for you...built by MySQL. It's called MySQL Proxy. The project page can be found here -> http://forge.mysql.com/wiki/MySQL_Proxy.

Will load balance your DB servers and take care of all this magic for you...on the DB platform where it belongs.

Pretty cool...but it is ALPHA...so tread lightly.

Randy


Connect to alternate DB when default DB is down? - El Forum - 07-29-2008

[eluser]bradym[/eluser]
Sadly, our databases are not replicated or load balanced right now. A new db design with replication and load balancing is being developed but won't be ready for some time. Unfortunately MySQL Proxy isn't really an option yet, since (as you point out) it's still alpha.

So.. for now we need to continue to handle this in the code.

After my last post, I did a var_dump on the results of a failed db connect (just gave it the wrong password) and noticed that the conn_id property of the object is false. Based on that I'm playing with some code, I'll post back when I've got something working.


Connect to alternate DB when default DB is down? - El Forum - 07-29-2008

[eluser]Randy Casburn[/eluser]
I'm thinking as I type, so...

If you're auto loading your DB, you could hook into a pre_controller routine that tests for the DB connection exactly as you've described above...so in code:

Code:
$this->load->database('local_group_name');
if ( ! this->db )
{
     $this->load->database('remote_group_name');
    
}

That is exactly what your code above does. In a pre_controller hook you could ensure your CI_DB object wouldn't have to change. In other words, you would be able to use $this->db to access the CI_DB object rather than separate names for separate connections.

Again, thinking as I'm typing, but this would achieve what you want as a temporary stop-gap.

Randy


Connect to alternate DB when default DB is down? - El Forum - 07-30-2008

[eluser]bradym[/eluser]
I haven't been able to get it working as a hook, but I've built a class with code similar to what you posted in the constructor and auto loading it works great. I'm playing around with adding some other useful functions to the class now.

Thanks for your suggestions Randy! Smile


Connect to alternate DB when default DB is down? - El Forum - 07-30-2008

[eluser]Randy Casburn[/eluser]
Great! Glad I could help.