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

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

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

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

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

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

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

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

<?php<br />/** <br /> * 主数据库 写 从数据库（可多个）读 <br /> * 实现主从数据库 读写分离 主服务器无法连接 从服务器可切换写功能 <br /> * 从务器无法连接 主服务器可切换读功 <br /> * by lmt <br /> * */ <br />class DbConnectionMan extends CDbConnection { <br />    public $timeout = 10; //连接超时时间 <br />    public $markDeadSeconds = 600; //如果从数据库连接失败 600秒内不再连接  <br />    //用 cache 作为缓存全局标记 <br />    public $cacheID = 'cache'; <br /> <br />    /** <br />     * @var array $slaves.Slave database connection(Read) config array. <br />     * 配置符合 CDbConnection. <br />     * @example <br />     * 'components'=>array( <br />     *   'db'=>array( <br />     *    'connectionString'=>'mysql://', <br />     *    'slaves'=>array( <br />     *     array('connectionString'=>'mysql://'), <br />     *     array('connectionString'=>'mysql://'), <br />     *    ) <br />     *   ) <br />     * ) <br />     * */ <br />    public $slaves = array(); <br />    /** <br />     *  <br />     * 从数据库状态 false 则只用主数据库 <br />     * @var bool $enableSlave <br />     * */ <br />    public $enableSlave = true; <br /> <br />    /** <br />     * @var slavesWrite 紧急情况主数据库无法连接 切换从服务器（读写）. <br />     */ <br />    public $slavesWrite = false; <br /> <br />    /** <br />     * @var masterRead 紧急情况从主数据库无法连接 切换从住服务器（读写）. <br />     */ <br />    public $masterRead = false; <br /> <br />    /** <br />     * @var _slave <br />     */ <br />    private $_slave; <br /> <br />    /** <br />     * @var _disableWrite 从服务器（只读）. <br />     */ <br />    private $_disableWrite = true; <br /> <br />    /** <br />     * <br />     * 重写 createCommand 方法,1.开启从库 2.存在从库 3.当前不处于一个事务中 4.从库读数据 <br />     * @param string $sql <br />     * @return CDbCommand <br />     * */ <br />    public function createCommand($sql = null) { <br />        if ($this->enableSlave && !emptyempty($this->slaves) && is_string($sql) && !$this->getCurrentTransaction() && self::isReadOperation($sql) && ($slave = $this->getSlave()) <br />        ) { <br />            return $slave->createCommand($sql); <br />        } else { <br />            if (!$this->masterRead) { <br />                if ($this->_disableWrite && !self::isReadOperation($sql)) { <br /> <br />                    throw new CDbException("Master db server is not available now!Disallow write operation on slave server!"); <br />                } <br />            } <br />            return parent::createCommand($sql); <br />        } <br />    } <br /> <br />    /** <br />     * 获得从服务器连接资源 <br />     * @return CDbConnection <br />     * */ <br />    public function getSlave() { <br />        if (!isset($this->_slave)) { <br />            shuffle($this->slaves); <br />            foreach ($this->slaves as $slaveConfig) { <br />                if ($this->_isDeadServer($slaveConfig['connectionString'])) { <br />                    continue; <br />                } <br />                if (!isset($slaveConfig['class'])) <br />                    $slaveConfig['class'] = 'CDbConnection'; <br /> <br />                $slaveConfig['autoConnect'] = false; <br />                try { <br />                    if ($slave = Yii::createComponent($slaveConfig)) { <br />                        Yii::app()->setComponent('dbslave', $slave); <br />                        $slave->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout); <br />                        $slave->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); <br />                        $slave->setAc<p style="color:transparent">。本文来源gao!%daima.com搞$代*!码网1</p><cite>搞代gaodaima码</cite>tive(true); <br />                        $this->_slave = $slave; <br />                        break; <br />                    } <br />                } catch (Exception $e) { <br />                    $this->_markDeadServer($slaveConfig['connectionString']); <br />                    Yii::log("Slave database connection failed!ntConnection string:{$slaveConfig['connectionString']}", 'warning'); <br /> <br />                    continue; <br />                } <br />            } <br /> <br />            if (!isset($this->_slave)) { <br />                $this->_slave = null; <br />                $this->enableSlave = false; <br />            } <br />        } <br />        return $this->_slave; <br />    } <br /> <br />    public function setActive($value) { <br />        if ($value != $this->getActive()) { <br />            if ($value) { <br />                try { <br />                    if ($this->_isDeadServer($this->connectionString)) { <br />                        throw new CDbException('Master db server is already dead!'); <br />                    } <br />                    //PDO::ATTR_TIMEOUT must set before pdo instance create <br />                    $this->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout); <br />                    $this->open(); <br />                } catch (Exception $e) { <br />                    $this->_markDeadServer($this->connectionString); <br />                    $slave = $this->getSlave(); <br />                    Yii::log($e->getMessage(), CLogger::LEVEL_ERROR, 'exception.CDbException'); <br />                    if ($slave) { <br />                        $this->connectionString = $slave->connectionString; <br />                        $this->username = $slave->username; <br />                        $this->password = $slave->password; <br />                        if ($this->slavesWrite) { <br />                            $this->_disableWrite = false; <br />                        } <br />                        $this->open(); <br />                    } else { //Slave also unavailable <br />                        if ($this->masterRead) { <br />                            $this->connectionString = $this->connectionString; <br />                            $this->username = $this->username; <br />                            $this->password = $this->password; <br />                            $this->open(); <br />                        } else { <br />                            throw new CDbException(Yii::t('yii', 'CDbConnection failed to open the DB connection.'), (int) $e->getCode(), $e->errorInfo); <br />                        } <br />                    } <br />                } <br />            } else { <br />                $this->close(); <br />            } <br />        } <br />    } <br /> <br />    /** <br />     * 检测读操作 sql 语句 <br />     *  <br />     * 关键字： SELECT,DECRIBE,SHOW ... <br />     * 写操作:UPDATE,INSERT,DELETE ... <br />     * */ <br />    public static function isReadOperation($sql) { <br />        $sql = substr(ltrim($sql), 0, 10); <br />        $sql = str_ireplace(array('SELECT', 'SHOW', 'DESCRIBE', 'PRAGMA'), '^O^', $sql); //^O^,magic smile <br />        return strpos($sql, '^O^') === 0; <br />    } <br /> <br />    /** <br />     * 检测从服务器是否被标记 失败. <br />     */ <br />    private function _isDeadServer($c) { <br />        $cache = Yii::app()->{$this->cacheID}; <br />        if ($cache && $cache->get('DeadServer::' . $c) == 1) { <br />            return true; <br />        } <br />        return false; <br />    } <br /> <br />    /** <br />     * 标记失败的slaves. <br />     */ <br />    private function _markDeadServer($c) { <br />        $cache = Yii::app()->{$this->cacheID}; <br />        if ($cache) { <br />            $cache->set('DeadServer::' . $c, 1, $this->markDeadSeconds); <br />        } <br />    } <br />}

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

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

希望本文所述对大家基于Yii框架的php程序设计有所帮助。

喜欢 (0)赏

[搞代码]

分享 (0)