EmergencyEMERGENCY? Get 24/7 Help Now!

ProxySQL Admin Configuration

and  | January 25, 2017 |  Posted In: Percona Software, Percona XtraDB Cluster, ProxySQL

PREVIOUS POST
NEXT POST

ProxySQL AdminProxySQL Admin (proxysql-admin) is a powerful tool for configuring Percona XtraDB Cluster nodes into ProxySQL. The proxysql-admin tool comes with the ProxySQL package from Percona apt/yum repositories.

ProxySQL 1.3.2-1 is now available from the Percona repositories. This release is based on ProxySQL v1.3.2a and introduces the following new changes: proxysql-admin_v1.3.2a.md.

Installing on Red Hat or CentOS

If you are running an RPM-based Linux distribution, use the yum package manager to install ProxySQL and proxysql-admin from the official Percona software repository.

First, if your system does not already have the Percona’s yum repository configured, please run the following command:

Next, install the proxysql/proxysql-admin package:

Installing on Debian or Ubuntu

If you are running a DEB-based Linux distribution, use the apt package manager to install ProxySQL and proxysql-admin from the official Percona software repository.

First, if your system does not already have the Percona’s apt repository configured, please fetch the repository package:

Next, install the repository package:

Then update the local apt cache:

Finally, install the proxysql/proxysql-admin package:

Pre-Requisites

  • ProxySQL and Percona XtraDB Cluster should be up and running.
  • For security purposes, please make sure to change the default user settings in the ProxySQL configuration file. It is recommended that you use config-file to run the proxysql-admin script. Default –config-file location is /etc/proxysql-admin.cnf.

This script will accept two different options to configure Percona XtraDB Cluster nodes:

1) −−enable

This option configures Percona XtraDB Cluster nodes into the ProxySQL database, and add two cluster monitoring scripts into the ProxySQL scheduler table for checking the cluster status.

_scheduler script info:

  • proxysql_node_monitor: checks cluster node membership, and re-configures ProxySQL if cluster membership changes occur
  • proxysql_galera_checker: checks desynced nodes, and temporarily deactivates them. It will also add two new users into the Percona XtraDB Cluster with the USAGE privilege. One monitors cluster nodes through ProxySQL, and the other connects to Cluster node via the ProxySQL console. Please make sure to use super user credentials from Percona XtraDB Cluster to setup the default users.

2. −−disable

This option removes Percona XtraDB Cluster nodes from ProxySQL and stops the ProxySQL monitoring daemon.

Extra options

i) −−mode

This option sets up read/write mode for Percona XtraDB Cluster nodes in the ProxySQL database, based on the hostgroup. For now, the only supported modes are loadbal and singlewrite. singlewrite is the default mode, and it accepts writes only on one single node (and this node can be provided either interactively or by using the –write-node to specify the hostname and the port number for the one single write node). All other remaining nodes will be read-only and accept only read statements. The mode loadbal, on the other hand, is a load balanced set of evenly weighted read/write nodes.

singlewrite mode setup:

loadbal mode setup:

ii) −−node-check-interval

This option configures the interval for monitoring via the proxysql_galera_checker script (in milliseconds):

iii) −−adduser

This option aids with adding the Percona XtraDB Cluster application user to the ProxySQL database:

iv) −−test-run

This option sets up a test/dummy proxysql configuration:

We hope you enjoy ProxySQL Admin!

PREVIOUS POST
NEXT POST
Ramesh Sivaraman

Ramesh joined the Percona QA Team in March 2014. He has almost 7 years of experience in database administration and, prior to joining Percona, was giving MySQL database support to various service and product based internet companies. Ramesh's professional interests include writing shell/perl script to automate routine tasks and new technology. Ramesh lives in Kerala, the southern part of India, close to his family.

Roel Van de Paar

Roel leads Percona's QA team. Before coming to Percona, he contributed significantly to the QA infrastructure at Oracle. Roel has a varied background in IT, backed up by many industry leading certifications. He also enjoys time with God, his wife and 5 children, or heading into nature.

4 Comments

  • It seems confusing to reference proxysql-admin as a package since it’s a script inside the proxysql package from Percona repository.

  • It would be great to have some better explanation of the admin config.

    #proxysql-admin credentials
    export PROXYSQL_USERNAME=”admin”
    export PROXYSQL_PASSWORD=”admin”
    export PROXYSQL_HOSTNAME=”localhost”
    export PROXYSQL_PORT=”6032″
    export CLUSTER_USERNAME=”admin”
    export CLUSTER_PASSWORD=”admin”
    export CLUSTER_HOSTNAME=”localhost”
    export CLUSTER_PORT=”3306″
    export MONITOR_USERNAME=”monitor”
    export MONITOR_PASSWORD=”monit0r”
    export CLUSTER_APP_USERNAME=”proxysql_user”
    export CLUSTER_APP_PASSWORD=”passw0rd”
    export WRITE_HOSTGROUP_ID=”10″
    export READ_HOSTGROUP_ID=”11″
    export MODE=”singlewrite”

    I’ve been having some difficulty connecting to my Percona cluster because it’s not clear the different between the “CLUSTER_APP” account and “CLUSTER_USERNAME”. I see this error in the log file:

    2017-02-02T20:17:21.624590Z 50765 [Note] Aborted connection 50765 to db: ‘unconnected’ user: ‘monitor’ host: ‘192.168.1.201’ (Got an error reading communication packets)

    Any help or a push in the right direction would be a big help.

    thanks!

    • Hi Ken,

      We have updated blog with script usage information.

      CLUSTER_USERNAME variable is used for connecting to the Percona XtraDB Cluster node for ProxySQL configuration. It is important that this user is present in the Cluster and that the super user account is being used to setup the configuration.

      CLUSTER_APP_USERNAME is a ProxySQL application username for connecting to the Percona XtraDB Cluster node. proxysql-admin tool will create this user in Percona XtraDB Cluster. If this user is already present proxysql-admin tool will skip the user creation.

Leave a Reply