The Power of ProxySQL to Split Read and Write Queries in Amazon RDS

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

Get Started

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

# apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg

1	# apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg

# wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -

1	# wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' \| apt-key add -

install2

# echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

1	# echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ \| tee /etc/apt/sources.list.d/proxysql.list

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.

# apt-get update
# apt-get install mysql-client

1 2	# apt-get update # apt-get install mysql-client

Step – 3 Installing ProxySQL

# apt-get update
# apt-get install proxysql

1 2	# apt-get update # apt-get install proxysql

# systemctl start proxysql
# systemctl status proxysql

1 2	# systemctl start proxysql # systemctl status proxysql

install6

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

# 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> '

initial1

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.

# UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';

1	# UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';

Step 2: change the mysql interface

# UPDATE global_variables SET variable_value = '0.0.0.0:3306' WHERE variable_name = 'mysql-interfaces';

1	# UPDATE global_variables SET variable_value = '0.0.0.0:3306' WHERE variable_name = 'mysql-interfaces';

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.

# LOAD ADMIN VARIABLES TO RUNTIME;
# SAVE ADMIN VARIABLES TO DISK;
# PROXYSQL RESTART;  (optional)

# LOAD ADMIN VARIABLES TO RUNTIME;

# SAVE ADMIN VARIABLES TO DISK;

# PROXYSQL RESTART; (optional)

initial4

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:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (0, 'DataBase-endpoint', DataBasePort);

1 2	INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, 'DataBase-endpoint', DataBasePort);

Example:
INSERT INTO mysql_servers(hostgroup_id, hostname, port)

VALUES (0, 'masterdb.xyz', 3306);

1	VALUES (0, 'masterdb.xyz', 3306);

Step 2: Adding slave database
Syntax:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (1, 'SlavedbEndPoint', PortNumber);

1 2	INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'SlavedbEndPoint', PortNumber);

To save the database configuration on disk, execute the following command:

# LOAD MYSQL SERVERS TO RUNTIME;
# SAVE MYSQL SERVERS TO DISK;

1 2	# LOAD MYSQL SERVERS TO RUNTIME; # SAVE MYSQL SERVERS TO DISK;

Step 3: To view the status of the backend server (master, slave databases

# SELECT hostgroup_id,hostname,status FROM mysql_servers;

1	# SELECT hostgroup_id,hostname,status FROM mysql_servers;

add3

Steps to Set up Read/write split query rules

The next step is to create rules to route SELECT queries to the read replica:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1), (2, 1, '^SELECT', 1, 1);

1 2	INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1), (2, 1, '^SELECT', 1, 1);

To persist the changes, execute the below commands

# LOAD MYSQL QUERY RULES TO RUNTIME; 
# SAVE MYSQL QUERY RULES TO DISK;

1 2	# LOAD MYSQL QUERY RULES TO RUNTIME; # SAVE MYSQL QUERY RULES TO DISK;

Steps to Configure database user credentials

Add the database user credentials to ProxySQL:

Syntax:

INSERT INTO mysql_users(username, password, default_hostgroup) 
VALUES ('DBusername', 'DBpassword', 0);

1 2	INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('DBusername', 'DBpassword', 0);

# LOAD MYSQL USERS TO RUNTIME; 
# SAVE MYSQL USERS TO DISK;

1 2	# LOAD MYSQL USERS TO RUNTIME; # SAVE MYSQL USERS TO DISK;

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

# CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';

1	# CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';

The user also needs the REPLICATION CLIENT privilege to monitor replication lag. In this case, execute:

#  GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

1	# GRANT USAGE, REPLICATION CLIENT ON . TO 'monitor'@'%';

Step 2: Add the credentials of the monitor user to ProxySQL

Log into the ProxySql admin console.

# UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

1	# UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

#  UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

1	# UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

Steps to Verify the read and write split

Connect the proxysql server using its public IP:
Syntax:

# mysql -h <public-IP> -u <username> -p

1	# mysql -h <public-IP> -u <username> -p

While prompting the password, give the database password.
Then, execute some write and read queries like below;

# drop database trail
# select database;

1 2	# drop database trail # select database;

# select * from stats_mysql_query_digest;

1	# select * from stats_mysql_query_digest;

verify3

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

ProxySQL, a strategic tool for optimizing Amazon RDS MySQL performance, reveals a robust solution for intelligently managing database traffic. By adeptly splitting read and write queries, ProxySQL enhances efficiency, providing a seamless experience for users.

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

Get Started

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.