[Ubuntu] Load Balancing MySQL with MySQL Proxy

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. 

 

Was this article helpful?
0 out of 0 found this helpful