Blogg

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!

Pär

CodeIgniter