So when it comes to routing your MySQL traffic several options exist.
- HAproxy
- MariaDB MaxScale
- NGINX
- MySQL Router
- ProxySQL
- You can even still get MySQL Proxy if you wanted it but it is EOF.
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
- https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
- https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/
- https://www.percona.com/blog/2017/01/25/proxysql-admin-configuration/
- https://www.percona.com/blog/2016/09/15/proxysql-percona-cluster-galera-integration/
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:
- https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/
- http://www.keepalived.org/doc/
- To be fair here is an example for keepalived and HAproxy as well
- https://andyleonard.com/2011/02/01/haproxy-and-keepalived-example-configuration/
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: https://launchpad.net/mysql-proxy
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.
- If you track hosts that connect to your database via your reporting or monitoring , realize those IPS or hostnames are now going to be the proxy server.
- What about all the users you already have in MySQL then? Can you migrate them to proxysql? Yes you can. It takes a few steps but it is do able. Here is an example of this:
- Make sure you understand the Multi layer configuration system. Save your info to disk!
- https://github.com/sysown/proxysql/wiki/Main-(runtime)#runtime-tables
- https://github.com/sysown/proxysql/wiki/Main-(runtime)#disk-database
- Can ProxySQL run on the MySQL Default port 3306
- Yes Edit the mysql-interfaces
- Keep in mind now your max_connections. If you have Max_connections in mysql set to 500, then that is your limit of course for standard users. With ProxySQL you can now spread users across the system and set a max per node. So to help ensure you do not hit 500 connections set the mysql-max_connections a little bit lower than MySQL value.
- Take advantage of the Monitor Module and STATS .. Know what is going on with your proxy and traffic.
- Take advantage of Query Caching if applicable for your application.
