Site icon CentLinux

How to install ProxySQL on CentOS 7

Alaric Bird
Share on Social Media

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:

2. Query Caching

ProxySQL includes a sophisticated query caching mechanism:

3. Query Rewriting and Filtering

ProxySQL offers powerful query rewriting and filtering features:

4. Connection Pooling

ProxySQL supports efficient connection management through connection pooling:

5. Query Statistics and Monitoring

ProxySQL provides detailed monitoring and statistics for database performance:

6. Traffic Filtering and Security

ProxySQL includes features for traffic management and security:

7. Replication and High Availability

ProxySQL supports replication and high availability setups:

8. Flexible Configuration

ProxySQL offers a dynamic and flexible configuration system:

Common Use Cases for ProxySQL

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

ProxySQL vs. Traditional Database Proxies

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

FeatureProxySQLTraditional Proxies
Load BalancingAdvancedBasic or None
Query CachingBuilt-inOften Not Available
Query RewritingYesLimited or None
Connection PoolingYesNot Always Available
Monitoring & StatisticsDetailedLimited
Traffic FilteringYesBasic or None
Replication ManagementAdvancedBasic
High AvailabilityBuilt-inNot Always Available
Dynamic ConfigurationYesOften Requires Restart
ProxySQL vs. 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:

MySQL Cluster Node 1:

MySQL Cluster Node 2:

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!

Exit mobile version