Mysql replication read / write splitting: Mysql proxy or in software?

MySQL & MariaDB Other MySQL® Questions
1

Hi there…

I am rolling out a web app from a single server to a cluster using mysql replication. Obviously, I need to allow for read / write splitting to send write queries to the server.

The way I see it there are two options:

  1. Use mysql proxy to do this.
  2. Handle it in software. Rewrite the DB abstraction layer to separate read queries from write queries. Connect in a lazy manner, i.e. only connect to the master when (if) a write arrives.

2 is not that hard due to the way the DB abstraction is handled, however, using mysql proxy would side step this coding (and therefore code maintenance) nicely.

Not having used mysql proxy, I done know how stable it is and if it would slow things down.

Would mysql proxy sidestep this problem nicely, or would it introduce ‘another component to go wrong’ in my system. Does it introduce performance lags / increase resources used?

2

We have used MySQL-proxy and it is stable enough.