Yii多数据库主从读写分离实例介绍

本文实例讲述了Yii实现多数据库主从读写分离的方法。分享给大家供大家参考。具体分析如下：

Yii框架数据库多数据库、主从、读写分离 实现,功能描述:

1.实现主从数据库读写分离 主库：写 从库(可多个)：读

2.主数据库无法连接时 可设置从数据库是否 可写

3.所有从数据库无法连接时 可设置主数据库是否 可读

4.如果从数据库连接失败 可设置N秒内不再连接

利用yii扩展实现,代码如下:

代码如下:

array( 
     *   &＃39;db&＃39;=>array( 
     *    &＃39;connectionString&＃39;=>&＃39;mysql://&＃39;, 
     *    &＃39;slaves&＃39;=>array( 
     *     array(&＃39;connectionString&＃39;=>&＃39;mysql://&＃39;), 
     *     array(&＃39;connectionString&＃39;=>&＃39;mysql://&＃39;), 
     *    ) 
     *   ) 
     * ) 
     * */ 
    public $slaves = array(); 
    /** 
     *  
     * 从数据库状态 false 则只用主数据库 
     * @var bool $enableSlave 
     * */ 
    public $enableSlave = true; 
 
    /** 
     * @var slavesWrite 紧急情况主数据库无法连接 切换从服务器（读写）. 
     */ 
    public $slavesWrite = false; 
 
    /** 
     * @var masterRead 紧急情况从主数据库无法连接 切换从住服务器（读写）. 
     */ 
    public $masterRead = false; 
 
    /** 
     * @var _slave 
     */ 
    private $_slave; 
 
    /** 
     * @var _disableWrite 从服务器（只读）. 
     */ 
    private $_disableWrite = true; 
 
    /** 
     * 
     * 重写 createCommand 方法,1.开启从库 2.存在从库 3.当前不处于一个事务中 4.从库读数据 
     * @param string $sql 
     * @return CDbCommand 
     * */ 
    public function createCommand($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)) { 
 
                    throw new CDbException("Master db server is not available now!Disallow write operation on slave server!"); 
                } 
            } 
            return parent::createCommand($sql); 
        } 
    } 
 
    /** 
     * 获得从服务器连接资源 
     * @return CDbConnection 
     * */ 
    public function getSlave() { 
        if (!isset($this->_slave)) { 
            shuffle($this->slaves); 
            foreach ($this->slaves as $slaveConfig) { 
                if ($this->_isDeadServer($slaveConfig[&＃39;connectionString&＃39;])) { 
                    continue; 
                } 
                if (!isset($slaveConfig[&＃39;class&＃39;])) 
                    $slaveConfig[&＃39;class&＃39;] = &＃39;CDbConnection&＃39;; 
 
                $slaveConfig[&＃39;autoConnect&＃39;] = false; 
                try { 
                    if ($slave = Yii::createComponent($slaveConfig)) { 
                        Yii::app()->setComponent(&＃39;dbslave&＃39;, $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[&＃39;connectionString&＃39;]); 
                    Yii::log("Slave database connection failed!ntConnection string:{$slaveConfig[&＃39;connectionString&＃39;]}", &＃39;warning&＃39;); 
 
                    continue; 
                } 
            } 
 
            if (!isset($this->_slave)) { 
                $this->_slave = null; 
                $this->enableSlave = false; 
            } 
        } 
        return $this->_slave; 
    } 
 
    public function setActive($value) { 
        if ($value != $this->getActive()) { 
            if ($value) { 
                try { 
                    if ($this->_isDeadServer($this->connectionString)) { 
                        throw new CDbException(&＃39;Master db server is already dead!&＃39;); 
                    } 
                    //PDO::ATTR_TIMEOUT must set before pdo instance create 
                    $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, &＃39;exception.CDbException&＃39;); 
                    if ($slave) { 
                        $this->cOnnectionString= $slave->connectionString; 
                        $this->username = $slave->username; 
                        $this->password = $slave->password; 
                        if ($this->slavesWrite) { 
                            $this->_disableWrite = false; 
                        } 
                        $this->open(); 
                    } else { //Slave also unavailable 
                        if ($this->masterRead) { 
                            $this->cOnnectionString= $this->connectionString; 
                            $this->username = $this->username; 
                            $this->password = $this->password; 
                            $this->open(); 
                        } else { 
                            throw new CDbException(Yii::t(&＃39;yii&＃39;, &＃39;CDbConnection failed to open the DB connection.&＃39;), (int) $e->getCode(), $e->errorInfo); 
                        } 
                    } 
                } 
            } else { 
                $this->close(); 
            } 
        } 
    } 
 
    /** 
     * 检测读操作 sql 语句 
     *  
     * 关键字： SELECT,DECRIBE,SHOW ... 
     * 写操作:UPDATE,INSERT,DELETE ... 
     * */ 
    public static function isReadOperation($sql) { 
        $sql = substr(ltrim($sql), 0, 10); 
        $sql = str_ireplace(array(&＃39;SELECT&＃39;, &＃39;SHOW&＃39;, &＃39;DESCRIBE&＃39;, &＃39;PRAGMA&＃39;), &＃39;^O^&＃39;, $sql); //^O^,magic smile 
        return strpos($sql, &＃39;^O^&＃39;) === 0; 
    } 
 
    /** 
     * 检测从服务器是否被标记 失败. 
     */ 
    private function _isDeadServer($c) { 
        $cache = Yii::app()->{$this->cacheID}; 
        if ($cache && $cache->get(&＃39;DeadServer::&＃39; . $c) == 1) { 
            return true; 
        } 
        return false; 
    } 
 
    /** 
     * 标记失败的slaves. 
     */ 
    private function _markDeadServer($c) { 
        $cache = Yii::app()->{$this->cacheID}; 
        if ($cache) { 
            $cache->set(&＃39;DeadServer::&＃39; . $c, 1, $this->markDeadSeconds); 
        } 
    } 
}

main.php配置:components 数组中,代码如下:

代码如下:

&＃39;db&＃39;=>array( 
        &＃39;class&＃39;=>&＃39;application.extensions.DbConnectionMan&＃39;,//扩展路径 
        &＃39;connectionString&＃39; => &＃39;mysql:host=192.168.1.128;dbname=db_xcpt&＃39;,//主数据库 写 
        &＃39;emulatePrepare&＃39; => true, 
        &＃39;username&＃39; => &＃39;root&＃39;, 
        &＃39;password&＃39; => &＃39;root&＃39;, 
        &＃39;charset&＃39; => &＃39;utf8&＃39;, 
        &＃39;tablePrefix&＃39; => &＃39;xcpt_&＃39;, //表前缀 
        &＃39;enableSlave&＃39;=>true,//从数据库启用 
   &＃39;urgencyWrite&＃39;=>true,//紧急情况 主数据库无法连接 启用从数据库 写功能 
    &＃39;masterRead&＃39;=>true,//紧急情况 从数据库无法连接 启用主数据库 读功能 
        &＃39;slaves&＃39;=>array(//从数据库 
            array(   //slave1 
                &＃39;connectionString&＃39;=>&＃39;mysql:host=localhost;dbname=db_xcpt&＃39;, 
                &＃39;emulatePrepare&＃39; => true, 
                &＃39;username&＃39;=>&＃39;root&＃39;, 
                &＃39;password&＃39;=>&＃39;root&＃39;, 
                &＃39;charset&＃39; => &＃39;utf8&＃39;, 
                &＃39;tablePrefix&＃39; => &＃39;xcpt_&＃39;, //表前缀 
            ), 
   array(   //slave2 
                &＃39;connectionString&＃39;=>&＃39;mysql:host=localhost;dbname=db_xcpt&＃39;, 
                &＃39;emulatePrepare&＃39; => true, 
                &＃39;username&＃39;=>&＃39;root&＃39;, 
                &＃39;password&＃39;=>&＃39;root&＃39;, 
                &＃39;charset&＃39; => &＃39;utf8&＃39;, 
                &＃39;tablePrefix&＃39; => &＃39;xcpt_&＃39;, //表前缀 
            ), 
 
        ), 
),