blog
proxysql-admin Alternatives – ClusterControl ProxySQL GUI
ProxySQL is a very popular proxy in MySQL environments. It comes with a nice set of features including read/write splitting, query caching and query rewriting. ProxySQL stores its configuration in SQLite database, configuration changes can be applied on runtime and are performed through SQL commands. This increases the learning curve and could be a blocker for some people that would like to just install it and get it running.
This is a reason why a couple of tools exist that can help you to manage ProxySQL. Let’s take a look at one of them, proxysql-admin, and compare it with features available for ProxySQL in ClusterControl.
proxysql-admin
Proxysql-admin is a tool that comes included in the ProxySQL when installed from Percona repositories. It is dedicated to making the setup of Percona XtraDB Cluster in ProxySQL easier. You can define the setup in the configuration file (/etc/proxysql-admin.cnf) or through arguments to the proxysql-admin command. It is possible to:
- Configure hostgroups (reader, writer, backup writer, offline) for PXC
- Create monitoring user in ProxySQL and PXC
- Create application user in ProxySQL and PXC
- Configure ProxySQL (maximum running connections, maximum transactions behind)
- Synchronize users between PXC and ProxySQL
- Synchronize nodes between PXC and ProxySQL
- Create predefined (R/W split) query rules for users imported from PXC
- Configure SSL for connections from ProxySQL to the backend databases
- Define a single writer or round robin access to the PXC
As you can see, this is by no means a complex tool, it focuses on the initial setup. Let’s take a look at couple examples.
root@vagrant:~# proxysql-admin --enable
This script will assist with configuring ProxySQL for use with
Percona XtraDB Cluster (currently only PXC in combination
with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
Configuring the ProxySQL monitoring user.
ProxySQL monitor user name as per command line/config-file is proxysql-monitor
The monitoring user is already present in Percona XtraDB Cluster.
Would you like to enter a new password [y/n] ? n
Monitoring user 'proxysql-monitor'@'10.%' has been setup in the ProxySQL database.
Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
Percona XtraDB Cluster application user name as per command line/config-file is proxysql_user
Application user 'proxysql_user'@'10.%' already present in PXC.
Adding the Percona XtraDB Cluster server nodes to ProxySQL
Write node info
+------------+--------------+------+--------+
| hostname | hostgroup_id | port | weight |
+------------+--------------+------+--------+
| 10.0.0.152 | 10 | 3306 | 1000 |
+------------+--------------+------+--------+
ProxySQL configuration completed!
ProxySQL has been successfully configured to use with Percona XtraDB Cluster
You can use the following login credentials to connect your application through ProxySQL
mysql --user=proxysql_user -p --host=localhost --port=6033 --protocol=tcpAbove shows the initial setup. As you can see, a singlewriter (default) mode was used, monitoring and application users have been configured and the whole server configuration was prepared.
root@vagrant:~# proxysql-admin --status
mysql_galera_hostgroups row for writer-hostgroup: 10
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| writer | reader | backup-writer | offline | active | max_writers | writer_is_also_reader | max_trans_behind |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| 10 | 11 | 12 | 13 | 1 | 1 | 2 | 100 |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
mysql_servers rows for this configuration
+---------------+-------+------------+------+--------+--------+----------+---------+-----------+
| hostgroup | hg_id | hostname | port | status | weight | max_conn | use_ssl | gtid_port |
+---------------+-------+------------+------+--------+--------+----------+---------+-----------+
| writer | 10 | 10.0.0.153 | 3306 | ONLINE | 1000 | 1000 | 0 | 0 |
| reader | 11 | 10.0.0.151 | 3306 | ONLINE | 1000 | 1000 | 0 | 0 |
| reader | 11 | 10.0.0.152 | 3306 | ONLINE | 1000 | 1000 | 0 | 0 |
| backup-writer | 12 | 10.0.0.151 | 3306 | ONLINE | 1000 | 1000 | 0 | 0 |
| backup-writer | 12 | 10.0.0.152 | 3306 | ONLINE | 1000 | 1000 | 0 | 0 |
+---------------+-------+------------+------+--------+--------+----------+---------+-----------+Here is the output of the default configuration of the PXC nodes in ProxySQL.
ClusterControl
ClusterControl is, in comparison to the proxysql-admin, a way more complex solution. It can deploy a ProxySQL load balancer and preconfigure it according to the user requirements.
When deploying you can define administrator user and password, monitoring user and you can as well import one of the existing MySQL users (or create a new one if this is what you need) for the application to use. It is also possible to import ProxySQL configuration from other ProxySQL that you already have in the cluster. It makes the deployment faster and more efficient.
What is also important to mention is that ClusterControl can deploy ProxySQL in both MySQL and Galera Clusters. It can be used with MySQL, Percona and MariaDB flavours of MySQL.
Once deployed, ClusterControl gives you options to fully manage ProxySQL via an easy to use GUI.
You can monitor your ProxySQL instance.
You can check the heavier queries executed through ProxySQL. It is also possible to create a query rule based on the exact query.
ClusterControl configures ProxySQL for a read/write split. It is also possible to add custom query rules based on your requirements and application configuration.
Compared to proxysql-admin, ClusterControl gives you full control over the server configuration. You can add new servers, you can move them around host groups as you want. You can create new hostgroups (and then, for example, create new query rules for them).
It is also possible to manage users in ProxySQL. You can edit existing users, import new users that exist in the backend database.
Bulk import is also possible to accomplish. You can also create new users on both ProxySQL and backend databases.
ClusterControl can also be used to reconfigure ProxySQL. You can modify all of the variables through a simple UI with search option.
As you can see, ClusterControl comes with in-depth management features for ProxySQL. It allows you to deploy and manage ProxySQL instances with ease.