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
- CentOS 7 Linux distribution
- Non-root user with sudo privileges
- Basic knowledge on how to use the Linux terminal
- MySQL installed on your CentOS 7. You can learn about that on our How to Install MYSQL 8.0 and Create a Database on a CentOS 7 Linux VPS or Dedicated Server tutorial
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
- Looking for Top web hosting? Clicking on this link can be the solution.
