ProxySQL is a great load balancer which however suffers from some shortcomings concerning the management of MySQL users. ProxySQL provides a firewall which, in my case, is not complete enough to properly manage users and secure their access. Indeed, this firewall does not accept subnets and keeps unauthorized connections in ProxySQL. We cannot then be sure of not suffering a DDOS attack on our ProxySQL instance. In this article, I will explain how I managed to overcome this problem.

Reminder of the principle of connection through ProxySQL

To understand what follows, you have to bear in mind how ProxySQL connects to MySQL. The user connects to Proxysql which then establishes the connection to MySQL. For this, ProxySQL maintains MySQL users in its internal database. The names of MySQL users, their passwords as well as the MySQL destination server are entered in the mysql_users table. At each connection request to a MySQL server, …

A loadbalancer is used in many MySQL setups. Loadbalancing can be done with an hardware loadbalancer or with software. Software solutions tend to be a fair bit cheaper.

Loadbalancing is often implemented as an extra layer: The application connects to the loadbalancer via the network and the loadbalancer connects to the database via the network. The loadbalancing can be implemented on the application machines to get rid of the extra layer, this is better for latency (each layer adds latency) and as each application server has it's own loadbalancer it can also remove bottlenecks. The drawback are that you'll lose a single point of control and with multiple loadbalancers the number of health check queries will probably increase. The two-layer setup is often used with Connector/J and haproxy.

There are simple and more intelligent loadbalancers. There are loadbalancers which can split read and write traffic for example. Of …