Yii method to implement master-slave reading and writing separation in multiple databases_php tutorial

This article mainly introduces the method of Yii to realize the separation of master-slave reading and writing in multiple databases. It is a very practical skill to realize the separation function of master-slave reading and writing in multiple databases by extending the Yii database class. Friends who need it can refer to it. Next

The example in this article describes how Yii implements master-slave read and write separation in multiple databases. Share it with everyone for your reference. The specific analysis is as follows:

Yii framework database multi-database, master-slave, read-write separation implementation, functional description:

1. Realize master-slave database read-write separation master library: write slave library (can Multiple): Read

2. When the master database cannot be connected, you can set whether the slave database is writable

3. When all slave databases cannot be connected, you can set whether the master database is readable

4. If the connection from the database fails, you can set it to not connect again within N seconds

Use the yii extension to implement, the code is as follows:

The code is as follows:

<?php
/** 
 * 主数据库 写 从数据库（可多个）读 
 * 实现主从数据库 读写分离 主服务器无法连接 从服务器可切换写功能 
 * 从务器无法连接 主服务器可切换读功 
 * by lmt 
 * */ 
class DbConnectionMan extends CDbConnection { 
    public $timeout = 10; //连接超时时间 
    public $markDeadSeconds = 600; //如果从数据库连接失败 600秒内不再连接  
    //用 cache 作为缓存全局标记 
    public $cacheID = &#39;cache&#39;; 
 
    /** 
     * @var array $slaves.Slave database connection(Read) config array. 
     * 配置符合 CDbConnection. 
     * @example 
     * &#39;components&#39;=>array( 
     *   &#39;db&#39;=>array( 
     *    &#39;connectionString&#39;=>&#39;mysql://<master>&#39;, 
     *    &#39;slaves&#39;=>array( 
     *     array(&#39;connectionString&#39;=>&#39;mysql://<slave01>&#39;), 
     *     array(&#39;connectionString&#39;=>&#39;mysql://<slave02>&#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 configuration: components array, the code is as follows:

The code is as follows:

'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_', //表前缀 
            ), 
 
        ), 
),