CodeIgniter/ActiveRecord setup to use master + slave db replication

This is how you can set up CodeIgniter to direct mysql queries to different read/write hosts in your db replicated environment, using a db_slave for your SELECT’s, and a db_master for the INSERT/UPDATE/DELETE queries.

File: application/config/database.php

Specify the different database hosts in the database config file:

< ?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

$active_group = "master";
$active_record = TRUE;

# db_master
$db['master']['hostname'] = "host1";
$db['master']['username'] = "username";
$db['master']['password'] = "password";
$db['master']['database'] = "exampledb";
$db['master']['dbdriver'] = "mysql";
$db['master']['dbprefix'] = "";
$db['master']['pconnect'] = FALSE;
$db['master']['db_debug'] = TRUE;
$db['master']['cache_on'] = FALSE;
$db['master']['cachedir'] = "";
$db['master']['char_set'] = "utf8";
$db['master']['dbcollat'] = "utf8_general_ci";

#db_slave
$db['slave']['hostname'] = "host2";
$db['slave']['username'] = "username";
$db['slave']['password'] = "password";
$db['slave']['database'] = "exampledb";
$db['slave']['dbdriver'] = "mysql";
$db['slave']['dbprefix'] = "";
$db['slave']['pconnect'] = FALSE;
$db['slave']['db_debug'] = TRUE;
$db['slave']['cache_on'] = FALSE;
$db['slave']['cachedir'] = "";
$db['slave']['char_set'] = "utf8";
$db['slave']['dbcollat'] = "utf8_general_ci";
...

File: application/core/My_Model.php

Add this into My_Model:

< ?php

class MY_Model extends CI_Model {
    function __construct(){
        parent::__construct();
	$this->db_master = $this->load->database('default', TRUE);
	$this->db_slave = $this->load->database('default', TRUE);
    }


}

File: application/models/example_model.php

Use the read/write queries in your models like this:

< ?php
class example_model extends MY_Model {

	function example_model()
	{
		parent::MY_Model();
	}

	# read query
	function getSomething()
	{
		$query = $this->db_slave->get('mytable'); // db_slave
		return $query->result();
	}

	# write query
	function insertSomething()
	{
		$this->db_master->insert('mytable', $_POST); // db_master
		return $this->db_master->insert_id();
	}

That´s it!


Comments

4 responses to “CodeIgniter/ActiveRecord setup to use master + slave db replication”

  1. Darren Smith Avatar
    Darren Smith

    Having 2 constructors in your MY_Model class is not a very good idea. Not only is it a fraction slower but it also breaks OOP paradigms and will not be future proof. The only reason you can do it that way is because PHP 5 attempts to be backwards compatible with PHP4, don’t expect future versions of PHP to be as forgiving.

    Stick with __construct as your constructor and put the statements inside MY_Model() into that constructor so you can use super constructors and inheritance as it is meant to be used in OOP. Remember PHP4 didn’t have a real OOP model.

  2. How to connect more than one slave DBs?
    $this->db_slave = $this->load->database(‘slave’, TRUE);
    Can load->database select different db config according to website balance?

  3. peder fjällström Avatar
    peder fjällström

    it is common to let mysql handle balancing of db reads by itself. if this is the case, just point your slave host to the read-cluster.

    should you need to handle this on application level (in ci), just add more slaves to config/db.php and core/my_controller.php.

  4. peder fjällström Avatar
    peder fjällström

    true. fixing this.