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

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&＃20540;

>master_log_pos=391592414; master端记录的position&＃20540;

mysql>start slave;

mysql>show slave status \G

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

/**
* 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://',
* 'slaves'=>array(
* array('connectionString'=>'mysql://'),
* array('connectionString'=>'mysql://'),
* )
* )
* )
* */
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 '

';     var_dump($e);echo '

';}
}
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&＃43;/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'),
),
),