I have configured master slave on my system (MYSQL). Now, is there is any script or way available or possible to transfer all read request to SLAVE.
So, let me make my question here more clear.
I have a application written in JAVA-SPRING and use Bone-CP to connect to Mysql db.
So, now I have to implement Master-Slave where all write request goes to MASTER and all read request goes to SLAVE via Master or directly.
I don't want to make any changes in code, so I am looking for some solution which I could implement on MYSQL to transfer all read/Select request to Slave.
I have googled around but no solution or hint found.
There might be two possible solution for it.
1. All request first hit MASTER and then master transfer all request to SLAVE.
2. A layer between APPLICATION and MYSQL which decides where to transfer the request accordingly
But I have no idea how to implement it, So can anyone provide any link, hint or solution to my problem.
Best Answer
this is called Read/Write Splitting
MySQL itselft have no ablity to meed your demand,
but MySQL officially release a simple program called MySQL Proxy, which will help you to archive your goal and more. however it has lots of limitations, such as doesn't support procedure, etc. and are offically NOT recommended to use in production environment.
Many companies create their own middleware and some are open sourced, but as those middlewares are customized by their own scenario, it's actually not easy to use in other enviroment. so my advice is ,according to your business, write your own middleware.
hope that useful!
added 2014-6-26 7:31 GMT
you probably could consider some other high availability solution such as :
Galera Cluster
Multi-Master Replication Manager for MySQL(a.k.a MMM)
they will partially meet your request, which means write/read on master node, read on several slave nodes
note that if you use MySQL Replication and read from the slave side, you probably read data slower than master side particularly there are heaviy wirte on master side, because hundreds of thousands of threads are writen simultaneously, while only one slave I/O thread propagate data to slave node.