提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

Yii实现多数据库主从读写分离的方法

数据库方法 实现 读写 主从 分离 yii
2023-06-13 09:15:38 时间

本文实例讲述了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程序设计有所帮助。

相关文章