In a move to enhance ProxySQL’s capabilities, DigitalOcean has sponsored ProxySQL LLC, the company behind ProxySQL, to add support for the PROXY Protocol in its latest release, v2.7.0. This protocol brings significant improvements to managing database connections behind a load balancer, offering a streamlined way to identify client IP addresses. Let’s explore what this update means and how it can benefit your database environment!
What is ProxySQL?
ProxySQL is a high-performance protocol aware proxy for MySQL designed to optimize performance, scalability, and availability. Positioned between database clients and servers, it offers a wide range of features including connection pooling, read/write splitting, query routing, query rules, automatic failover, and load balancing. ProxySQL can handle millions of connections and queries, making it an excellent tool for organizations managing complex database environments.
At DigitalOcean, we use ProxySQL internally to manage our MySQL database connections which has proven to be highly effective. This is distinct from our public Database as a Service (DBaaS) offering, which does not use ProxySQL.
Problem: Loss of Client IP Information in Load-Balanced Traffic
When database traffic passes through a load balancer before ProxySQL, the load balancer obscures the original client’s IP address, making it difficult to track traffic sources accurately. Without visibility into client IPs, database administrators face challenges in many areas when troubleshooting and managing database connections.
The PROXY Protocol addresses this issue by preserving client IPs and connection details as traffic passes through load balancers.
How Does the PROXY Protocol Work in ProxySQL?
The PROXY Protocol adds a human-readable header to requests passing through a load balancer, which includes the original client’s IP address and connection details. When a connection reaches ProxySQL, it checks for this PROXY Protocol header. If the header is found and the network load balancer’s IP address matches the configured mysql-proxy_protocol_networks, ProxySQL extracts the original client IP and port from the header to be used in its system.
How to Enable PROXY Protocol in ProxySQL
Enabling the PROXY Protocol in ProxySQL is simple and flexible. By configuring the mysql-proxy_protocol_networks global variable, you can control how it is activated:
-
Default (empty string): Disables PROXY Protocol support.
-
*(Wildcard): Enables PROXY Protocol support for all incoming connections. -
Comma-separated IP addresses/subnets: Enables support only for specified IP ranges or subnets.
For example, to enable it for a subnet (192.168.1.0/24) and a specific IP (10.0.0.1), you would use:
mysql-proxy_protocol_networks = "192.168.1.0/24,10.0.0.1"
Note: The subnet notation should follow standard CIDR notation (e.g., 192.168.1.0/24).
Benefits of PROXY Protocol in ProxySQL
Once enabled, ProxySQL leverages the PROXY Protocol in several key areas:
-
Query Rules: IP-based rules can now be applied for query caching, routing, and filtering.
-
Processlist: The client IP and port are displayed, offering precise details on the originating connection.
-
Auditing & Logging: Logs now report the original client IP and port, providing accurate audit trails and query logs.
-
Access Control: With client IPs visible, administrators can create IP-based access restrictions to secure database connections.
Conclusion
For database administrators, this update simplifies the complexities of managing connections behind load balancers, offering a more reliable way to track and manage traffic sources. It is a valuable tool in modernizing database operations, providing enhanced visibility, control, and security in today’s dynamic, distributed network environments.
We hope you find this new feature beneficial to your environment!