Site icon Shuru

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

sudo yum install wget
wget https://github.com/sysown/proxysql/releases/download/v2.5.1/proxysql-2.5.1-1-centos8.x86_64.rpmCode language: JavaScript (javascript)
sudo yum localinstall proxysql-2.5.1-1-centos8.x86_64.rpmCode language: CSS (css)
sudo systemctl start proxysql.serviceCode language: CSS (css)
sudo systemctl status proxysql.serviceCode language: CSS (css)
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 

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1, '10.*.*.*', 3306);Code language: JavaScript (javascript)
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)
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

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

SHOW TABLES FROM monitor;
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;Code language: CSS (css)
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;Code language: CSS (css)
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES (1,2);
INSERT INTO mysql_users(username, password,default_hostgroup) VALUES ('shurutech’,'abcd’,1);Code language: JavaScript (javascript)
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

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)
INSERT INTO mysql_users(username, password,default_hostgroup) VALUES ('shuruapp','abcd',1);Code language: JavaScript (javascript)
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

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

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

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.

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

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.

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.

ProxySQL Guide: Monitoring 📝

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> 'Code language: JavaScript (javascript)
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;
- job_name: "proxysql_staging"
scrape_interval: 5s
static_configs:
- targets: ["10.2xx.1.4:6070", "10.2xx.1.7.6070"]
Code language: Bash (bash)

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.

Exit mobile version