scaling PHP application with MySQL replication without PHP code change?

I am planning to increase My site performance by adding another MySQL server beside the curr开发者_如何学Pythonent one because the current server is too busy.

Is it possible to scale PHP application with MySQL replication without PHP code change? I means all quires will be sent to the master and the master will distribute the load between itself and the slave.

Is there any easy way to send all write quires to the master and distribute read quires between the master and slave?

---

I think you need to put a load balancer / proxy between your db servers and clients (your code). Example solutions are:

• HAProxy: http://haproxy.1wt.eu/
• MySQL Proxy: https://launchpad.net/mysql-proxy

---

If you don't want to do the "load balancing" manually, you might want to look into MySQL Proxy.

---

I think you should also optimize your application's code (PHP) and then you should optimize your architecture.

First of all you can check your MYSQL queries. Mysql slow query log can help you. If you have a connection issues (MYSQL server has gone away or too many connections etc) you should manage your application's connection pooling mechanism.

And other steps and also your answer is (I think), you can set up MYSQL master-master replication. When you set replication clearly, you can put a load balancer (HAProxy) front of your replication. You have 2 nodes for mysql (server A and server B, both of them master server) You can configure HAProxy with server A is master and server B is backup server. Your all MYSQL operations comes server A via HAProxy and your data is automaticly sync with server B. When server A is down, HAProxy sends all queries server B automaticly.

Also you can configure HAProxy with server A is all insert queries and server B is for all read queries. All this cases your code should connect MYSQL via HAProxy