<?php
/**
?* 主數(shù)據(jù)庫 寫 從數(shù)據(jù)庫(可多個(gè))讀
?* 實(shí)現(xiàn)主從數(shù)據(jù)庫 讀寫分離 主服務(wù)器無法連接 從服務(wù)器可切換寫功能
?* 從務(wù)器無法連接 主服務(wù)器可切換讀功
?* by lmt
?* */
class DbConnectionMan extends CDbConnection {
??? public $timeout = 10; //連接超時(shí)時(shí)間
??? public $markDeadSeconds = 600; //如果從數(shù)據(jù)庫連接失敗 600秒內(nèi)不再連接?
??? //用 cache 作為緩存全局標(biāo)記
??? 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();
??? /**
???? *?
???? * 從數(shù)據(jù)庫狀態(tài) false 則只用主數(shù)據(jù)庫
???? * @var bool $enableSlave
???? * */
??? public $enableSlave = true;
?
??? /**
???? * @var slavesWrite 緊急情況主數(shù)據(jù)庫無法連接 切換從服務(wù)器(讀寫).
???? */
??? public $slavesWrite = false;
?
??? /**
???? * @var masterRead 緊急情況從主數(shù)據(jù)庫無法連接 切換從住服務(wù)器(讀寫).
???? */
??? public $masterRead = false;
?
??? /**
???? * @var _slave
???? */
??? private $_slave;
?
??? /**
???? * @var _disableWrite 從服務(wù)器(只讀).
???? */
??? private $_disableWrite = true;
?
??? /**
???? *
???? * 重寫 createCommand 辦法,1.開啟從庫 2.存在從庫 3.當(dāng)前不處于一個(gè)事務(wù)中 4.從庫讀數(shù)據(jù)
???? * @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);
??????? }
??? }
?
??? /**
???? * 獲得從服務(wù)器連接資源
???? * @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();
??????????? }
??????? }
??? }
?
??? /**
???? * 檢測(cè)讀操作 sql 語句
???? *?
???? * 關(guān)鍵字： 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;
??? }
?
??? /**
???? * 檢測(cè)從服務(wù)器是否被標(biāo)記 失敗.
???? */
??? private function _isDeadServer($c) {
??????? $cache = Yii::app()->{$this->cacheID};
??????? if ($cache && $cache->get('DeadServer::' . $c) == 1) {
??????????? return true;
??????? }
??????? return false;
??? }
?
??? /**
???? * 標(biāo)記失敗的slaves.
???? */
??? private function _markDeadServer($c) {
??????? $cache = Yii::app()->{$this->cacheID};
??????? if ($cache) {
??????????? $cache->set('DeadServer::' . $c, 1, $this->markDeadSeconds);
??????? }
??? }
}

'db'=>array(
??????? 'class'=>'application.extensions.DbConnectionMan',//擴(kuò)展路徑
??????? 'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//主數(shù)據(jù)庫 寫
??????? 'emulatePrepare' => true,
??????? 'username' => 'root',
??????? 'password' => 'root',
??????? 'charset' => 'utf8',
??????? 'tablePrefix' => 'xcpt_', //表前綴
??????? 'enableSlave'=>true,//從數(shù)據(jù)庫啟用
?? 'urgencyWrite'=>true,//緊急情況 主數(shù)據(jù)庫無法連接 啟用從數(shù)據(jù)庫 寫功能
??? 'masterRead'=>true,//緊急情況 從數(shù)據(jù)庫無法連接 啟用主數(shù)據(jù)庫 讀功能
??????? 'slaves'=>array(//從數(shù)據(jù)庫
??????????? 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_', //表前綴
??????????? ),
?
??????? ),
),