How to install ProxySQL on CentOS 7

Learn how to install ProxySQL on CentOS 7 with our step-by-step guide. Follow detailed instructions to set up and configure ProxySQL for high-performance database management. #centlinux #linux #mysql

What is ProxySQL?

ProxySQL is a high-performance, open-source proxy server designed to enhance the efficiency, scalability, and management of MariaDB and MySQL database clusters. As an intermediary between client applications and database servers, ProxySQL offers advanced features that help optimize database performance, ensure reliability, and simplify management tasks.

Key Features of ProxySQL

Here’s a detailed look at ProxySQL’s features and how they benefit your database environment:

1. Advanced Load Balancing

ProxySQL provides advanced load balancing capabilities to efficiently distribute database queries:

• Query Routing: Routes queries to different backend servers based on various criteria like query type, schema, or user. This allows for intelligent distribution of read and write queries.
• Load Distribution: Balances the load between primary and replica servers to maximize resource utilization and improve performance.
• Failover Management: Automatically handles server failures by redirecting traffic to available servers, ensuring high availability and minimal downtime.

2. Query Caching

ProxySQL includes a sophisticated query caching mechanism:

• Query Cache: Caches the results of frequently requested queries to reduce the load on backend servers and speed up response times.
• Configurable Cache Policies: Supports customizable cache expiration and invalidation rules to manage cached queries effectively.
• Performance Boost: Reduces the number of queries hitting the database, which can significantly improve performance for repeated requests.

3. Query Rewriting and Filtering

ProxySQL offers powerful query rewriting and filtering features:

• Query Rewriting: Allows modification of queries before they reach the backend server, useful for query optimization or redirection.
• Query Filtering: Applies rules to accept, reject, or modify queries based on specific conditions.
• Optimization: Helps improve query performance and manage complex query patterns.

4. Connection Pooling

ProxySQL supports efficient connection management through connection pooling:

• Connection Pooling: Reuses existing connections to reduce the overhead of establishing new connections.
• Configurable Pool Settings: Allows adjustments to pool size, connection timeouts, and more for optimal performance.

5. Query Statistics and Monitoring

ProxySQL provides detailed monitoring and statistics for database performance:

• Query Analytics: Tracks performance metrics such as query execution time, errors, and resource usage.
• Real-Time Monitoring: Offers a real-time view of query activity and server status.
• Historical Data: Collects historical performance data for analysis and optimization.

6. Traffic Filtering and Security

ProxySQL includes features for traffic management and security:

• Access Control: Defines rules to control which clients can connect to which backend servers.
• Traffic Management: Manages traffic flow based on query types, user roles, or other criteria.
• Security Enhancements: Protects against threats such as SQL injection attacks.

7. Replication and High Availability

ProxySQL supports replication and high availability setups:

• Replication Management: Handles replication configurations between primary and replica servers.
• High Availability Solutions: Provides failover and redundancy features to ensure continuous service availability.

8. Flexible Configuration

ProxySQL offers a dynamic and flexible configuration system:

• Dynamic Configurations: Allows changes to settings without restarting the ProxySQL service.
• Configuration Management: Provides management through a command-line interface, API, or web interface.

Common Use Cases for ProxySQL

ProxySQL can be applied in various scenarios to enhance database performance, reliability, and management:

• High-Performance Database Environments: Optimize database queries and manage high-traffic environments.
• Read/Write Splitting: Distribute write queries to primary servers and read queries to replicas.
• Failover Management: Implement automated failover strategies for high availability.
• Query Optimization: Rewrite and cache queries to improve performance.
• Database Security: Filter and control traffic to prevent unauthorized access and attacks.

ProxySQL vs. Traditional Database Proxies

Here’s a comparison of ProxySQL with traditional database proxies:

Recommended Courses

If you’re serious about mastering databases and boosting your tech career, The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert by Colt Steele is one of the most practical and highly-rated online courses available. Designed for both beginners and professionals, this course walks you step-by-step from the fundamentals of SQL to advanced database management skills using real-world examples.

Whether you’re a developer, data analyst, or aspiring system administrator, this bootcamp will give you the confidence to work with MySQL like a pro. [Enroll today] and start building skills that employers value.

Disclaimer: This post contains affiliate links. If you purchase through these links, I may earn a small commission at no additional cost to you.

MySQL Cluster Specification

In this article, we will install ProxySQL on CentOS 7 to use it as a load balancer for MySQL cluster.

We are using three CentOS 7 virtual machines with following specifications.

ProxySQL Load Balancer:

• CPU – 3.4 Ghz (2 Cores)
• Memory – 1 GB
• Storage – 20 GB
• Hostname – proxysql-01.example.com
• IP Address – 192.168.116.210 /24
• Operating System – CentOS 7.6

MySQL Cluster Node 1:

• CPU – 3.4 Ghz (2 Cores)
• Memory – 1 GB
• Storage – 60 GB
• Hostname – percona-01.example.com
• IP Address – 192.168.116.209 /24
• Operating System – CentOS 7.6

MySQL Cluster Node 2:

• CPU – 3.4 Ghz (2 Cores)
• Memory – 1 GB
• Storage – 60 GB
• Hostname – percona-02.example.com
• IP Address – 192.168.116.208 /24
• Operating System – CentOS 7.6

To ensure a robust and reliable environment for running ProxySQL on CentOS 7, it’s essential to have a powerful and stable server setup. Consider the HPE ProLiant MicroServer Gen11 Tower, a best-seller known for its reliable performance and scalability, ideal for database and proxy management tasks.

Pair this with the SanDisk Ultra 1TB Internal SSD, which offers lightning-fast read/write speeds to significantly enhance database query handling and caching efficiency. These products can elevate your ProxySQL deployment experience by delivering optimal hardware performance.

Disclaimer: If you purchase through the affiliate links, a small commission will be earned at no additional cost to you, supporting the continued creation of technical content.

Install ProxySQL on CentOS 7

Connect with proxysql-01.example.com using ssh as root user.

We require MySQL client tools to connect with ProxySQL admin panel. Therefore, we are installing mariadb-client using yum command.

yum install -y mariadb

ProxySQL v2.0.6 is available for download at GitHub.

Copy the URL of your required package and use yum command to install ProxySQL on CentOS 7.

yum install -y https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql-2.0.6-1-centos7.x86_64.rpm

Enable and start ProxySQL service.

systemctl enable proxysql.service
systemctl start proxysql.service

Output:

proxysql.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig proxysql on

Allow ProxySQL service port 6033/tcp (it is the reverse of MySQL default port 3306) in Linux Firewall.

firewall-cmd --permanent --add-port=6033/tcp
firewall-cmd --reload

Configure ProxySQL Load Balancer

ProxySQL runs its administration service on default port 6032/tcp. We can use MySQL client tools to connect to ProxySQL admin panel and configure the load balancer as per our requirements.

The default administrative user for ProxySQL is admin with password admin.

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL> '

Output:

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

ProxySQL>

Change default password for ProxySQL admin user.

UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Add our backend database servers (MySQL, MariaDB or Percona) to the ProxySQL server pool.

We are assuming that you have already configured replication between MySQL nodes before configuring ProxySQL load balancer.

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.116.209',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.116.208',3306);

Query the msql_servers table to verify the records.

SELECT hostgroup_id,hostname,port,status FROM mysql_servers;

Output:

+--------------+-----------------+------+--------+
| hostgroup_id | hostname        | port | status |
+--------------+-----------------+------+--------+
| 1            | 192.168.116.209 | 3306 | ONLINE |
| 1            | 192.168.116.208 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
2 rows in set (0.00 sec)

Configure ProxySQL Nodes Monitoring

ProxySQL requires a database user in MySQL cluster for monitoring of MySQL nodes.

Connect with percona-01.example.com using ssh as root user.

Login to MySQL database instance.

mysql -u root -p123

Execute following commands to create the monitoring user with necessary privileges.

CREATE USER 'monitor'@'%' IDENTIFIED BY 'Ahm3r';
GRANT SELECT ON sys.* TO  'monitor'@'%';
FLUSH PRIVILEGES;
EXIT

Connect with proxysql-01.example.com using ssh as root user.

Login to ProxySQL admin panel using mysql command.

Define monitoring username/password in global variables.

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

Configure monitoring intervals as follows.

UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

Load the changes into Runtime to apply the changes.

LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;

Save the changes into Disk for persistency.

SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;

Query the connection log table to verify the connectivity between ProxySQL and MySQL cluster nodes.

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

Output:

+-----------------+------+------------------+-------------------------+---------------+
| hostname        | port | time_start_us    | connect_success_time_us | connect_error |
+-----------------+------+------------------+-------------------------+---------------+
| 192.168.116.209 | 3306 | 1569689633319939 | 1248                    | NULL          |
| 192.168.116.208 | 3306 | 1569689633280911 | 1291                    | NULL          |
| 192.168.116.208 | 3306 | 1569689631309213 | 1193                    | NULL          |
| 192.168.116.209 | 3306 | 1569689631278225 | 1107                    | NULL          |
| 192.168.116.208 | 3306 | 1569689629316735 | 1252                    | NULL          |
| 192.168.116.209 | 3306 | 1569689629277982 | 1415                    | NULL          |
| 192.168.116.209 | 3306 | 1569689627305699 | 1168                    | NULL          |
| 192.168.116.208 | 3306 | 1569689627274188 | 1095                    | NULL          |
| 192.168.116.209 | 3306 | 1569689625308582 | 1276                    | NULL          |
| 192.168.116.208 | 3306 | 1569689625273728 | 1252                    | NULL          |
+-----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)

Create ProxySQL users for MySQL cluster

Connect with percona-01.example.com using ssh as root user.

Login to MySQL database instance using mysql command and execute following commands to create a database user.

CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
GRANT ALL ON RECIPES.* TO 'testuser'@'%';
FLUSH PRIVILEGES;
EXIT

Connect with proxysql-01.example.com using ssh as root user.

Login to ProxySQL admin panel and execute following commands to add the same MySQL database user in ProxySQL configuration.

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Now connect to MySQL database cluster via ProxySQL load balancer.

mysql -u testuser -p123 -h 127.0.0.1 -P6033

Output:

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> 

Check hostname of the database node, that you are connected with.

select @@hostname;

Output:

+------------------------+
| @@hostname             |
+------------------------+
| percona-01.example.com |
+------------------------+
1 row in set (0.00 sec)

Display list of available databases.

show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| RECIPES            |
+--------------------+
2 rows in set (0.20 sec)

We have been connected to percona-01.example.com node via ProxySQL load balancer. It show that our ProxySQL load balancer is working fine.

Final Thoughts

Installing ProxySQL on CentOS 7 is a crucial step for optimizing your database setup, offering advanced features like load balancing and query routing for improved performance and reliability. I hope this guide has provided you with the information you need to complete the installation successfully.

Need expert AWS and Linux system administration? From cloud architecture to server optimization, I provide reliable and efficient solutions tailored to your needs. Hire me today!

Let’s work together to make sure your ProxySQL setup meets all your performance and management needs. Contact me today for customized solutions and support!