本文实例讲述了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程序设计有所帮助。
