PXC-Operator

Overview

The source code and default configuration of the Building Block is available in our code.sysEleven.de.

PXC Operator

The PXC Operator (Percona XtraDB Cluster) is an open source enterprise MySQL solution.

Infos on release notes and new features please follow Release notes pxc-operator

This building block will deploy a Percona XtraDB cluster with HAProxy as a load balancing frontend.

Using the supplied configuration possibilities, you can configure all settings for the cluster itself as well as manage databases and users inside the cluster.

Prerequisites on PXC-Operator

You need to provide a storage provider for this Building Block. Proceed with the following prerequisite description to use the Velero Building Block out of the box.

Recommended Setup

A recommended resource overview is listed in the table below.

CPU / vCPU	Memory
6.7	9728MiB

No further activities need to be carried out in advance.

Adding the Building Block

Add the directory syseleven-pxc-operator to your control repository. Add a .gitlab-ci.yml to the directory with the following content:

include:
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobDevelopment.yaml
    ref: 3.50.3
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobStaging.yaml
    ref: 3.50.3
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobProduction.yaml
    ref: 3.50.3

Remove environments you are not using by removing their include.

Configuring the building block

The building block consists of multiple helm charts that can be configure individually.
Look at the helmfile.yaml in the releases.[].values section. The configuration of each helm chart can be overriden in those files in the specified hierarchy.

There are some default values that the building block will set for the upstream helm chart.

Required configuration

Set the following sections in one of those files:

• values-pxc-db.yaml
• values-pxc-db-$ENVIRONMENT.yaml

Configuring backups

You have to set

backup:
  storages:
    s3:
      s3:
        credentialsAccessKey: ...
        credentialsSecretKey: ...

If you can't, please disable the backup schedule with

backup:
  schedule: []

Credentials

You have to set the following variables otherwise they will be changed on every run.

secrets:
  passwords:
    root: ...
    xtrabackup: ...
    monitor: ...
    clustercheck: ...
    proxyadmin: ...
    pmmserver: ...
    operator: ...
    replication: ...

Users and database management (optional)

Specify your values in the following helm values files:

• values-mysql-config.yaml
• values-mysql-config-${ENVIRONMENT}.yaml

Parameters

The following table lists the configurable parameters for the management of the mysql-config chart and their default values.

Parameter	Description	Default
schedule	The cron schedule for the job that creates the users and databases	*/15 * * * *
target.host	The MySQL server on which to manage databases and users	pxc-db-haproxy
target.user	The user with which users and databases are created	root
target.existingSecret	The password for target.user	"pxc-db"
target.existingSecretKey	The key in target.existingSecret where the password for target.user is stored	"root"
databases	The databases to manage. See configuration for details	[]
users	The users to manage. See configuration for details	[]

Default parameters like e.g. the image and tolerations are available, too. See the values.yaml file for those parameters.

Configuration

The user and database creation is done with ansible, so you can specify any parameters that the ansible modules support. See

• databases: community.mysql.mysql_db
• users: community.mysql.mysql_user

Example:

databases:
  - name: forms
    state: present
  - name: web
    encoding: utf8mb4
    state: present

users:
  - name: 'web'
    host: '%'
    # We recommend using already hashed passwords. Please check the notes in the ansible module for the supported hash modules
    password: '*94BDCEBE19083CINVALIDEFD02F964C7AF3CFC29'
    priv: 'web.*:ALL'
    state: present
  - name: 'old_user'
    host: '%'
    password: '*94BDCEBE19083CINVALIDEFD02F964CFOOO21319'
    priv: 'web.*:ALL'
    state: absent

Backup Cleanup

This Building Blocks ships a cronjob that deletes s3 backup files older than a certain age (default 14d).

By default, PXC-operator does not cleanup backup files stored in s3 (see).
This might lead to saturation of your S3 quota and other applications using this quota might break.

You can disable this cronjob by setting backupcleanup.enabled=false in pxc-db-*.yaml. See pxc-operator-extension/values.yaml for further configuration options and default values.

Local Storage (optional, but strongly recommended for performance)

You have to deploy a local storage class provisioner or change pxc.persistence.storageClass. A possible local storage class provisoner is local-path-provisioner.

See how to use pxc-operator with local storage for more information

Known issues

• In our experience PXC is more suitable to run on Kubernetes than a traditional Primary/Secondary-MySQL setup, nevertheless PXC comes with a few limitations that you should be aware of.
• The performance of a database often depends on the performance of the underlying storage and PXC is no exception. While our distributed storage provides high availability, the added network latency can lead to a measurable performance impact on the database. As a solution we recommend to use our local storage nodes instead. This way you get the best performance and the high availability is still provided by the database cluster itself.
• As the upstream Helm chart of pxc-operator hasn't implemented the support of cluster wide operation, the operator needs to be deployed to each namespace which should have a database cluster.
• Since Upstream does not support adding sidecars to the database pods, we currently do not provide metrics or alerts for MySQL databases within this Building Block. Since Percona is about to add sidecar support soon we will add collecting metrics and provide alerts in a future release. Until then, we recommend to use the community helm chart prometheus-mysql-exporter or PMM. We decided against PMM since we want to integrate the monitoring with the Building Block kube-prometheus-stack to provide a seamless experience.

Version 3.40.0 - wrong nomenclature when creating clusterSecretName

Version 3.40.0 has a mismatch on the pxc.SecretName, which is used to distribute the credentials on the cluster deployment.
This is fixed on version 3.40.1.
As a workaround on version 3.40.0, you can specify the common name via the values-pxc-db.yaml or corresponding environment-files by adding the following values:

pxc:
  clusterSecretName: pxc-db

FAQ

Backup fails with 'AccessDenied' error

Please check if this bucket already exists. Object storage buckets need to be unique within a region.

Monitoring

Additional alertrules

• None

Additional Grafana dashboards

• None

Scaling Setup

This building block consists of multiple components. Each of the components can and must be scaled individually.

PXC-Operator

• Scaling replicas is not supported
• Requests/limits for CPU/memory can be adjusted

PXC Cluster

• Replicas should always be set to a 2N+1
• Requests/limits for CPU/memory can be adjusted
• When setting CPU/memory limits, also adjust your my.cnf accordingly (See upstream documentation)

Release-Notes

Please find more infos on release notes and new features Release notes PXC Operator