Login
June 19, 2023|
Voiced by Amazon Polly |
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.
Understanding ProxySQL and its Benefits
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.
Pioneers in Cloud Consulting & Migration Services
- Reduced infrastructural costs
- Accelerated application deployment
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:
|
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:
|
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
|
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
|
1 2 3 |
$ 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
|
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
|
1 2 3 4 |
$ 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
|
1 2 3 |
$ 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.
|
1 2 3 |
$ 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
|
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
|
1 2 3 |
$ 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.
|
1 |
$ mysql -u proxyuser -p -h proxy_server_IP -P 6033 --prompt='proxyuser> ' |
Step 2: Monitor the query routing using the ProxySQLAdmin interface:
|
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
About CloudThat
CloudThat is an official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.
Drop a query if you have any questions regarding ProxySQL, I will get back to you quickly.
To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.
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.
PREV
Comments