Overview
Discover how ProxySQL, a helpful tool for Amazon RDS MySQL, can boost performance. This blog breaks down ProxySQL’s benefits, focusing on making read and write queries work smarter for your database. We’ll start by outlining what you need to get started. Then, step-by-step guides will show you how to install ProxySQL, set it up, and smoothly add your Amazon RDS MySQL database.
Learn how to tell ProxySQL to handle read and write queries better, improving your database traffic flow. We’ll also cover creating a monitor user for better visibility into ProxySQL’s performance. The blog wraps up with a guide on checking if the setup works well, examining ProxySQL stats, and using monitoring tools for precise query handling and overall database improvement. To finish, we’ll reflect on the ProxySQL journey, highlighting the benefits and how it boosts Amazon RDS MySQL performance.
Pioneers in Cloud Consulting & Migration Services
- Reduced infrastructural costs
- Accelerated application deployment
Prerequisites
An Amazon EC2 instance with the Ubuntu 22.04 image was used for this setup. The instance type selected was t2.micro. Additionally, an Amazon RDS MySQL master and slave database were utilized.
Architecture
Steps to Install ProxySQL
Step 1 — Adding repository
Ref: https://proxysql.com/documentation/installing-proxysql/
Step 2 – Install mysql-client
To connect to ProxySQL, you’ll need a tool called a MySQL client application. This is because ProxySQL uses a MySQL-like system for administrative tasks. We’ll use the mysql command line tool, which comes with the mysql-client package available in Ubuntu. Ensure your system has the latest version by updating your package repository, then install the mysql-client package.
Step – 3 Installing ProxySQL
Steps for Initial configuration
Step 1: Changing the ProxySQL default Administrator Password
The default user name and password is “admin”, we need to change the password first by connecting proxysql
To change the administrator password, update the “admin-admin_credentials” setting in the global_variables database. Just remember to replace “password” in the command with your chosen strong password.
Step 2: change the mysql interface
The change you made won’t happen right away because of how ProxySQL’s setup works. There are three parts to it:
Memory: This changes when you make edits using the command line.
Runtime: ProxySQL uses this for the actual settings.
Disk: This is for keeping the settings even when you restart.
Currently, your change is in the memory part. To make it happen, you need to move it to the runtime and then save it to the disk to keep it even after restarting.
Steps to Add Database
Note: I already created Amazon RDS MySQL master and read the replica instance and user with grand all privilege.
Step 1: Adding master database
Syntax:
Example:
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
Step 2: Adding slave database
Syntax:
To save the database configuration on disk, execute the following command:
Step 3: To view the status of the backend server (master, slave databases
Steps to Set up Read/write split query rules
The next step is to create rules to route SELECT queries to the read replica:
To persist the changes, execute the below commands
Steps to Configure database user credentials
Add the database user credentials to ProxySQL:
Syntax:
Ref: https://proxysql.com/documentation/proxysql-read-write-split-howto/
Steps to Configure Monitoring
ProxySQL always checks the health of the configured MySQL server backends. To do this, we need to create credentials in MySQL and set them up in ProxySQL, specifying how often the system should check. To make the user in MySQL, connect to the PRIMARY and follow these.
Step 1: Create a monitor user in the MySQL database
The user also needs the REPLICATION CLIENT privilege to monitor replication lag. In this case, execute:
Step 2: Add the credentials of the monitor user to ProxySQL
Log into the ProxySql admin console.
Steps to Verify the read and write split
Connect the proxysql server using its public IP:
Syntax:
While prompting the password, give the database password.
Then, execute some write and read queries like below;
Login to the proxysql admin console and execute the below command to see the query split
The above picture shows hostgroup 1 is master and 0 is read replica. As per our query split rule, it has split the write query into master and select query into the replica.
Conclusion
We navigated the installation and configuration process throughout this blog series, ensuring a solid foundation for ProxySQL integration. The capability to add databases effortlessly and set up specific rules for query routing empowers administrators to tailor performance strategies. Establishing a monitor user adds transparency, allowing for the insightful tracking of ProxySQL’s impact. It is clear that ProxySQL significantly contributes to a more responsive and scalable Amazon RDS MySQL environment. Whether it’s about load balancing, query routing, or enhanced security features, ProxySQL stands out as a valuable asset.
In conclusion, the implementation of ProxySQL emerges not only as a solution but as a catalyst for elevating the efficiency and reliability of Amazon RDS MySQL, making it an essential tool in the toolkit of database administrators.
Drop a query if you have any questions regarding ProxySQL and we will get back to you quickly.
Making IT Networks Enterprise-ready – Cloud Management Services
- Accelerated cloud migration
- End-to-end view of the cloud environment
About CloudThat
CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.
CloudThat is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, Microsoft Gold Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, and many more.
To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.
FAQs
1. How does ProxySQL contribute to improved database security?
ANS: – ProxySQL enhances security by allowing query rewriting, blocking, and masking. This means you can control and monitor the queries that reach your MySQL servers, adding a layer of protection to your Amazon RDS MySQL environment.
2. Can ProxySQL be used with different database setups in Amazon RDS?
ANS: – Yes, ProxySQL is versatile and can be configured to work with various database setups in AWS RDS. Whether you have a single master, multiple replicas, or a complex setup, ProxySQL provides the flexibility to optimize database performance across different scenarios.
3. Can ProxySQL be used with different Amazon RDS MySQL versions?
ANS: – Yes, ProxySQL is compatible with various MySQL versions, including those supported by Amazon RDS. It adapts to different setups and configurations, making it a versatile choice for optimizing performance across different MySQL versions in your AWS environment.
WRITTEN BY Harikrishnan S
Harikrishnan Seetharaman is a Research Associate (DevOps) at CloudThat. He completed his Bachelor of Engineering degree in Electronics and Communication, and he achieved AWS solution architect-Associate certification. His area of interest is implementing a cloud-native solution for customers and helping them by proving robust and reliable solutions for their complex problems, DevOps, and SaaS. Apart from his professional interest he likes to spend time in farming and learning new DevOps tools.
Click to Comment