Simple MySQL replication cluster with load balancer on the slaves

Scenario

Background

This post is a follow on from my previous post on how to setup a load balanced high availability Apache cluster and uses the same network setup and heartbeat setup. For the purposes of testing and to save on resource I am using the web servers from the previous guide as the MySQL servers in this guide. Obviously in a real world setup this would be doing it wrong

End Goal

You will be able to connect to 1 IP address and port with MySQL client and be routed to 1 of many MySQL servers. The machine doing the routing will be in a high availability setup with another machine.

Server Roles

bhlabs1/6 will load balance mysql queries through mysql-proxy
bhlabs2 will be  the master replication server
bhlabs3-5 will be mysql slaves

I’m separating this guide into 3 sections:

• MySQL Replication Cluster
• mysql-proxy Setup
• Usage with heartbeat