CentLinux

Sunday, 29 September 2019

Install ProxySQL Load Balancer for MySQL on CentOS 7

Install ProxySQL Load Balancer for MySQL on CentOS 7

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.

 

Table of Contents:

 Install ProxySQL Load Balancer for MySQL on CentOS 7

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.

# 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 server.

# 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 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.

# 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)

 

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.

# 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.

If you find this article useful? Consider supporting us by Buy Me A Coffee


Read more

Subscribe via email

 

You May Also Like These:

By at
Labels:

4 comments:

  1. Unknown30 May 2020 at 21:04

    Hi,
    this configuration is useful tu use only one server at time if i have master master replication on place or both server working to the same time with load balancing?

    thank you

    ReplyDelete
    Replies
    1. Ahmer M30 May 2020 at 22:12

      The same configuration is good for clustered environment. In fact the above article is written by using a Percona XtraDB cluster.

      Delete
  2. Anonymous24 September 2020 at 16:52

    Hi, Ahmer, thanks for this documentation, it's really helpful.. I have problem logging back in to ProxySQL shell after logging out. What do you think could cause that? It says ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)

    Also after i follow the steps above, i got (Access denied for user 'monitor'@'192.168.1.26' (using password: YES) ) and connect_success_time_us (0)

    Any advice?

    Thanks

    ReplyDelete
    Replies
    1. Ahmer M26 September 2020 at 13:51

      It looks like the user admin and monitor do not have remote login permissions.
      Make sure that you have create user admin@'%' and monitor@'%' as mentioned in above guide.

      Delete

Subscribe to: Post Comments (Atom)
© 2023 CentLinux. All Rights Reserved.