Database Fail Over
Rate Topic:

Hello all! I am currently working on a project for which we have chosen Yii as our new Framework of choice. I am currently trying to figure out the best way to implement some sort of automatic database fail over in Yii. My thought so far is to over-ride the getDBCOnnection() in CActiveRecord, but I am not sure if this is the correct path to take. Basically what I am looking to do is check a DB connection and if it fails connect to another DB. Simple concept I am just not sure where to put it. I know there are better ways to do this by using mysqlnd_ms but it is not setup on the servers we are using yet so have to come up with a way to do this in Yii. Any help is greatly appreciated. -DA

Hello all! I am currently working on a project for which we have chosen Yii as our new Framework of choice. I am currently trying to figure out the best way to implement some sort of automatic database fail over in Yii. My thought so far is to over-ride the getDBCOnnection() in CActiveRecord, but I am not sure if this is the correct path to take. Basically what I am looking to do is check a DB connection and if it fails connect to another DB. Simple concept I am just not sure where to put it. I know there are better ways to do this by using mysqlnd_ms but it is not setup on the servers we are using yet so have to come up with a way to do this in Yii. Any help is greatly appreciated. -DA

Hey, thanks so much for the reply.
So, I am not familiar with HAProxy and how it could help me, I will however look into it.

The thing is, we already have servers with stuff on them, I am not in the networking department so load balancing is basically up to them. That being said, our old framework had DB fail over in the code and I have been tasked with doing that same thing in Yii. I need to know how to best approach it from that perspective, but also can recommend better ways if they are out there.

HAProxy used to be a tcp/http load balancer. But it is also good for mysql failovers. There's also mysql-proxy, which is operating closer to the MySQL protocol and might be even better (although it's in alpha stage).

programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code

In the meantime, does anyone have any suggestions for doing this in Yii?

Yes. As I wrote above: Extend CDbConnection so you can specify multiple connection params. I remember somebody implementing split read/write like that. Unfortunately I cannot find that solution again ...

programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code

I did see something like that somewhere also, but it was changing core Yii stuff and also not exactly what I am looking for, I don't think.
So, if I took the route of extending CDbConnection, I am not sure exactly what methods I would change. I wouldn't know where to begin honestly. Where in CDbConnection does it get the config variable and connect to the DB? I assume that is where I would say "test connection" if it can't connect try the other.

I am fairly new to Yii, so I am confused on what the Yii approach would look like. I really hope someone could point me in the correct direction more directly. I looked at that blog post, but it doesn't look to be specifically for Yii I will look at it more closely to see if it would be of any help.