CRUD with MySQL Master-Slave Architecture

John Foley asked:

I have setup mysql master-slave replication with 1 master and 3 slaves. Now that I have finished setting up the architecture I am curious as to the best way to make my reads. I realize all writes, updates, and deletes will be directed to the master.

Do I need to spin up another server running something like HAproxy to load balance the reads to all the slaves? Is it possible for the master to load balance reads? It would be preferred for all operations to be performed on the same endpoint so I don’t need to modify logic in the web layer to read from one IP ( stand alone load balancer ) and write, update, and delete to a second IP ( master ).

Is there a standard way to handle CRUD on a master-slave architecture?

My answer:

You can use a proxy designed for MySQL, such as MySQL Proxy or MaxScale, to not only load balance your read queries, but also to ensure that writes are directed to the master. From the point of view of the clients, there is only one MySQL connection, which is useful for clients which can’t make separate connections for reads and writes.

View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.