How to Install and Configure ProxySQL on CentOS 7

How to Install and Configure ProxySQL on CentOS 7

Introduction

ProxySQL is a high performance proxy that acts as an intermediary between database(s) and all the clients that seek resources to it. It has a number of benefits including:

  • Routing queries to healthy databases to ensure optimum query processing.
  • Provides intelligent load balancing of requests onto the pool of databases.
  • Acts as a firewall by filtering queries that are made to the database hence preventing security issues arising from SQL injection.
  • Abstracts the complexity of the underlying database topology from the concerned applications.
  • It can re-write badly written queries for optimization and also has a query cache feature to ensure the same.

Prerequisites

Steps

Update system packages

$ sudo yum update

Install dependencies

$ sudo yum install perl-DBD-MYSQL -y
$ sudo yum install wget -y

Install ProxySQL

$ sudo wget https://github.com/sysown/proxysql/releases/download/v1.4.12/proxysql-1.4.12-1-centos7.x86_64.rpm
$ sudo rpm -ivh proxysql-1.4.12-1-centos7.x86_64.rpm

You can now go ahead and start the ProxySQL service and ensure that is running.

$ sudo systemctl start proxysql
$ sudo systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)
   Active: active (running) since Sat 2018-09-22 20:44:40 UTC; 3s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 14660 ExecStart=/etc/rc.d/init.d/proxysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/proxysql.service
               ├─14664 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
               └─14665 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
Sep 22 20:44:40 centos7-hostadvice systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL...
Sep 22 20:44:40 centos7-hostadvice proxysql[14660]: Starting ProxySQL: 2018-09-22 20:44:40 [INFO] Using config file /etc/proxysql.cnf
Sep 22 20:44:40 centos7-hostadvice proxysql[14660]: DONE!
Sep 22 20:44:40 centos7-hostadvice systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL.

It should show you active (running) in the third line of the output starting with “Active”. The above output means that ProxySQL has successfully been installed and is running.

You may access the admin interface by using the following command.

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>' --default-auth=mysql_native_password
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 12
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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'forhelp. Type 'c' to clear the current input statement.
Admin>

Set an Administrator Password

By default, the username and password is admin, which is really insecure. You may want to change the password.

In the ProxySQL prompt, enter the following command while replacing the passwordwith your own unique password.

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

Make the changes persistent by adding the following commands:

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

Conclusion

We have successfully installed ProxySQL and configured the admin password for extra security. Note configurations may also be done and parsed into ProxySQL via libconfig.

Although ProxySQL is still under active development and constant changes are being made from version to version, it has proven to be an excellent tool for databases’ complexity management.

Check out configuring ProxySQL and running it against MYSQL nodes for monitoring.

Check out these top 3 Linux hosting services

FastComet
AU$2.69 /mo
Starting price
Visit FastComet
Rating based on expert review
  • User Friendly
    4.7
  • Support
    5.0
  • Features
    4.8
  • Reliability
    4.5
  • Pricing
    5.0
HostArmada
AU$3.74 /mo
Starting price
Visit HostArmada
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.5
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.0
Hostinger
AU$4.49 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7

How to Run ProxySQL Against MySQL for Monitoring on CentOS 7

ProxySQL serves to monitor and filter; as a firewall all incoming requests from
3 min read
Kennedy Mbuvi
Kennedy Mbuvi
Author

How To Set up a VSFTPD Server on a CentOS 7 VPS or Dedicated Server

Brief Description FTP is usually insecure exposing clear-text passwords, userna
2 min read
Avi Ilinsky
Avi Ilinsky
Hosting Expert

How to Install MongoDB on Your CentOS 7 VPS or Dedicated Server

MongoDB is designed for extensive databases and is mainly used for data analytic
2 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Set Up a Linux VPN Server from a Windows Client

VPN is important for securely connecting to server’s resource
5 min read
Vladimir Rakov
Vladimir Rakov
Hosting Expert
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top