MySQL proxy redirect Read/Write

Question

We have a system where we have a Master / Multiple Slaves .

Currently everything happens on the Master and the slaves are just here for backup .

We use Codeigniter as a development platform .

Now we decided to user the slaves for the Reads and the Master for the Write queries .

I have been told that this is not doable without modifying the source code because proxy can't know the type of the query .

Any idea how to proceed with this without causing too much damages for a perfectly working system ...

score 5 · Accepted Answer · answered Feb 20 '15 at 12:22

5

We will use this : http://dev.mysql.com/downloads/mysql-proxy/

It does exactly what we want :

enter image description here

More info here :

http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting/

http://www.infoq.com/news/2007/10/mysqlproxyrwsplitting

http://archive.oreilly.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html

answered Feb 20 '15 at 12:22

Tarek

3,810
3
36
62

I am considering to use mysql-proxy, cant you tell me is there any change in CI? I just want to keep using $this->db->select() and $this->db>insert() and let mysql-proxy to handle read and writes to master/slaves – consigliere Aug 08 '15 at 20:49
2

mysql-proxy is in Alpha phase only use in developement – Navrattan Yadav Dec 09 '15 at 05:45
Not recommended for Production use – Cyberience Oct 30 '20 at 04:37

score 1 · Answer 2 · answered Feb 12 '15 at 10:04

1

something i was also looking, few month back i did something like this but i added 3 web server with master slave mysql servers, first web server enabled with mod_proxy to redirect request to read and write server all request will come to this server, if post,put or delete request come to server it will go to write server, all get or normal request will go to read server

here you can find mod_proxy setting which i used

http://pastebin.com/a30BRHFq

here you can read about load balancing http://www.rackspace.com/knowledge_center/article/simple-load-balancing-with-apache

still looking for better solution with less hardware involved

answered Feb 12 '15 at 10:04

umefarooq

4,540
1
29
38

All our queries are ajax based and then processed by php to run the mysql query (Select / Insert / Delete / Update..) – Tarek Feb 12 '15 at 10:08
no problem ajax request also either get or post, if get it will go to readonly server for select queries, if request post, put, delete it will go to write only server where you will perform insert, update and delete – umefarooq Feb 12 '15 at 10:14

umefarooq · Answer 3 · 2015-02-12T11:30:35.887

figure out another solution through CI, create two database connections in database.php file keep save mysql server as default database connection and other connection for write only server

you can use this base model extend

https://github.com/jamierumbelow/codeigniter-base-model

you need to extend your models with this model and need to extend you model with this, it has functionality for callbacks before and after insert,update, delete and get queries, only you need to add one custom method or callback change_db_group

//this method in MY_Model    
function change_db_group{
   $this->_database = $this->load->database('writedb', TRUE)
}

no your example model

class Example_Model extends MY_Model{

  protected $_table = 'example_table';
  protected $before_create = array('change_db_group');
  protected $before_update = array('change_db_group');
  protected $before_delete = array('change_db_group');

}

you database connection will be changed before executing insert,update or delete queries

MySQL proxy redirect Read/Write

3 Answers3