A Comprehensive ProxySQL Guide: Step-by-Step Installation and Configuration

What is ProxySQL?

ProxySQL is a powerful database proxy that offers SQL performance tuning for high-performance and efficient database management. In this ProxySQL guide, we will walk you through the step-by-step process of installing and configuring it on your Google Cloud Platform instance. From installation to configuration and testing, we’ll cover everything you need to know to get ProxySQL up and running smoothly.

Setting up ProxySQL

ProxySQL Guide: Installation 🚜

This wiki demonstrates the process of installing ProxySQL on an Instance. Let’s get started with the ProxySQL installation guide.

• Specifications
• Code Blocks

Specifications

• Platform – Google Cloud Platform 
• Operating System – CentOS 8 
• ProxySQL Package – proxysql-2.5.1-1-centos8.x86_64 

Code Blocks

• SSH into the instance and run the following commands 
• Install wget: Run the following command to install wget –

sudo yum install wget

• Download ProxySQL package from GitHub: Use wget to download the ProxySQL package from GitHub. Run the command –

wget https://github.com/sysown/proxysql/releases/download/v2.5.1/proxysql-2.5.1-1-centos8.x86_64.rpmCode language: JavaScript (javascript)

• Install ProxySQL: Install the downloaded ProxySQL package using the following command –

sudo yum localinstall proxysql-2.5.1-1-centos8.x86_64.rpmCode language: CSS (css)

• Start ProxySQL server: Start the server with the command –

sudo systemctl start proxysql.serviceCode language: CSS (css)

• Check the status of ProxySQL server: Ensure that ProxySQL is running without any issues by executing –

sudo systemctl status proxysql.serviceCode language: CSS (css)

• Install MySQL Client: Install the MySQL client using the commands

sudo yum update && sudo yum install mysql

The Installations are complete 🎉. For a more comprehensive guide please refer to the official documentation here.

ProxySQL Guide: Configuration 🤖

Now that ProxySQL is installed, it’s time to configure it to work with your MySQL database servers.

• Specifications
• Code Blocks

Specifications

• MySQL version on Database servers – 5.7.33-log MySQL Community Server 

Code Blocks 

• Login to ProxySQL server: Access the ProxySQL server using the command –

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)

• Add database server to ProxySQL Server
  • ProxySQL stores databases information in mysql_servers table
  • ProxySQL has a concept of hostgroups, and it uses a table mysql_replication_hostgroups in which we define writer and read hostgroups IDs as declared later in this doc here
    • There exists a column in mysql_replication_hostgroups named check_type with has following options read_only, innodb_read_only, super_read_only, read_only|innodb_read_only, read_only&innodb_read_only with a default value read_only
  • read_only is a variable on MySQL database, which is by default set to OFF, and if set to ON it disables writes from all other clients except the master, in case it is a slave database.
  • ProxySQL uses read_only value as a check for databases and monitors it.
  • According to the mysql-monitor_writer_is_also_reader (variable) value ProxySQL copies/moves databases to write hostgroup. For reference one can view the Read-only variables definitions on the official doc here
  • To create a Master-Replica setup, in which there is a single Master database and multiple Replica databases, we require to set read_only value to ON on the databases which are desired to be replicas, before configuring them to ProxySQL

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1, '10.*.*.*', 3306);Code language: JavaScript (javascript)

• Configure monitoring: ProxySQL constantly monitors the MySQL server backends configured to identify the health status. The credentials for monitoring the backends need to be created in MySQL and also configured in ProxySQL Assuming there exists a user with username “proxysql_monitor” and password “some-password”, let’s configure it on ProxySQL server –

UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='some-password' WHERE variable_name='mysql-monitor_password';
Code language: JavaScript (javascript)

• Any changes made to variables will only be applied on executing the below commands – 

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Check if the monitoring is working correctly by listing all monitor tables –

• List all monitor tables

SHOW TABLES FROM monitor;

• Use mysql_server_connect_log table to check connections

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;Code language: CSS (css)

• Use mysql_server_ping_log to check the backend server status

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;Code language: CSS (css)

• Configure replication host-groups

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES (1,2);

• Configure application user in ProxySQL server: Assuming an application currently accesses the database with a user with username “shurutech” and password “abcd”, to allow the user to access the database through the ProxySQL server we need to configure its username and password in the mysql_users table in the ProxySQL server.

INSERT INTO mysql_users(username, password,default_hostgroup) VALUES ('shurutech’,'abcd’,1);Code language: JavaScript (javascript)

• To apply the inserts and updates in tables we need to execute the following commands

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

ProxySQL server is now configured 🎉

Testing ProxySQL setup

ProxySQL Guide: How-Tos 📝

• How to configure a new user
• How to migrate a service to use ProxySQL
• How to check the status of the connection to Database servers
• How to check query stats
• How to verify that ProxySQL is functional and benchmark system performance

How to configure a new user

• Get an understanding of user authentication flow in ProxySQL server Here
• Allow ProxySQL server host for the user in the database using the solution as discussed Here
• Now we can configure the user in PoxySQL with the below commands
• Start mysql client

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)

• Insert user to mysql_users table

INSERT INTO mysql_users(username, password,default_hostgroup) VALUES ('shuruapp','abcd',1);Code language: JavaScript (javascript)

• Save changes to the disk

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

Yay! 🎉 user is now configured in the ProxySQL server.

How to migrate a service to use ProxySQL

• Configure service user in ProxySQL server using How to configure a new user
• Navigate to the file containing the environment variables in the service and update the following to ProxySQL internal IP and Port 6033 respectively
  • DB_HOST
  • DB_PORT
  • DB_GATEWAY_HOST
  • DB_GATEWAY_PORT
• There are several ways to test the migration and connection
  • check the connection on the service pod to ProxySQL internal IP with the help of netstat
  • check the stats_mysql_query_digest table in the ProxySQL server for entries corresponding to the newly added service user (say dummy_user )

SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC where username = "dummy_user";Code language: JavaScript (javascript)

How to check the status of the connection to Database servers

• Use the mysql_server_connect_log or mysql_server_ping_log table to check connections on the ProxySQL server

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
Code language: CSS (css)

How to check query stats

• Query stats can be found in the stats_mysql_query_digest table in ProxySQL

SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 20;

How to verify that ProxySQL is functional and benchmark system performance

ProxySQL suggests using sysbench.

• install sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbenchCode language: JavaScript (javascript)

• run sysbench

sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 runCode language: JavaScript (javascript)

ProxySQL Guide: Troubleshooting tips 📝

• Authentication error
• Access denied error for user
• Incorrect port mapping

Authentication error

Usually, authentication errors in ProxySQL setup occur due to a lack of an authenticated user between the ProxySQL server and MySQL server. For the user authentication flow to work seamlessly between the ProxySQL server and MySQL server and the application the user in mysql.user table needs to be configured in the ProxySQL server. (i.e. an entry with username, and password needs to be created in the mysql_users table in the ProxySQL server).

Access denied error for user

We can fix this problem by allowing users to access the MySQL server from a particular host.

• Currently, the application directly communicates with databases and hence queries are generated by a user from the application server with some defined IP address (say 10.20.0.1) to the database server.
• The database then authenticates the user and checks its privileges and allowed hosts (i.e. from which host machine the user can connect to the database, currently say 10.20.0.1).
• Since the ProxySQL server comes into the architecture, the database will be receiving queries from users through ProxySQL servers (in our case 10.200.0.10 or 10.200.0.9) instead of the application server (10.20.0.1 as assumed above).
• But the database does not know the ProxySQL servers yet (i.e. the users in mysql.user table are not allowed to connect through the ProxySQL server IP address yet).
• So to allow users to connect to the database through ProxySQL, we need to add the ProxySQL server IP address to the allowed hosts’ list for a user in the database.
• This can be done by either adding a new user or renaming the user. We choose to rename instead of creating a new user. Renaming replaces the corresponding row for a user in mysql.user table ( without revoking any user privileges).
• For example: RENAME USER ‘username’@’10.20.0.1’ to ‘username’@’10.200.0.1’

The above example exactly replaces the host 10.20.0.1 with 10.200.0.1 and connections from ProxySQL servers will now be allowed for the users. But it is not recommended as this might break other services which are not using ProxySQL. An alternative is to replace the host to accommodate the ProxySQL server Host (i.e. 10.200.0.1) with the previously allowed host (i.e. 10.20.0.1) by using 10.%.%.% instead.

Incorrect port mapping

It mostly happens due to the load balancer redirecting requests to the wrong port.

• The load balancer redirects to the same port on the backend as configured to communicate on the front end.
• For example, If you want the load balancer to talk to your backend on port 6033, your load balancer should receive traffic from the outer world on port 6033

ProxySQL Guide: Monitoring 📝

• This wiki guides you to visualise ProxySQL server metrics assuming we already have our Prometheus and Grafana infrastructure up and running, and Prometheus can connect to ProxySQL servers.
• ProxySQL comes with an inbuilt Prometheus exporter, follow the below steps to allow it to be accessible on port 6070 by the Prometheus server
• Login to MySQL client on ProxySQL server

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)

• Enable and configure ProxySQL’s REST API in ProxySQL Admin to connect with the inbuilt Prometheus Exporter

SET admin-restapi_enabled="true";
SET admin-restapi_port=6070;
Code language: JavaScript (javascript)

Execute the below commands to apply changes

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

• Now, we need to configure the exporter to our Prometheus Server. We can do it by adding a job in the prometheus.yml file and restart the Prometheus service

- job_name: "proxysql_staging"
scrape_interval: 5s
static_configs:
- targets: ["10.2xx.1.4:6070", "10.2xx.1.7.6070"]
Code language: Bash (bash)

• Now we can head to Grafana and add dashboards to visualise ProxySQL server metrics 🎉. Here is a link you could check for a more comprehensive guide for adding observability.

Why use ProxySQL?

ProxySQL helps in SQL performance tuning. By leveraging the benefits of ProxySQL, you can achieve improved database performance, scalability, high availability, and enhanced security for your applications. It acts as a crucial component in modern database architectures, ensuring efficient and reliable database operations.

• Load Balancing: ProxySQL acts as a database proxy server, distributing incoming database traffic across multiple database servers. It helps evenly distribute the workload, preventing overload on specific servers and ensuring optimal resource utilization.
• High Availability: By implementing ProxySQL in a high availability setup, you can eliminate single points of failure and increase the overall reliability of your database infrastructure. ProxySQL can automatically detect and route traffic to available database servers, minimizing downtime.
• Query Routing and Caching: ProxySQL provides intelligent query routing capabilities, allowing you to direct read and write queries to different database servers. This enables efficient utilization of resources, offloading read queries to replicas and directing write queries to the master. Additionally, ProxySQL can cache frequently executed queries, reducing the load on the database servers and improving response times.
• Query Analysis and Monitoring: ProxySQL offers powerful query analysis and monitoring features, allowing you to gain insights into the performance and behaviour of your database queries. It collects query statistics, such as execution time, number of queries, and query patterns, enabling you to identify bottlenecks, optimize queries, and improve overall database performance.
• Security and Access Control: ProxySQL provides an additional layer of security for your database infrastructure. It can enforce authentication and authorization rules, allowing you to control access to the database servers. ProxySQL can also mask sensitive data by rewriting queries or blocking specific queries to enhance data protection.
• Scalability and Flexibility: With ProxySQL, you can easily scale your database infrastructure by adding or removing database servers without affecting your applications. It abstracts the underlying database topology, allowing you to make changes to your database architecture transparently.
• Performance Optimization: ProxySQL offers various performance optimization features, such as connection pooling, thread pooling, and query optimization rules. These features help reduce connection overhead, optimize resource usage, and improve overall database performance.
• Ease of Deployment and Configuration: ProxySQL is relatively easy to deploy and configure, making it accessible to both small-scale and enterprise-level deployments. It provides a comprehensive set of configuration options, allowing you to fine-tune its behaviour based on your specific requirements.