Load Balancing and MySQL Master and Slaves
If you are currently using MySql master/slave replication for load balancing and wish to transport to cakePHP, it really couldn't be easier.
2 simple steps to master/slave replication in cake..
Firstly in app/config/database.php write the following:-
Download code
Then, in app/app_model.php, create 4 new methods:
Download code
It really is as simple as that.
However, there are some instance where you may want to use a query in your controller where you update/insert information. In this case, use the following:
Download code
I use this where I want information to be updated, but don't want cake to flush the cache. But then again, that may be like crossing the streams in Ghostbusters, so don't listen to me..
With thanks to Nate and gwoo
http://www.howtoforge.com/mysql_master_master_replication
Firstly in app/config/database.php write the following:-
Download code
class DATABASE_CONFIG {
var $default = array(
'driver' => 'mysql',
'host' => 'slave.host.ip',
'login' => '....',
'password' => '.....',
'database' => 'my_db'
);
var $master = array(
'driver' => 'mysql',
'host' => 'master.host.ip',
'login' => '....',
'password' => '.....',
'database' => 'my_db'
);
}
Then, in app/app_model.php, create 4 new methods:
Download code
function beforeSave() {
$this->useDbConfig = 'master';
return true;
}
function afterSave() {
$this->useDbConfig = 'default';
return true;
}
function beforeDelete() {
$this->useDbConfig = 'master';
return true;
}
function afterDelete() {
$this->useDbConfig = 'default';
return true;
}
It really is as simple as that.
However, there are some instance where you may want to use a query in your controller where you update/insert information. In this case, use the following:
Download code
$this->ModelName->setDataSource('master');
$this->ModelName->query("UPDATE post_views SET views=views+1 WHERE id=1234");
$this->ModelName->setDataSource('default');
I use this where I want information to be updated, but don't want cake to flush the cache. But then again, that may be like crossing the streams in Ghostbusters, so don't listen to me..
With thanks to Nate and gwoo
http://www.howtoforge.com/mysql_master_master_replication
Comments
Comment
1 One More Thing
Comment
2 Adding support for custom queries
Model Class:
<?php
class AppModel extends Model {
function query() {
$params = func_get_args();
if (!empty($params) && is_string($params[0])) {
$updates = array(
'CREATE', 'DELETE', 'DROP',
'INSERT', 'UPDATE'
);
if (preg_match('/^(' . implode('|', $updates) .
')/i', trim($params[0]))) {
$this->useDbConfig = 'master';
}
}
if (!empty($params)) {
$result =& call_user_func_array(array($this, 'parent::query'), $params);
}
if ($this->useDbConfig == 'master') {
$this->useDbConfig = 'default';
}
return $result;
}
}
?>
Comment
3 Awesome.
Bug
4 No longer works for 1.2
I have no fix for this yet.
Comment
5 1.2 MasterSlave
You can still use the setDataSource() method to attain the same results. The save method can be master/slaved with the following AppModel:
Model Class:
<?php
class AppModel extends Model {
// model stuffs
// override the save to switch db connections
function save($data = null, $validate = true, $fieldList = array()) {
$oldDb = $this->dbConfig;
$this->setDataSource('master');
$return = parent::save($data, $validate, $fieldList);
$this->dbConfig = $oldDb;
return $return;
}
}
?>