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 Training for You: ProxySQL Course HandsOn ProxySQL Tutorial Master Class

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

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
proxysql.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig proxysql on
# systemctl start proxysql.service

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
success
# firewall-cmd --reload
success

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

ProxySQL> UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
Query OK, 1 row affected (0.00 sec)

ProxySQL> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQL> SAVE ADMIN VARIABLES TO DISK;
Query OK, 32 rows affected (0.00 sec)

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.

ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.116.209',3306);
Query OK, 1 row affected (0.00 sec)

ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.116.208',3306);
Query OK, 1 row affected (0.00 sec)

Query the msql_servers table to verify the records.

ProxySQL> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+-----------------+------+--------+
| 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 and execute following commands to create the monitoring user with necessary privileges.

# mysql -u root -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 14
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'Ahm3r';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT SELECT ON sys.* TO  'monitor'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT
Bye

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.

ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

ProxySQL> UPDATE global_variables SET variable_value='Ahm3r' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Configure monitoring intervals as follows.

ProxySQL> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)

Load the changes into Runtime to apply the changes.

ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

Save the changes into Disk for persistency.

ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Query OK, 121 rows affected (0.00 sec)

ProxySQL> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.06 sec)

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

ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-----------------+------+------------------+-------------------------+---------------+
| 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.

mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (1.19 sec)

mysql> GRANT ALL ON RECIPES.* TO 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)

mysql> EXIT
Bye

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.

ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
Query OK, 1 row affected (0.00 sec)

ProxySQL> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQL> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Now connect to MySQL database cluster via ProxySQL load balancer.

# mysql -u testuser -p123 -h 127.0.0.1 -P6033
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)]> select @@hostname;
+------------------------+
| @@hostname             |
+------------------------+
| percona-01.example.com |
+------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> show databases;
+--------------------+
| 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.

If you are new to MySQL database then, we recommend you to read Murach’s MySQL (3rd Edition) (PAID LINK) by Mike Murach & Associates. This book is a very good starting point for newbies.

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.

If you need professional help with your ProxySQL setup or require additional configuration and optimization, I’m here to assist you! Check out my Fiverr gig for expert support and get your ProxySQL environment running smoothly: Linux Server Support

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!