CakePHP : the rapid development php framework

The Bakery

Load Balancing and MySQL Master and Slaves

by eagerterrier
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:-


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:


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:


$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

Report

More on Tutorials

Advertising

Comments

  • olav posted on 03/29/10 08:21:52 AM
    ... because the approach depiced in http://bakery.cakephp.org/articles/view/load-balancing-and-mysql-master-and-slaves-2#comment-2752 switches the data source only for the primary model. If this model is associated with other models using belongsTo, hasMany etc., only the data source for the primary model is switched.

    This means that calls to bindModel() will no longer work!

    The only workaround for this is to switch the database between master and slaves way below the model layer by implementing a dedicated datasource that knows about the master/slave configuration. A sad and very low-level hack, but unavoidable if you want to continue to use bindModel().
  • mojobojo posted on 03/29/10 01:34:15 AM
    In 1.3.0 $dbConfig is actually $useDbConfig. This corrects the above save() override comment so it works.
  • olav posted on 01/19/09 06:00:47 AM
    ... needs to be wrapped, too. saveField(), on the other hand, internally calls save() so it is covered by the wrap of save().
  • mdarby posted on 06/30/07 10:11:08 PM
    Great post! Thanks!!
  • mariano posted on 06/09/07 02:35:15 AM
    Eventhough custom queries are a big no no, one can easily add support to avoid to manually set the proper database connection when using custom queries. Just add the following function to the AppModel class:

    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;
        }
    }
    ?>
  • eagerterrier posted on 05/24/07 12:31:47 PM
    I feel like Columbo, but I forgot to mention that this does not work in cakePHP versions before 1.1.15
    • olav posted on 04/23/08 09:59:04 AM
      The approach using beforeSave() no longer works for cake 1.2. Looking at CAKE/libs/model/model.php:save() the call to beforeSave() happens long after variable $db has been set using the old value of $this->useDbConfig .

      I have no fix for this yet.
      • stupergenius posted on 08/26/08 09:20:41 AM
        The approach using beforeSave() no longer works for cake 1.2.
        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;
            }
        }
        ?>
login to post a comment.