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.

Compiling PHP with Oracle support on CentOS 5.2

Getting PHP and oracle to play nice is a massive pain, so this post is more a log for myself next time I have to do it more than anything else!

The procedure requires you to compile PHP from source, so if thats not your thing or your easily scared don’t bother continuing!

Firstly an enormous yum install command to get everything we need in one go:

# yum install openssh-server httpd httpd-devel apxs libx openssl-devel curl-devel libpng-devel\
 oci8-devel libxml libxml2 libxml2-devel libjpeg libXpm libXpm-devel libpng libxslt libxslt-devel\
 freetype freetype-devel

Go and grab the instant client from the oracle website and install it with RPM:

# rpm -i oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
# rpm -i oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm

And then get the PHP source (substitute for whatever the latest version is at your compile time obviously):

# wget http://uk.php.net/get/php-5.2.8.tar.gz/from/this/mirror
# tar -xzf php-5.2.8.tar.gz
# cd php-5.2.8

And finally configure it all together:

# ./configure  --with-apxs2=/usr/sbin/apxs --with-zlib --without-gdbm --enable-force-cgi-redirect\
 --enable-ftp --enable-xml --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin\
 --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib\
 --libexecdir=/usr/libexec --localstatedir=/var --mandir=/usr/share/man --infodir=/usr/share/info\
 --with-config-file-path=/etc --with-config-file-scan-dir=/etc/php.d --with-pear --enable-pdo\
 --with-oci8=instantclient,/usr/lib/oracle/11.1/client/lib --with-pdo-oci=instantclient,/usr,11.1\
 --with-xsl --with-curl --with-openssl --enable-soap --enable-sockets --enable-sigchild --enable-xdebug\
 --with-jpeg-dir --with-xpm-dir --with-png-dir --with-freetype-dir --with-ttf --enable-gd-native-ttf --with-gd

And with a bit of luck you should be up and running. Obviously now you have to do battle with Oracle itself which is a minefield of confusion!

How to quickly setup a load balanced, high availability, Apache cluster

I wanted to find a simple to maintain and expand soultion for load balancing a web cluster with high availability. I have found my solution in HAProxy.

Scenario

Bluhalo Labs HAProxy Test Rig

This demo scenario is in the following enviroment:

• Network Configuration
  • Network: 192.168.11.0
  • Subnet Mask: 255.255.255.0
  • Gateway: 192.168.11.254 (Although this is irrelavant)
• 1 x Shared IP for the Load Balancers
  • 192.168.11.40
• 2 x Load Balancers
  • BHLabs1 – 192.168.11.30
  • BHLabs6 – 192.168.11.39
• 4 x Web Servers
  • BHLabs2 – 192.168.11.35
  • BHLabs3 – 192.168.11.36
  • BHLabs4 – 192.168.11.37
  • BHLabs5 – 192.168.11.38

At the start of this setup all machines are running Ubuntu 8.04 Server from a standard install with openssh-server installed and the root password set. All setup commands are run as root or with sudo.

Make working via PuTTY and Vim a bit nicer

If you’re used to working in a nice IDE or some kind of GUI when programming, this may be of interest to you.

The common complaint I hear about when people have to work from the command line such as on remote servers is that there is no nice editor. To me this sounds ludicrous just because I’m used to using Vi all day, but I can see how it would be a problem to those used to a prettier interface

Operating System Usage Over the Next 4 Years

Operating System Usage Prediction

These figures are extrapolated from W3Schools.com. I have predicted data for the next 4 years with a linear scale based on the last 5 years. The vertical axis has a logarithmic scale so the OSX and Linux data is meaningful. The graph shows a very slight decline in Windows usage and a notable increase in Apple OSX and Linux usage.

Obviously there are plenty of unforseen changes that could have an effect on this graph. I see the move towards OSX from Windows in the mainstream as inevitable, and I would imagine the change is more likely to be a sudden surge over a period of months rather than a smooth decline of Windows usage over a number of years.

It is interesting to compare the initial Vista predictions made by Steven York just before Vista’s release with the real stats in hindsight. The obviously low take up of Vista leaves most users on Windows XP, which when you compare to the look and feel of modern environments (for example: Websites, Mobile Phones, Latest Apple OS) it’s very clunky and unintuitive.

Ubuntu and Active Directory in 10 Minutes

This aims to be a short and simple step-by-step guide on how to get your blank box running Ubuntu server, joined to Active Directory and login to it with your AD credentials. Once installed, you can tailor this to your own environment easily. We use this on our local development servers to allow users to have their own home directories on each server with their own sites without having to juggle 6 different passwords. It also adds security in that once a user is locked out of AD, they are locked out of everywhere.

This article assumes you already have a functioning Windows 2003 server running AD.

4 Simple Apache tweaks to speed up delivery of your web site

Add these simple directives into your Apache configuration and you should notice a considerable increase in speed in the delivery of pages. As a working example, I performed these tweaks on http://www.shortlist.com/. Here are the before and after stats:

Shortlist Before Tweaks

Shortlist After Tweaks

The changes are:

• Homepage on an empty cache from 956.6k to 658.9k, both 133 requests.
• Homepage on a primed cache from 153.9k and 128 requests, to 25.9k and 8 requests.

Installing PHP 5.2.x or 5.3.x on RedHat ES5, CentOS 5, etc

I’ve had to follow this tutorial a few times myself now so decided I should share it with the world.

A few of our applications which make use of SOAP get a Segmentation Fault if run with PHP 5.1.x or lower. We believe this is due to a bug in PHP but can’t be sure, regardless it works fine in PHP 5.2.4 and above.

Problem is, RedHat ES5 does not have support at this time for anything higher than 5.1.6, and we didn’t want to break RPM dependancys etc by installing from source.

To install PHP 5.2.5 (Highest in repository at this time) you can make use of a RPM repository maintained by Remi. He has a repository for each distro, but to save you translating for the ES5 one I’ll give you the commands here. Run the following to get up and running:

wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm
rpm -Uvh remi-release-5*.rpm epel-release-5*.rpm

You now have the Remi repository on your system, however it is disabled by default. Obviously you don’t want all of your packages been effected by this repository, however to enable it for a specific package, run the following:

yum --enablerepo=remi update php

You should now have the latest PHP5 installed:

# php -v

PHP 5.2.5 (cli) (built: Nov 10 2007 10:52:30)

Copyright (c) 1997-2007 The PHP Group

Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies

---

Update 12th Aug ‘08:
There is now an English page on the Remi site on how to configure repositories for all sorts of distros:

http://blog.famillecollet.com/pages/Config-en

---

Update 13th June ‘09:
The download link for the ES5 package has changed, post updated above.:

http://blog.famillecollet.com/pages/Config-en

---

Update 9th August ‘09:

It would seem this repo has now been upgraded to PHP 5.3.0.

Repairing permissions and ownership on the entire Linux file system

A problem I experianced recently was one of my users accidently ran the following command, as root, on a live server:

chown www:www /.* -R

Note the “/.” rather than the “./”. This has the rather unfortunate consequence of changing the ownership of the entire drive to www:www (our apache user).

After a bit of panicking (this was a live server after all!) I found RPM has a very underdocumented feature called “fixperms”. If you look for the man page for this, all you get is:

rpm {–setperms|–setugids} PACKAGE_NAME …

With some quick testing on a local box I found what this command does is read through an RPM and fix the permissions on the files it would have generated. Using the command rpm -qa you can list all installed packages. A little bit of creativity later and…

for i in $(rpm -qa);

do

rpm –setperms $i;

done

for i in $(rpm -qa);

do

rpm –setugids $i;

done

These 2 commands will go through the list of installed packages and reperform the task originaly done at instalation of setting the owner, group and permissions on each file.Unfortunatly there is no equivelant command for dpkg based systems such as Debian or Ubuntu, our OS of choice at Bluhalo.