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
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).
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 …
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.
So this is what I have come up with so far. I’d love to get some thoughts or suggestions on whether this is the correct approach.
class DaDbConnection extends CDbConnection{
public $dbConnectTries = 6;
public $numDatabases = 3;
private $_tries =0;
private $_db = 1;
/*
* Extends CDbConnection open() method
* Tries to connect to database connections setup in config/main.php up to
* the value of $dbConnectionTries or a connection is successful
* @throws CException If it can not connect to any DBs
*/
protected function open()
{
try{
//try to connect to the default DB
parent::open();
}catch(Exception $e){
if($this->_tries < $this->dbConnectTries){
//If there aren't anymore DBs to try we must start over from the first
if($this->_db >= $this->numDatabases){
$tryDb = 'db';
$this->_db = 0;
}else{
$tryDb = 'db'.$this->_db;
}
$this->_db++;
$this->_tries++;
$this->connectionString = Yii::app()->$tryDb->connectionString;
$this->username = Yii::app()->$tryDb->username;
$this->password = Yii::app()->$tryDb->password;
$this->open();
}else{
throw new CDbException('Could Not Connect to a DB.');
}
}
}
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.
