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

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

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

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

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

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

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

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

<?php
/**
 * 主数据库 写 从数据库（可多个）读
 * 实现主从数据库 读写分离 主服务器无法连接 从服务器可切换写功能
 * 从务器无法连接 主服务器可切换读功
 * by lmt
 * */
class dbconnectionman extends cdbconnection {
    public $timeout = 10; //连接超时时间
    public $markdeadseconds = 600; //如果从数据库连接失败 600秒内不再连接 
    //用 cache 作为缓存全局标记
    public $cacheid = 'cache';
 
    /**
     * @var array $slaves.slave database connection(read) config array.
     * 配置符合 cdbconnection.
     * @example
     * 'components'=>array(
     *   'db'=>array(
     *    'connectionstring'=>'mysql://<master>',
     *    'slaves'=>array(
     *     array('connectionstring'=>'mysql://<slave01>'),
     *     array('connectionstring'=>'mysql://<slave02>'),
     *    )
     *   )
     * )
     * */
    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['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("slave database connection failed!ntconnection string:{$slaveconfig['connectionstring']}", 'warning');
 
                    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('master db server is already dead!');
                    }
                    //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, '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 { //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('yii', 'cdbconnection failed to open the db connection.'), (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('select', 'show', 'describe', 'pragma'), '^o^', $sql); //^o^,magic smile
        return strpos($sql, '^o^') === 0;
    }
 
    /**
     * 检测从服务器是否被标记 失败.
     */
    private function _isdeadserver($c) {
        $cache = yii::app()->{$this->cacheid};
        if ($cache && $cache->get('deadserver::' . $c) == 1) {
            return true;
        }
        return false;
    }
 
    /**
     * 标记失败的slaves.
     */
    private function _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程序设计有所帮助。