Mysql master-slave synchronization practice YII

1. Two servers connected to the master, slave2, master configuration: server-id1master end ID log-bindatalogbinmysql-bin log Path and file name # binlog-do-dbcacti synchronization cacti, if this is disabled, that is, except for not allowed, all other databases are synchronized. Binlog-ignore-dbmysql does not synchronize mysql

1. master and slave are interconnected between two servers. 2. master Configuration: server-id = 1 master id log-bin =/data/logbin/mysql-bin log Path and file name # binlog-do-db = cacti synchronize cacti. If this function is disabled, that is, except for not allowed, all other databases are synchronized. Binlog-ignore-db = mysql does not synchronize mysql

1. Two servers are connected to the master and slave

2. master Configuration:

Server-id = 1 master id

Log-bin =/data/logbin/mysql-bin log Path and file name

# Binlog-do-db = cacti: Synchronize cacti. If this function is disabled, all other databases are synchronized except not allowed.

Binlog-ignore-db = mysql does not synchronize mysql databases. The following is the same

Mysql> show master status;

3. slave Configuration:

Server-id = 2 slave id, which must be greater than the master.

Save and exit.

/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

> Master_host = '1970. 168.2.67 ',

> Master_user = 'rsync': account and password created on the master node for master-slave Synchronization

> Master_password = '000000 ',

> Master_port = '123' indicates the port number used by the master client.

> Master_log_file = 'mysql-bin.000047 ', the file value recorded by the master.

> Master_log_pos = 391592414; position value recorded on the master end

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 :// ',
* 'Slafs' => 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 +/I', $ SQL );
}


}

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

'Db' => array (
// 'Connectionstring' => 'mysql: host = localhost; dbname = yiitest ',
'Class' => 'dbconnectionman ',
'Connectionstring' => 'mysql: host = localhost; dbname = ms_test ',
'Default' => true,
// 'Tableprefix' => 'Ms _',
'Username' => 'root ',
'Password' => '123 ',
'Charset' => 'utf8 ',
// 'Enablesprofiling' => true,
// 'Enablesparamlogging' => true,
'Slafs' => array (
Array ('connectionstring' => 'mysql: host = 10.237.94.13; dbname = ms_test ',
// 'Class' => 'cdbconnection ',
'Username' => 'yanghuolong ',
'Password' => '123 ',
'Enablesprofiling' => true,
'Enablesparamlogging' => true,
'Charset' => 'utf8 '),
),
),