Posted on No Comments on CentOS: Increase MySQL Uptime with MySQL Proxy

CentOS: Increase MySQL Uptime with MySQL Proxy

How can we achieve 100% MySQL uptime? For me, I would answer as follow:

  • Cluster all MySQL servers together
  • Load balance and failover between each of the cluster members
  • Make them run in redundant network line

I have tried to use Galera for active-active multi master replication in  previous post. This setup will surely help on scaling up the MySQL infrastructure that we have without headache. What I need the most is a loadbalancer and auto-failover in front of them. We can use a lot of reverse-proxy provider out there like HAProxy, Heartbeat and others but here I will use MySQL Proxy. Why? Because it require only 3 steps to make it run! I repeat, 3 steps!

Architecture that I will implement as follow:

Variables as below:

OS: CentOS 6 64bit
Server IP: 192.168.0.88

1. Download MySQL Proxy at http://dev.mysql.com/downloads/mysql-proxy/. I will download the “Red Hat & Oracle Linux 5 (x86, 64-bit), Compressed TAR Archive” version. I will run the MySQL proxy under /usr/local directory as below:

$ cd /usr/local
$ wget http://mysql.oss.eznetsols.org/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ tar -xzf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ mv mysql-proxy-0.8.2-linux-rhel5-x86-64bit mysql-proxy

2. Open MySQL port in iptables:

$ service iptables start
$ iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
$ service iptables save
$ service iptables restart

2. Lets start it! Run following command as root:

$ /usr/local/mysql-proxy/bin/mysql-proxy -P 0.0.0.0:3306 -b 192.168.0.91:3306 -b 192.168.0.92:3306 &

3. We just need to put above command (in step 2) to /etc/rc.local via text editor to make sure it started automatically after reboot.

To stop the mysql-proxy service, just kill the process by run following command:

$ kill -9 `pidof mysql-proxy`

Done! You can change your database information in your web server to the new IP, 192.168.0.88 (the mysql proxy server). Try to reboot MySQL DB1 and let web server make a SQL request and you will notice that your MySQL query is returnable by another server, MySQL DB2.