ProxySQL is a high performance proxy/load-balancer for MySQL database clusters. ProxySQL is free and open source and it is distributed under GPL license. ProxySQL supports thousands of concurrent connections. ProxySQL is highly customizable via a SQL like command-line interface.
In this article, we are installing ProxySQL Load Balancer for MySQL cluster on CentOS 7.
If you are new to MySQL database then, we recommend you to read Murach's MySQL (3rd Edition) by Mike Murach & Associates. This book is a very good starting point for newbies.
This Article Provides:
ProxySQL Features:
Some of the popular features of ProxySQL are:
- High performance
- Efficient workload management
- Query caching
- Query routing
- Supports failover
- Advanced configuration with 0 downtime
- Application layer proxy
- Cross platform
- Advanced topology support
- Firewall
Environment Specification:
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
Installing ProxySQL Load Balancer for MySQL 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.
[root@proxysql-01 ~]# yum install -y mariadb
...
Installed:
mariadb.x86_64 1:5.5.64-1.el7
Dependency Installed:
perl.x86_64 4:5.16.3-294.el7_6
perl-Carp.noarch 0:1.26-244.el7
perl-Encode.x86_64 0:2.51-7.el7
perl-Exporter.noarch 0:5.68-3.el7
perl-File-Path.noarch 0:2.09-2.el7
perl-File-Temp.noarch 0:0.23.01-3.el7
perl-Filter.x86_64 0:1.49-3.el7
perl-Getopt-Long.noarch 0:2.40-3.el7
perl-HTTP-Tiny.noarch 0:0.033-3.el7
perl-PathTools.x86_64 0:3.40-5.el7
perl-Pod-Escapes.noarch 1:1.04-294.el7_6
perl-Pod-Perldoc.noarch 0:3.20-4.el7
perl-Pod-Simple.noarch 1:3.28-4.el7
perl-Pod-Usage.noarch 0:1.63-3.el7
perl-Scalar-List-Utils.x86_64 0:1.27-248.el7
perl-Socket.x86_64 0:2.010-4.el7
perl-Storable.x86_64 0:2.45-3.el7
perl-Text-ParseWords.noarch 0:3.29-4.el7
perl-Time-HiRes.x86_64 4:1.9725-3.el7
perl-Time-Local.noarch 0:1.2300-2.el7
perl-constant.noarch 0:1.27-2.el7
perl-libs.x86_64 4:5.16.3-294.el7_6
perl-macros.x86_64 4:5.16.3-294.el7_6
perl-parent.noarch 1:0.225-244.el7
perl-podlators.noarch 0:2.5.1-3.el7
perl-threads.x86_64 0:1.87-4.el7
perl-threads-shared.x86_64 0:1.43-6.el7
Dependency Updated:
mariadb-libs.x86_64 1:5.5.64-1.el7
Complete!
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 server.
[root@proxysql-01 ~]# yum install -y https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql-2.0.6-1-centos7.x86_64.rpm
Loaded plugins: fastestmirror
proxysql-2.0.6-1-centos7.x86_64.rpm | 9.0 MB 00:21
Examining /var/tmp/yum-root-25LFKp/proxysql-2.0.6-1-centos7.x86_64.rpm: proxysql-2.0.6-1.x86_64
Marking /var/tmp/yum-root-25LFKp/proxysql-2.0.6-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.0.6-1 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: proxysql-2.0.6-1.x86_64
Loading mirror speeds from cached hostfile
* base: repo.isra.edu.pk
* extras: repo.isra.edu.pk
* updates: repo.isra.edu.pk
--> Processing Dependency: perl(DBI) for package: proxysql-2.0.6-1.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: perl-DBI-1.627-4.el7.x86_64
--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Running transaction check
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
proxysql x86_64 2.0.6-1 /proxysql-2.0.6-1-centos7.x86_64
35 M
Installing for dependencies:
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k
Transaction Summary
================================================================================
Install 1 Package (+8 Dependent packages)
Total size: 36 M
Total download size: 1.4 M
Installed size: 38 M
Downloading packages:
(1/8): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00
(2/8): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00
(3/8): perl-DBD-MySQL-4.023-6.el7.x86_64.rpm | 140 kB 00:01
(4/8): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:01
(5/8): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00
(6/8): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00
(7/8): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:01
(8/8): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:04
--------------------------------------------------------------------------------
Total 319 kB/s | 1.4 MB 00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 1/9
Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 2/9
Installing : perl-IO-Compress-2.061-2.el7.noarch 3/9
Installing : perl-Data-Dumper-2.145-3.el7.x86_64 4/9
Installing : perl-Net-Daemon-0.48-5.el7.noarch 5/9
Installing : perl-PlRPC-0.2020-14.el7.noarch 6/9
Installing : perl-DBI-1.627-4.el7.x86_64 7/9
Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 8/9
Installing : proxysql-2.0.6-1.x86_64 9/9
warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
Verifying : perl-Net-Daemon-0.48-5.el7.noarch 1/9
Verifying : proxysql-2.0.6-1.x86_64 2/9
Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 3/9
Verifying : perl-IO-Compress-2.061-2.el7.noarch 4/9
Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 5/9
Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 6/9
Verifying : perl-DBI-1.627-4.el7.x86_64 7/9
Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 8/9
Verifying : perl-PlRPC-0.2020-14.el7.noarch 9/9
Installed:
proxysql.x86_64 0:2.0.6-1
Dependency Installed:
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-6.el7
perl-DBI.x86_64 0:1.627-4.el7
perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PlRPC.noarch 0:0.2020-14.el7
Complete!
Enable and start ProxySQL service.
[root@proxysql-01 ~]# systemctl enable proxysql.service
proxysql.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig proxysql on
[root@proxysql-01 ~]# systemctl start proxysql.service
Allow ProxySQL service port 6033/tcp (it is the reverse of MySQL default port 3306) in Linux Firewall.
[root@proxysql-01 ~]# firewall-cmd --permanent --add-port=6033/tcp
success
[root@proxysql-01 ~]# firewall-cmd --reload
success
Configure ProxySQL Load Balancer for MySQL on CentOS 7:
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.
[root@proxysql-01 ~]# 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.
[root@percona-01 ~]# 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)
Configure 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.
[root@proxysql-01 ~]# 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.
We have successfully installed and configured ProxySQL Load Balancer for MySQL on CentOS 7.
0 Comments