readreplica implementation with codigniter

I'm developing PHP application with codigniter framework. And using amazon read replica setup. I need a way to handle all select query through read only DB and all update related query through master DB. Following is the current usage I use in my script.

database config file

$db['default']['hostname'] = 'master_db_host';
$db['default']['username'] = 'dbuser';
$db['default']['password'] = 'dbpassword';
$db['default']['database'] = 'db_name';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

model script

class replica_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }

    public function write($data)
    {
        $this->db->insert('dumy', $data);
        return $this->db->insert_id();
    }

    public function read()
    {
        $query = $this->db->get("dumy");
        return $query->result();
    }

}

Can any one help me to alter the script to access master and replica based on read and write?

---

We can handle multiple DB with codeigniter. So first we can see how the config file need to be set.

$active_group = 'default';
$active_record = TRUE;

//Master DB config values
$db['default']['hostname'] = 'master_db_host';
$db['default']['username'] = 'db_user';
$db['default']['password'] = 'db_pass';
$db['default']['database'] = 'db_name';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


//Replica DB config values
$db['read_replica']['hostname'] = 'replica_db_host';
$db['read_replica']['username'] = 'db_user';
$db['read_replica']['password'] = 'db_password';
$db['read_replica']['database'] = 'db_name';
$db['read_replica']['dbdriver'] = 'mysql';
$db['read_replica']['dbprefix'] = '';
$db['read_replica']['pconnect'] = FALSE;
$db['read_replica']['db_debug'] = TRUE;
$db['read_replica']['cache_on'] = FALSE;
$db['read_replica']['cachedir'] = '';
$db['read_replica']['char_set'] = 'utf8';
$db['read_replica']['dbcollat'] = 'utf8_general_ci';
$db['read_replica']['swap_pre'] = '';
$db['read_replica']['autoinit'] = TRUE;
$db['read_replica']['stricton'] = FALSE;

Parameter "$db['read_replica']['pconnect']" should be true on master db config and it should be false on replica config. Other wise when we enable replica db master db config also overwritten by replica config.

Then model usage will be like follows.

class Replica_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
        $this->db_replica = $this->load->database('read_replica',TRUE);
    }

    public function write($data)
    {
        $this->db->insert('dumy', $data);
        return $this->db->insert_id();
    }

    public function read()
    {
        $query = $this->db_replica->get("dumy");
        return $query->result();
    }

}

---

CI allows you to connect to multiple databases via groups.

This is directly from their site:

https://ellislab.com/codeigniter/user-guide/database/connecting.html

$DB1 = $this->load->database('read', TRUE);
$DB2 = $this->load->database('write', TRUE);

hen you connect this way, you will use your object name to issue commands rather than the syntax used throughout this guide. In other words, rather than issuing commands with:

$this->db->query();
$this->db->result();

etc...

You will instead use:

$DB1->query();
$DB1->result();

etc...

In you will have a multi-dimensional array for your db

 $db['read']['hostname'] = ...

 $db['write']['hostname'] = ...

So when you read, you would use $DB1, when you write, you'd use $DB2 ...

I haven't used this exact setup. But that's the gist of it.

链接地址: http://www.djcxy.com/p/83266.html

上一篇: MySQL在插入过程中在每个表上选择*

下一篇: 用codigniter执行readreplica实现