CodeIgniter configure different IP for READ and WRITE MySQL data

Question

My company has been setting up a different server for read and write access of mysql DB. Previously, we use custom php script. However, I was recently given task to create tools by leveraging CI. I've done it on test server. Now, I'm confused, how to implement it on live server.

Answer 1

Might it be easier, rather than doing this by IP, do it using MySQL users.

You could have two different users, one with all the write privileges like INSERT and UPDATE, then another user with all the READ privileges like SELECT, or what ever you wish.

Then what ever code is running on your write web server uses the write user and vice-a-versa?

Answer 2

Setup multiple 'connection groups' for each server in your database.php config file, then in the Model:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

//read from DB1
$DB1->query();

//write to DB2
$DB2->insert();

See examples in: Connecting to Multiple Databases