Load Balancing MySQL w/ MySQL Proxy
We have the technology
Install MySQL Proxy on the load balancer box(es).
sudo apt-get install mysql-proxy --yes
Configure and run MySQL Proxy
You'll also need to open the MySQL port on the load balancer boxes. Note that MySQL Proxy listens on port 4040, not the regular MySQL port 3306. My test box here is 168.144.251.16 - it should be whichever IP address outside the database cluster that you're going to connect from to test the proxy works.
Run the proxy, telling it the address of the real database servers, then try to connect from the test box.
vi /etc/default/mysql-proxy
ENABLED="true"
OPTIONS="--proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua
--proxy-address=168.144.251.106:3306
--proxy-backend-addresses=168.144.251.160:3306
--proxy-read-only-backend-addresses=168.144.251.161:3306"
# On the test box
mysql -u some_user -p'some_other_password' -h lb.domainname.com
mysql> \q
You may be told that your load balancer hosts don't have access to the MySQL server. If this happens, login to the MySQL hosts, add a user at the hostname that failed, and try again.
ERROR 1130 (00000): Host 'lb.domainname.com' is not allowed to connect to this MySQL server
# On master.domainname.com and slave.domainname.com
mysql -u root -p
Enter password: [Enter your MySQL root password]
mysql> grant all on my_application.* to 'some_user'@'master.domainname.com'
identified by 'some_other_password';
mysql> grant all on my_application.* to 'some_user'@'slave.domainname.com'
identified by 'some_other_password';
mysql> \q
If you got MySQL prompts both times then both proxies are working.
Fire up mysql on the test box and connect to the floating IP addr. You should get the MySQL command prompt.
mysql -u some_user -p'some_other_password' -h hostname or ip my_application
Typing out exactly what is done to test this would take a long time and, largely, would be a waste of space. Here's a summary of the procedure. At all stages you should get a result from your query.
Run a query such as show processlist;
Shutdown master
Run the query again
Start master
Shutdown slave
Run the query again
Start slave
Shutdown load balancer/proxy
Run the query again
Shutdown master
Run the query again
Start master
Shutdown slave
Run the query again
Start slave
Start load balancer/proxy
Run the query again
If your query ran successfully each time then congratulations, you've now got a load balanced, highly available, MySQL instance.
Where now?
Being highly available and load balanced doesn't protect you from mistakes. Backup often, and check you can restore from your backups. You may be interested in building a MySQL binlog-only server to get point-in-time recovery.