Wednesday, May 23, 2018

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

So when it comes to routing your MySQL traffic several options exist.

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: 

Personally I like ProxySQL. Percona also has  few blogs on this as well
Percona also has ProxySQL version available 

I was thinking I would write up some examples but overall Percona has explained it all very well.  I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested. 

First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider. 
If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you. 

The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:
Yes it can be done with HAproxy but you have to instruct the application accordingly. 
This is handled in ProxySQL based on your query rules.

Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure?  

You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use KeepAlived.  This is VERY easy to set up and all of it is documented again well here: 
If you ever dealt with lua and mysql-proxy, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason:

Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy. 

So a few more things on ProxySQL. 

Happy Birthday MySQL 1995

Happy Birthday MySQL  ! Turned 23 today !