Using Different Database Configurations for Reading/Writing Data

When dealing with applications using large database plus numerous scripts on the background that constantly reads/writes data to the same database server, one of the problems we usually encounter is that tables get locked up during one of the processes. This causes our application to slow down. In this case, it is often necessary to separate database calls. Set up a slave DB with a user with read-only privileges, and which is constantly being synched with the master DB.

To make this work in cakePHP, you need to set-up two db configs. Define ur /config/database.php as such:

class DATABASE_CONFIG {
var $default = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'port' => '' ,
'login' => '*****',
'password' => '****',
'database' => '****',
'schema' => '',
'prefix' => **_',
'encoding' => ''
);
var $master = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'port' => '' ,
'login' => '****',
'password' => '****',
'database' => '*****',
'schema' => '',
'prefix' => '**_',
'encoding' => ''
);

}

The default configuration always uses the Slave DB. For security reasons, make sure that the login is set to a user with a read-only privilege.

Now create an app_model.php file. Create the following functions that shall override the parent model class:


function save($data = null, $validate = true, $fieldList = array())
{
$this->setDataSource('master');
$isSave = parent::save($data, $validate, $fieldList);
$this->setDataSource('default');
return $isSave;
}


function saveAll($data = null, $options = array())
{
$this->setDataSource('master');
$isSave = parent::save($data, $options);
$this->setDataSource('default');

return $isSave;
}


function updateAll($fields, $conditions=true)
{
$this->setDataSource('master');
$isUpdated = parent::updateAll($fields, $conditions);
$this->setDataSource('default');

return $isUpdated;
}


function delete($id = null, $cascade = true)
{
$this->setDataSource('master');
$isDeleted = parent::delete($id, $cascade);
$this->setDataSource('default');

return $isDeleted;
}


function deleteAll($conditions, $cascade = true, $callbacks = false)
{
$this->setDataSource('master');
$isDeleted = parent::deleteAll($conditions, $cascade, $callbacks);
$this->setDataSource('default');

return $isDeleted;
}

For a more complicated db setup, wherein you have multiple slave databases to access data from you could refer to my actual source here.

Advertisement