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.