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

Was this article helpful?