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”
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.
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?
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.
true. fixing this.