Configuring Load Balancing with ProxySQL

Configuring Load Balancing with ProxySQL

Percona XtraDB Cluster Operator provides a choice of two cluster components to provide load balancing and proxy service: you can use either HAProxy or ProxySQL. You can control which one to use, if any, by enabling or disabling via the haproxy.enabled and proxysql.enabled options in the deploy/cr.yaml configuration file.

Use the following command to enable ProxySQL:

kubectl patch pxc cluster1 --type=merge --patch '{
  "spec": {
     "proxysql": {
        "enabled": true,
        "size": 3,
        "image": "percona/percona-xtradb-cluster-operator:1.7.0-proxysql" },
     "haproxy": { "enabled": false }
  }}'

Note

For obvious reasons the Operator will not allow the simultaneous enabling of both HAProxy and ProxySQL.

The resulting setup will use the number zero Percona XtraDB Cluster member (cluster1-pxc-0 by default) as writer.

When a cluster with ProxySQL is upgraded, the following steps take place. First, reader members are upgraded one by one: the Operator waits until the upgraded member shows up in ProxySQL with online status, and then proceeds to upgrade the next member. When the upgrade is finished for all the readers, then the writer Percona XtraDB Cluster member is finally upgraded.

Note

when both ProxySQL and Percona XtraDB Cluster are upgraded, they are upgraded in parallel.

Passing custom configuration options to ProxySQL

You can pass custom configuration to ProxySQL using the proxysql.configuration key in the deploy/cr.yaml file.

Note

If you specify a custom ProxySQL configuration in this way, the Operator doesn’t provide its own ProxySQL configuration file. That’s why you should specify either a full set of configuration options or nothing.

Here is an example of ProxySQL configuration passed through deploy/cr.yaml:

...
proxysql:
  enabled: false
  size: 3
  image: percona/percona-xtradb-cluster-operator:1.7.0-proxysql
  configuration: |
    datadir="/var/lib/proxysql"

    admin_variables =
    {
      admin_credentials="proxyadmin:admin_password"
      mysql_ifaces="0.0.0.0:6032"
      refresh_interval=2000

      cluster_username="proxyadmin"
      cluster_password="admin_password"
      cluster_check_interval_ms=200
      cluster_check_status_frequency=100
      cluster_mysql_query_rules_save_to_disk=true
      cluster_mysql_servers_save_to_disk=true
      cluster_mysql_users_save_to_disk=true
      cluster_proxysql_servers_save_to_disk=true
      cluster_mysql_query_rules_diffs_before_sync=1
      cluster_mysql_servers_diffs_before_sync=1
      cluster_mysql_users_diffs_before_sync=1
      cluster_proxysql_servers_diffs_before_sync=1
    }

    mysql_variables=
    {
      monitor_password="monitor"
      monitor_galera_healthcheck_interval=1000
      threads=2
      max_connections=2048
      default_query_delay=0
      default_query_timeout=10000
      poll_timeout=2000
      interfaces="0.0.0.0:3306"
      default_schema="information_schema"
      stacksize=1048576
      connect_timeout_server=10000
      monitor_history=60000
      monitor_connect_interval=20000
      monitor_ping_interval=10000
      ping_timeout_server=200
      commands_stats=true
      sessions_sort=true
      have_ssl=true
      ssl_p2s_ca="/etc/proxysql/ssl-internal/ca.crt"
      ssl_p2s_cert="/etc/proxysql/ssl-internal/tls.crt"
      ssl_p2s_key="/etc/proxysql/ssl-internal/tls.key"
      ssl_p2s_cipher="ECDHE-RSA-AES128-GCM-SHA256"
    }
...

Accessing the ProxySQL Admin Interface

You can use ProxySQL admin interface to configure its settings.

Configuring ProxySQL in this way means connecting to it using the MySQL protocol, and two things are needed to do it:

  • the ProxySQL Pod name
  • the ProxySQL admin password

You can find out ProxySQL Pod name with the kubectl get pods command, which will have the following output:

$ kubectl get pods
NAME                                              READY   STATUS    RESTARTS   AGE
cluster1-pxc-node-0                               1/1     Running   0          5m
cluster1-pxc-node-1                               1/1     Running   0          4m
cluster1-pxc-node-2                               1/1     Running   0          2m
cluster1-proxysql-0                               1/1     Running   0          5m
percona-xtradb-cluster-operator-dc67778fd-qtspz   1/1     Running   0          6m

The next command will print you the needed admin password:

kubectl get secrets $(kubectl get pxc -o jsonpath='{.items[].spec.secretsName}') -o template='{{ .data.proxyadmin | base64decode }}'

When both Pod name and admin password are known, connect to the ProxySQL as follows, substituting cluster1-proxysql-0 with the actual Pod name and admin_password with the actual password:

kubectl exec -it cluster1-proxysql-0 -- mysql -h127.0.0.1 -P6032 -uproxyadmin -padmin_password

.

Download PDF Manual

Table Of Contents

Previous topic

Configuring Load Balancing with HAProxy

Next topic

Transport Layer Security (TLS)

Contact Us

For free technical help, visit the Percona Community Forum.
To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or professional services, contact Percona Sales.