Xipat Flexible Solutions Blog Splitting Read / Write with Mysql Proxy

Last few month, I mentioned to scaling up the site by spliting read and write mySql to the server by using Load Balancer. The time was fast and I've been so busy to complete the series of optimization performing posts. Today I will introduce a cheaper solution for spliting read and write by software Mysql Proxy I've been reading a lot of negative articles about its, and complaining it doesn't work. However it's going to more stable so far and with few our customers. It works.So firstly, what's it? "The MySQL Proxy is an application that communicates over the network using the MySQL network protocol and provides communication between one or more MySQL servers and one or more MySQL clients."There are many features of Mysql Proxy described on http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-using.html such as fix spelling, rewrite the query, make changes in results... But I just use 2 common usage scenaros for MySQL Proxy 1. Load balancing acress Mysql Slaves2. Splitting reads and writes so the reads go to the slave DB servers and writes go to the master DB serverIn many Linux package managers the MySQL Proxy package can be found under the name mysql-proxy. In CentOS the package is available from the EPEL repository. EPEL provides many additional packages that are not available from the main CentOS repository. If you don't have the EPEL repository installed, in CentOS 6 you can install it with the command rpm -ivh http://ftp-stud.hs-esslingen.de/pub/epel/6/i386/epel-release-6-8.noarch.rpm. Once you've added the EPEL repository, you can install MySQL Proxy with the command yum install mysql-proxy, then make sure it starts and stops automatically along with the system by running the command chkconfig mysql-proxy on. To following the configuration, I followed this posts http://www.openlogic.com/wazi/bid/259864/Simple-database-load-balancing-with-MySQL-Proxy This solution works like a charm for the heavily websites with most read queries and easily scale up the number of servers. It's also good case for doing backup site since you can do backup on the slave to avoid locking table issues of innodb.If you have any questions, feel free to contact us