Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Master-Slave database in CodeIgniter
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
//Create a new config file
/*
|--------------------------------------------------------------------------
| Number of databases in use
|--------------------------------------------------------------------------
|
| Using a Master and Slave. The Slave doubles as a read-only database.
|
| If we find mid-campaign that the slave is too out of sync, we can
| shave this down to 1 database so everything reads/write from Master,
| putting more load on Master but keeping database integrity.
|
*/
$config['number_databases'] = 2;
<?php
$active_group = 'write';
$active_record = TRUE;
//Master DB IP: 11.11.111.111 [WRITE, READ primary]
//Slave DB IP: 22.22.222.222 [READ, secondary]
//Note master and slave have same username/database/password names
$db['write']['hostname'] = '11.11.111.111';
$db['write']['username'] = 'production_user';
$db['write']['password'] = 'production_pass';
$db['write']['database'] = 'production_db';
$db['write']['dbdriver'] = 'mysql';
$db['write']['dbprefix'] = '';
$db['write']['pconnect'] = TRUE;
$db['write']['db_debug'] = TRUE;
$db['write']['cache_on'] = FALSE;
$db['write']['cachedir'] = '';
$db['write']['char_set'] = 'utf8';
$db['write']['dbcollat'] = 'utf8_general_ci';
$db['write']['swap_pre'] = '';
$db['write']['autoinit'] = TRUE;
$db['write']['stricton'] = FALSE;
//50/50 odds of reading from either server to distribute load from READing database
$db['read']['hostname'] = (mt_rand(1, 100) > 50) ? '11.11.111.111' : '22.22.222.222';
$db['read']['username'] = 'production_user';
$db['read']['password'] = 'production_pass';
$db['read']['database'] = 'production_db';
$db['read']['dbdriver'] = 'mysql';
$db['read']['dbprefix'] = '';
$db['read']['pconnect'] = TRUE;
$db['read']['db_debug'] = TRUE;
$db['read']['cache_on'] = FALSE;
$db['read']['cachedir'] = '';
$db['read']['char_set'] = 'utf8';
$db['read']['dbcollat'] = 'utf8_general_ci';
$db['read']['swap_pre'] = '';
$db['read']['autoinit'] = TRUE;
$db['read']['stricton'] = FALSE;
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* Application logic needs to be aware if its reading or writing.
*
* Note that this needs to be done carefully. If you read and write at the same time, its
* better to do both with the WRITE database so you don't WRITE to master and then READ from
* slave before the data has time to sync between databases.
*/
class SomeModel extends CI_Model {
private $READ;
private $WRITE;
public function __construct(){
parent::__construct();
$numDB = ($this->config->item('number_databases')) ? $this->config->item('number_databases') : 1;
if($numDB > 1) {
$this->WRITE = $this->load->database('write', TRUE);
$this->READ = $this->load->database('read', TRUE);
} else {
$this->WRITE = $this->READ = $this->load->database('write', TRUE);
}
}//End __construct
public function verifyUser($username, $password) {
$q = $this->READ->select()
->from('users')
->where('username', $username)
->where('password', md5($password))
->limit(1)
->get();
if($q->num_rows() > 0) {
return $q->row();
}
return FALSE;
}
public function createUser($username, $password) {
$q = $this->WRITE->INSERT('users', array('user' => $username, 'password' => $password));
return $q;
}
}
@aaronwu

This comment has been minimized.

Copy link

@aaronwu aaronwu commented Dec 14, 2015

Not the best solution!!!

@sbrawner

This comment has been minimized.

Copy link

@sbrawner sbrawner commented Jun 22, 2016

Do you have a better one? Like to hear it, please.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment