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

Using cron to create website and MySQL backups that rotate nicely over a week

I wrote this for a PHP/MySQL site that needed to have it’s databases backed up hourly and the whole site including uploads backed up daily. They wanted all databases going back 24 hours every hour, and then all content going back 7 days every day.  This creates a directory structure that looks like the following:

In this scenario assume the following:

• /mnt/backup/servername is an NFS mounted location I am backing up to
• /var/www is the location where the website is stored

Firstly for the impatient, here is the 2 lines you need to put into crontab:

0 0 * * * mkdir -p /mnt/backup/servername/`date +\%A`;  mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/`date +\%A`/all-databases-daily.sql; tar -czf /mnt/backup/servername/`date +\%A`/www.tar.gz /var/www/
0 * * * * mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/all-databases-hourly-`date +\%H`.sql

For the less impatient I’ll explain how it works.