Introduction to Yii multi-database master/slave read/write splitting instances

This article mainly introduces how Yii implements master-slave read/write splitting for multiple databases. it is a very practical technique to implement the separation of master-slave read/write for multiple databases by extending the Yii database class, for more information about how to implement master-slave read/write splitting for multiple databases in Yii, you can use the extension of Yii database to implement master-slave read/write splitting for multiple databases, it is a very practical technique. For more information, see

This document describes how to implement master-slave read/write splitting for multiple databases in Yii. Share it with you for your reference. The specific analysis is as follows:

Yii Framework database multi-database, master-slave, read/write splitting implementation, function description:

1. Master/Slave Database read/write splitting: Write slave database (multiple): Read

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

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

4. if the slave database fails to be connected, you can set it to no longer connect within N seconds.

The code is as follows:

The code is as follows:

 Array (* 'DB' => array (* 'ononstring' => 'MySQL ://
 
  
', * 'Slafs' => array (* array ('ononstring' => 'MySQL ://
  
   
'), * Array ('ononstring' => 'MySQL ://
   
    
'), *) **/Public $ slaves = array (); /*** if the slave database status is false, only the master database * @ var bool $ enableSlave **/public $ enableSlave = true; /*** @ var slavesWrite in case of emergency, the master database cannot connect to the slave server (read/write ). */public $ slavesWrite = false;/*** @ var masterRead the master database cannot be connected to the slave server (read/write) in an emergency ). */public $ masterRead = false;/*** @ var _ slave */private $ _ slave;/*** @ var _ disableWrite slave server (read-only ). */private $ _ disableWrite = true; /***** Override the createCommand method, 1. enable Slave Database 2. the slave database exists. 3. not in a transaction currently 4. read data from the database * @ 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 the connection resource from the server * @ return CDbConnection **/public function getSlave () {if (! Isset ($ this-> _ slave) {shuffle ($ this-> slaves); foreach ($ this-> slaves as $ slaveConfig) {if ($ this-> _ isDeadServer ($ slaveConfig ['ononstring']) {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 (Exc Eption $ e) {$ this-> _ markDeadServer ($ slaveConfig ['ononstring']); Yii: log ("Slave database connection failed! NtConnection string: {$ slaveConfig ['ononstring']} ", '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 ();}}} /*** check the SQL statement for read operations ** keyword: 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', 'pragm'), '^ O ^', $ SQL); // ^ O ^, magic smile return strpos ($ SQL, '^ O ^') = 0;}/*** checks whether the slave server is marked as failed. */private function _ isDeadServer ($ c) {$ cache = Yii: app ()-> {$ this-> cacheID }; if ($ cache & $ cache-> get ('adserver ::'. $ 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 configuration: in the components array, the code is as follows:

The code is as follows:

'DB' => array ('class' => 'application. extensions. dbConnectionMan ', // extension path 'ononstring' => 'MySQL: host = 192.168.1.128; dbname = db_xcpt', // write 'emulateprepare' to the primary database => true, 'username' => 'root', 'password' => 'root', 'charset' => 'utf8', 'tableprefix' => 'xcpt _', // The Table prefix 'enableslav' => true, // enable 'urgencywrite' from the database => true, // in emergencies, the master database cannot connect to enable the slave database write function 'masterread' => true, // in an emergency, the slave database cannot be connected. enable the master database read function 'slafs' => array (// slave database array (// slave1 'ononstring' => 'MySQL: host = localhost; dbname = db_xcpt ', 'default' repare' => true, 'username' => 'root', 'password' => 'root ', 'charset' => 'utf8', 'tableprefix' => 'xcpt _ ', // table prefix), array (// slave2 'ononstring' => 'MySQL: host = localhost; dbname = db_xcpt ', 'default' repare' => true, 'username' => 'root', 'password' => 'root ', 'charset' => 'utf8', 'tableprefix' => 'xcpt _ ', // table prefix ),),),