mysql主从同步实践 YII

1、两台服务器互联master、slave

2、master配置：

  server-id       = 1    master端ID号

log-bin=/data/logbin/mysql-bin    日志路径及文件名

#binlog-do-db = cacti            同步cacti，此处关闭的话，就是除不允许的，其它的库均同步。

binlog-ignore-db = mysql        不同步mysql库，以下同上

mysql>show master status;

3、slave配置：

  server-id = 2      slave的ID号，此处一定要大于master端。

保存退出。

 

/usr/local/mysql/bin/mysqladmin -uroot -p shutdown

tar xvzf /data/mysql/cacti.tgz /data/mysql/cacti

chown -R mysql.mysql /data/mysql/cacti

/usr/local/mysql/bin/mysql -uroot -p

mysql>stop slave;

mysql>change master to

     >master_host='192.168.2.67',

     >master_user='rsync',                            master端创建的用于主从同步的账户和密码

     >master_password='123456',

     >master_port='3306',                             master端设置的client端使用的端口号。

     >master_log_file='mysql-bin.000047',             master端记录的file值

     >master_log_pos=391592414;                       master端记录的position值

mysql>start slave;

mysql>show slave status \G

==========================================================

<?php


/**
 * DbConnectionMan(Database Connection Manager) class is a manager of database connections.
 * for the purpose of database read/write splitting.
 * It override the createCommand method,
 * detect the sql statement to decide which connection will be used.
 * Default it use the master connection.
 * */
class DbConnectionMan extends CDbConnection {


/**
* @var array $slaves.Slave database connection(Read) config array.
* The array value's format is the same as CDbConnection.
* @example 
* 'components'=>array(
* 'db'=>array(
* 'connectionString'=>'mysql://<master>',
* 'slaves'=>array(
* array('connectionString'=>'mysql://<slave01>'),
* array('connectionString'=>'mysql://<slave02>'),
* )
* )
* )
* */
public $slaves=array();






/**
* Whether enable the slave database connection.
* Defaut is true.Set this property to false for the purpose of only use the master database.
* @var bool $enableSlave 
* */
public $enableSlave=true;


/**
* @override
* @var bool $autoConnect Whether connect while init
* */
public $autoConnect=false;




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


/**
* Creates a CDbCommand object for excuting sql statement.
* It will detect the sql statement's behavior.
* While the sql is a simple read operation.
* It will use a slave database connection to contruct a CDbCommand object.
* Default it use current connection(master database).
* 
* @override 
* @param string $sql
* @return CDbCommand
* */
public function createCommand($sql) {
if ($this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($sql)) {
return $this->getSlave()->createCommand($sql);
} else {
return parent::createCommand($sql);
}
}




/**
* Construct a slave connection CDbConnection for read operation.
* @return CDbConnection
* */
public function getSlave() {
if (!isset($this->_slave)) {
foreach ($this->slaves as $slaveConfig) {
if (!isset($slaveConfig['class']))
$slaveConfig['class']='CDbConnection';
try {
if ($slave=Yii::createComponent($slaveConfig)) {
Yii::app()->setComponent('dbslave',$slave);
$this->_slave=$slave;
break;
}
} catch (Exception $e) {
                                                                  echo '<pre>';     var_dump($e);echo '</pre>';}
}
if (!$this->_slave) {
$this->_slave=clone $this;
$this->_slave->enableSlave=false;
}
}
return $this->_slave;
}


/**
* Detect whether the sql statement is just a simple read operation.
* Read Operation means this sql will not change any thing ang aspect of the database.
* Such as SELECT,DECRIBE,SHOW etc.
* On the other hand:UPDATE,INSERT,DELETE is write operation.
* */
public function isReadOperation($sql) {
return preg_match('/^\s*(SELECT|SHOW|DESC|PRAGMA)\s+/i',$sql);
}


}

==================================================

'db'=>array(
//'connectionString' => 'mysql:host=localhost;dbname=yiitest',
                        'class' => 'DbConnectionMan',
                        'connectionString' => 'mysql:host=localhost;dbname=ms_test',
'emulatePrepare' => true,
                        //'tablePrefix' => 'ms_',
'username' => 'root',
'password' => '123456',
'charset' => 'utf8',
//                        'enableProfiling' => true,
//                        'enableParamLogging' => true,
                        'slaves' => array(                            
                            array('connectionString' => 'mysql:host=10.237.94.13;dbname=ms_test',
//                                'class' => 'CDbConnection',
                            'username' => 'yanghuolong',
                            'password' => '123456',
                            'enableProfiling' => true,
                            'enableParamLogging' => true,
                            'charset' => 'utf8'),
                        ),
),