Yii实现多数据库主从读写分离的方法
本文实例讲述了Yii实现多数据库主从读写分离的方法。分享给大家供大家参考。具体分析如下：
Yii框架数据库多数据库、主从、读写分离实现,功能描述:
1.实现主从数据库读写分离主库：写从库(可多个)：读
2.主数据库无法连接时可设置从数据库是否可写
3.所有从数据库无法连接时可设置主数据库是否可读
4.如果从数据库连接失败可设置N秒内不再连接
利用yii扩展实现,代码如下:
<?php
/**
*主数据库写从数据库（可多个）读
*实现主从数据库读写分离主服务器无法连接从服务器可切换写功能
*从务器无法连接主服务器可切换读功
*bylmt
**/
classDbConnectionManextendsCDbConnection{
public$timeout=10;//连接超时时间
public$markDeadSeconds=600;//如果从数据库连接失败600秒内不再连接
//用cache作为缓存全局标记
public$cacheID='cache';
/**
*@vararray$slaves.Slavedatabaseconnection(Read)configarray.
*配置符合CDbConnection.
*@example
*'components'=>array(
* 'db'=>array(
* 'connectionString'=>'mysql://<master>',
* 'slaves'=>array(
* array('connectionString'=>'mysql://<slave01>'),
* array('connectionString'=>'mysql://<slave02>'),
* )
* )
*)
**/
public$slaves=array();
/**
*
*从数据库状态false则只用主数据库
*@varbool$enableSlave
**/
public$enableSlave=true;
/**
*@varslavesWrite紧急情况主数据库无法连接切换从服务器（读写）.
*/
public$slavesWrite=false;
/**
*@varmasterRead紧急情况从主数据库无法连接切换从住服务器（读写）.
*/
public$masterRead=false;
/**
*@var_slave
*/
private$_slave;
/**
*@var_disableWrite从服务器（只读）.
*/
private$_disableWrite=true;
/**
*
*重写createCommand方法,1.开启从库2.存在从库3.当前不处于一个事务中4.从库读数据
*@paramstring$sql
*@returnCDbCommand
**/
publicfunctioncreateCommand($sql=null){
if($this->enableSlave&&!emptyempty($this->slaves)&&is_string($sql)&&!$this->getCurrentTransaction()&&self::isReadOperation($sql)&&($slave=$this->getSlave())
){
return$slave->createCommand($sql);
}else{
if(!$this->masterRead){
if($this->_disableWrite&&!self::isReadOperation($sql)){
thrownewCDbException("Masterdbserverisnotavailablenow!Disallowwriteoperationonslaveserver!");
}
}
returnparent::createCommand($sql);
}
}
/**
*获得从服务器连接资源
*@returnCDbConnection
**/
publicfunctiongetSlave(){
if(!isset($this->_slave)){
shuffle($this->slaves);
foreach($this->slavesas$slaveConfig){
if($this->_isDeadServer($slaveConfig['connectionString'])){
continue;
}
if(!isset($slaveConfig['class']))
$slaveConfig['class']='CDbConnection';
$slaveConfig['autoConnect']=false;
try{
if($slave=Yii::createComponent($slaveConfig)){
Yii::app()->setComponent('dbslave',$slave);
$slave->setAttribute(PDO::ATTR_TIMEOUT,$this->timeout);
$slave->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
$slave->setActive(true);
$this->_slave=$slave;
break;
}
}catch(Exception$e){
$this->_markDeadServer($slaveConfig['connectionString']);
Yii::log("Slavedatabaseconnectionfailed!ntConnectionstring:{$slaveConfig['connectionString']}",'warning');
continue;
}
}
if(!isset($this->_slave)){
$this->_slave=null;
$this->enableSlave=false;
}
}
return$this->_slave;
}
publicfunctionsetActive($value){
if($value!=$this->getActive()){
if($value){
try{
if($this->_isDeadServer($this->connectionString)){
thrownewCDbException('Masterdbserverisalreadydead!');
}
//PDO::ATTR_TIMEOUTmustsetbeforepdoinstancecreate
$this->setAttribute(PDO::ATTR_TIMEOUT,$this->timeout);
$this->open();
}catch(Exception$e){
$this->_markDeadServer($this->connectionString);
$slave=$this->getSlave();
Yii::log($e->getMessage(),CLogger::LEVEL_ERROR,'exception.CDbException');
if($slave){
$this->connectionString=$slave->connectionString;
$this->username=$slave->username;
$this->password=$slave->password;
if($this->slavesWrite){
$this->_disableWrite=false;
}
$this->open();
}else{//Slavealsounavailable
if($this->masterRead){
$this->connectionString=$this->connectionString;
$this->username=$this->username;
$this->password=$this->password;
$this->open();
}else{
thrownewCDbException(Yii::t('yii','CDbConnectionfailedtoopentheDBconnection.'),(int)$e->getCode(),$e->errorInfo);
}
}
}
}else{
$this->close();
}
}
}
/**
*检测读操作sql语句
*
*关键字：SELECT,DECRIBE,SHOW...
*写操作:UPDATE,INSERT,DELETE...
**/
publicstaticfunctionisReadOperation($sql){
$sql=substr(ltrim($sql),0,10);
$sql=str_ireplace(array('SELECT','SHOW','DESCRIBE','PRAGMA'),'^O^',$sql);//^O^,magicsmile
returnstrpos($sql,'^O^')===0;
}
/**
*检测从服务器是否被标记失败.
*/
privatefunction_isDeadServer($c){
$cache=Yii::app()->{$this->cacheID};
if($cache&&$cache->get('DeadServer::'.$c)==1){
returntrue;
}
returnfalse;
}
/**
*标记失败的slaves.
*/
privatefunction_markDeadServer($c){
$cache=Yii::app()->{$this->cacheID};
if($cache){
$cache->set('DeadServer::'.$c,1,$this->markDeadSeconds);
}
}
}main.php配置:components数组中,代码如下:
'db'=>array(
'class'=>'application.extensions.DbConnectionMan',//扩展路径
'connectionString'=>'mysql:host=192.168.1.128;dbname=db_xcpt',//主数据库写
'emulatePrepare'=>true,
'username'=>'root',
'password'=>'root',
'charset'=>'utf8',
'tablePrefix'=>'xcpt_',//表前缀
'enableSlave'=>true,//从数据库启用
'urgencyWrite'=>true,//紧急情况主数据库无法连接启用从数据库写功能
'masterRead'=>true,//紧急情况从数据库无法连接启用主数据库读功能
'slaves'=>array(//从数据库
array( //slave1
'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
'emulatePrepare'=>true,
'username'=>'root',
'password'=>'root',
'charset'=>'utf8',
'tablePrefix'=>'xcpt_',//表前缀
),
array( //slave2
'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
'emulatePrepare'=>true,
'username'=>'root',
'password'=>'root',
'charset'=>'utf8',
'tablePrefix'=>'xcpt_',//表前缀
),
),
),
希望本文所述对大家基于Yii框架的php程序设计有所帮助。