Yii implements the method of separating master-slave reading and writing in multiple databases, yii database master-slave reading and writing

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 the separation of reading and writing in the master-slave database. Master database: write. Slave database (can be 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

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

Copy code The code is as follows:

/**
* Main database writes and reads from the database (can be multiple)
* Realize the separation of reading and writing in the master-slave database. The master server cannot be connected and the slave server can switch the write function
* The slave server cannot connect. The master server can switch the reading function
* by lmt
**/
class DbConnectionMan extends CDbConnection {
Public $timeout = 10; //Connection timeout
Public $markDeadSeconds = 600; //If the connection from the database fails, no more connections will be made within 600 seconds
//Use cache as cache global tag
Public $cacheID = 'cache';

/**
     * @var array $slaves.Slave database connection(Read) config array.
     * 配置符合 CDbConnection.
     * @example
     * 'components'=>array(
     *   'db'=>array(
     *    'connectionString'=>'mysql://',
     *    'slaves'=>array(
     *     array('connectionString'=>'mysql://'),
     *     array('connectionString'=>'mysql://'),
     *    )
     *   )
     * )
     **/
Public $slaves = array();
/**
* *
* If the slave database status is false, only the master database will be used
* @var bool $enableSlave
**/
Public $enableSlave = true;

/**
* @var slavesWrite Emergency master database cannot connect, switch slave server (read and write).
*/
Public $slavesWrite = false;

/**
* @var masterRead In case of emergency, the slave master database cannot be connected, switch to the slave server (read and write).
*/
Public $masterRead = false;

/**
     * @var _slave
    */
private $_slave;

/**
* @var _disableWrite slave (read-only).
*/
Private $_disableWrite = true;

/**
* *
* Rewrite the createCommand method, 1. Open the slave library 2. Exist from the slave library 3. Currently not in a transaction 4. Read data from the library
* @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);
          }
}

/**
* Get connection resources from the server
* @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();
            }
        }
    }

    /**
* Detect read operation sql statement
* *
* Keywords: SELECT,DECRIBE,SHOW...
* Write operations: 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;
    }

    /**
* Failed to detect whether the slave server is marked.
*/
    private function _isDeadServer($c) {
        $cache = Yii::app()->{$this->cacheID};
        if ($cache && $cache->get('DeadServer::' . $c) == 1) {
            return true;
        }
        return false;
    }

    /**
* Mark failed 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',//Extension path
          'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//Main database write
         'emulatePrepare' => true,
         'username' => 'root',
'password' => 'root',
         'charset' => 'utf8',
          'tablePrefix' => 'xcpt_', //Table prefix
          'enableSlave'=>true,//Enable from database
'urgencyWrite'=>true,//In emergencies, the main database cannot be connected, enable the slave database write function
'masterRead'=>true,//Emergency, the slave database cannot be connected, enable the master database read function
         'slaves'=>array(//From database
array( //slave1
               'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                  'emulatePrepare' => true,
'username'=>'root',
                   'password'=>'root',
                'charset' => 'utf8',
                'tablePrefix' => 'xcpt_', //Table prefix
),
array( //slave2
               'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                  'emulatePrepare' => true,
'Username' = & gt; 'root',
                   'password'=>'root',
                'charset' => 'utf8',
                 'tablePrefix' => 'xcpt_', //Table prefix
),

),
),

I hope this article will be helpful to everyone’s PHP programming based on the Yii framework.