A Guide to Implement ProxySQL with Amazon RDS as a Backend

Introduction

In today’s dynamic and scalable environments, a robust database proxy solution is becoming increasingly important. ProxySQL, a powerful open-source SQL-aware proxy, enables efficient database management and high availability. This blog post will guide you through implementing ProxySQL with Amazon RDS as the backend database, ensuring optimized performance and seamless database operations.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Understanding ProxySQL and its Benefits

ProxySQL is a high-performance, SQL-aware proxy between the application and the database. It acts as an intermediary, providing load balancing, query routing, caching, and failover features.

By implementing ProxySQL with RDS as the backend, you can achieve several benefits, including:

Load Balancing: ProxySQL distributes the database load across multiple backend nodes, ensuring optimal utilization of resources and improved performance.
Query Routing: ProxySQL intelligently routes queries based on predefined rules, allowing you to direct read and write queries to specific replicas or partitions.
Connection Pooling: ProxySQL efficiently manages connections to the backend database, reducing overhead and enhancing scalability.
High Availability: ProxySQL monitors the health of backend nodes and automatically redirects traffic to available nodes in case of failures, improving system uptime.
Query Caching: ProxySQL can cache frequently executed queries, reducing the load on the backend database and improving response times.

Prerequisites

Before we proceed with the implementation, make sure you have the following prerequisites in place:

An AWS account with access to Amazon RDS.
An RDS instance is running MySQL or MariaDB.
A separate server or Amazon EC2 instance is used to install and configure ProxySQL.

Steps to Set up ProxySQL

To install ProxySQL, follow these steps:

Step 1: Log in to your server or EC2 instance.

Step 2: Add the ProxySQL repository to your package manager:

$ sudo wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
$ sudo dpkg -i proxysql-release-latest-xenial.deb

1 2	$ sudo wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb $ sudo dpkg -i proxysql-release-latest-xenial.deb

Step 3: Install ProxySQL:

$ sudo apt-get update
$ sudo apt-get install proxysql

1 2	$ sudo apt-get update $ sudo apt-get install proxysql

Configuring ProxySQL:

Once ProxySQL is installed, follow these steps to configure it:

Step 1: Access the ProxySQL administration interface

$ mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin>'

1	$ mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin>'

Step 2: Set Admin password Load them to runtime and save to disk

$ ProxySQLAdmin>  UPDATE global_variables SET variable_value='admin:admin_password' WHERE variable_name='admin-admin_credentials';
$ ProxySQLAdmin>  LOAD ADMIN VARIABLES TO RUNTIME;
$ ProxySQLAdmin>  SAVE ADMIN VARIABLES TO DISK;

$ ProxySQLAdmin> UPDATE global_variables SET variable_value='admin:admin_password' WHERE variable_name='admin-admin_credentials';

$ ProxySQLAdmin> LOAD ADMIN VARIABLES TO RUNTIME;

$ ProxySQLAdmin> SAVE ADMIN VARIABLES TO DISK;

Step 3: Create a user for monitoring in primary database

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

1 2	mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; mysql> GRANT USAGE, REPLICATION CLIENT ON . TO 'monitor'@'%';

Step 4: Configure ProxySQL to monitor backend connections

$ ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
$ ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
$ ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
$ ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

$ ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

$ ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

$ ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;

$ ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

Step 5: Configure ProxySQL to connect with the backend RDS instance

$ ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, 'RDS_ENDPOINT', 3306);
$ ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
$ ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;

$ ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, 'RDS_ENDPOINT', 3306);

$ ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;

$ ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;

Note: Replace ‘RDS_ENDPOINT’ with the endpoint of your RDS instance.

Step 6: Configure ProxySQL to route queries to the backend

ProxySQL allows you to route queries based on various conditions, such as SQL patterns, users, or schema. The following query will insert query rules in ProxySQL to direct read queries to specific RDS replicas.

$ ProxySQLAdmin> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) VALUES (1, 1, '^SELECT.*', 0);
$ ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;
$ ProxySQLAdmin> SAVE MYSQL QUERY RULES TO DISK;

$ ProxySQLAdmin> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) VALUES (1, 1, '^SELECT.*', 0);

$ ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;

$ ProxySQLAdmin> SAVE MYSQL QUERY RULES TO DISK;

Step 7: Create read only user with read permissions in a primary database for the proxysql server to access the database

mysql> CREATE USER 'proxyuser'@'%' IDENTIFIED BY 'proxy_password';
mysql> GRANT SELECT, REFERENCES, RELOAD on *.* TO 'proxyuser'@'%';

1 2	mysql> CREATE USER 'proxyuser'@'%' IDENTIFIED BY 'proxy_password'; mysql> GRANT SELECT, REFERENCES, RELOAD on . TO 'proxyuser'@'%';

Step 8: Configure ProxySQL to handle client connections

$ ProxySQLAdmin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('proxyuser', 'proxy_password', 0);
$ ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;
$ ProxySQLAdmin> SAVE MYSQL USERS TO DISK;

$ ProxySQLAdmin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('proxyuser', 'proxy_password', 0);

$ ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;

$ ProxySQLAdmin> SAVE MYSQL USERS TO DISK;

Step 9: In RDS Console”Network & Security” section and modify the security group settings to allow inbound traffic from the ProxySQL server. Specify the IP address or security group associated with the ProxySQL server.

Testing and Verifying the Setup

Once ProxySQL and RDS are configured, you can test the setup by connecting a client application to ProxySQL and verifying the query routing and load balancing.

Step 1: Connect to ProxySQL from a MySQL client application using the ProxySQL server’s IP address and the configured username and password. Execute any select queries and validate.

$ mysql -u proxyuser -p -h proxy_server_IP -P 6033 --prompt='proxyuser> '

1	$ mysql -u proxyuser -p -h proxy_server_IP -P 6033 --prompt='proxyuser> '

Step 2: Monitor the query routing using the ProxySQLAdmin interface:

$ ProxySQLAdmin> SELECT * FROM stats.stats_mysql_connection_pool;

1	$ ProxySQLAdmin> SELECT * FROM stats.stats_mysql_connection_pool;

Conclusion

In this blog post, we explored the implementation of ProxySQL with Amazon RDS as the backend. We covered the installation and configuration of ProxySQL and the necessary steps to configure Amazon RDS and test the setup. By implementing ProxySQL, you can enhance your database infrastructure’s performance, scalability, and availability.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is an award-winning company and the first in India to offer cloud training and consulting services worldwide. As a Microsoft Solutions Partner, AWS Advanced Tier Training Partner, and Google Cloud Platform Partner, CloudThat has empowered over 850,000 professionals through 600+ cloud certifications winning global recognition for its training excellence including 20 MCT Trainers in Microsoft’s Global Top 100 and an impressive 12 awards in the last 8 years. CloudThat specializes in Cloud Migration, Data Platforms, DevOps, IoT, and cutting-edge technologies like Gen AI & AI/ML. It has delivered over 500 consulting projects for 250+ organizations in 30+ countries as it continues to empower professionals and enterprises to thrive in the digital-first world.

FAQs

1. How does ProxySQL handle failover and high availability?

ANS: – ProxySQL ensures high availability by monitoring the status and health of backend database servers. In a failure, it automatically redirects client connections to a healthy server, minimizing downtime. It can monitor server status and replication lag or use custom scripts for failover.

2. What is the role of ProxySQL in a MySQL/MariaDB setup?

ANS: – ProxySQL acts as a high-performance proxy between client applications and database servers. It improves performance and scalability by load balancing client requests across multiple servers. It also provides query routing, rewriting, connection pooling, and query caching functionalities.

WRITTEN BY Dharshan Kumar K S

Dharshan Kumar is a Research Associate at CloudThat. He has a working knowledge of various cloud platforms such as AWS, Microsoft, ad GCP. He is interested to learn more about AWS's Well-Architected Framework and writes about them.