Skip to content
logo
Percona Operator for MySQL
Changing MySQL Options
Initializing search
    percona/k8spxc-docs
    percona/k8spxc-docs
    • Welcome
      • System Requirements
      • Design and architecture
      • Comparison with other solutions
      • Install with Helm
      • Install with kubectl
      • Install on Minikube
      • Install on Google Kubernetes Engine (GKE)
      • Install on Amazon Elastic Kubernetes Service (AWS EKS)
      • Install on Microsoft Azure Kubernetes Service (AKS)
      • Install on OpenShift
      • Generic Kubernetes installation
      • Multi-cluster and multi-region deployment
      • Application and system users
      • Changing MySQL Options
        • Edit the deploy/cr.yaml file
        • Use a ConfigMap
        • Use a Secret Object
        • Make changed options visible to Percona XtraDB Cluster
        • Auto-tuning MySQL options
      • Anti-affinity and tolerations
      • Labels and annotations
      • Local Storage support
      • Defining environment variables
      • Load Balancing with HAProxy
      • Load Balancing with ProxySQL
      • Transport Encryption (TLS/SSL)
      • Data at rest encryption
      • Telemetry
      • Backup and restore
      • Upgrade Database and Operator
      • Horizontal and vertical scaling
      • Monitor with Percona Monitoring and Management (PMM)
      • Add sidecar containers
      • Restart or pause the cluster
      • Crash recovery
      • Debug and troubleshoot
      • How to install Percona XtraDB Cluster in multi-namespace (cluster-wide) mode
      • How to upgrade Percona XtraDB Cluster manually
      • How to use private registry
      • Custom Resource options
      • Percona certified images
      • Operator API
      • Frequently Asked Questions
      • Old releases (documentation archive)
      • Release notes index
      • Percona Operator for MySQL based on Percona XtraDB Cluster 1.12.0 (2022-12-07)
      • Percona Operator for MySQL based on Percona XtraDB Cluster 1.11.0 (2022-06-03)
      • Percona Distribution for MySQL Operator 1.10.0 (2021-11-24)
      • Percona Distribution for MySQL Operator 1.9.0 (2021-08-09)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.8.0 (2021-05-26)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.7.0 (2021-02-02)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.6.0 (2020-09-09)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.5.0 (2020-07-21)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.4.0 (2020-04-29)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.3.0 (2020-01-06)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.2.0 (2019-09-20)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.1.0 (2019-07-15)
      • Percona Kubernetes Operator for Percona XtraDB Cluster 1.0.0 (2019-05-29)

    • Edit the deploy/cr.yaml file
    • Use a ConfigMap
    • Use a Secret Object
    • Make changed options visible to Percona XtraDB Cluster
    • Auto-tuning MySQL options

    Changing MySQL Options¶

    You may require a configuration change for your application. MySQL allows the option to configure the database with a configuration file. You can pass options from the my.cnf configuration file to be included in the MySQL configuration in one of the following ways:

    • edit the deploy/cr.yaml file,

    • use a ConfigMap,

    • use a Secret object.

    Often there’s no need to add custom options, as the Operator takes care of providing MySQL with reasonable defaults. Also, some MySQL options can not be changed: you shouldn’t change require_secure_transport option to ON, as it would break the behavior of the Operator.

    Note

    If you still need something equal to require_secure_transport=ON to force encrypted connections between client and server, the most convenient workaround would be creating MySQL users with REQUIRE SSL option.

    Edit the deploy/cr.yaml file¶

    You can add options from the my.cnf configuration file by editing the configuration section of the deploy/cr.yaml. Here is an example:

    spec:
      secretsName: cluster1-secrets
      pxc:
        ...
          configuration: |
            [mysqld]
            wsrep_debug=CLIENT
            [sst]
            wsrep_debug=CLIENT
    

    See the Custom Resource options, PXC section for more details.

    Use a ConfigMap¶

    You can use a configmap and the cluster restart to reset configuration options. A configmap allows Kubernetes to pass or update configuration data inside a containerized application.

    Use the kubectl command to create the configmap from external resources, for more information see Configure a Pod to use a ConfigMap.

    For example, let’s suppose that your application requires more connections. To increase your max_connections setting in MySQL, you define a my.cnf configuration file with the following setting:

    [mysqld]
    ...
    max_connections=250
    

    You can create a configmap from the my.cnf file with the kubectl create configmap command.

    You should use the combination of the cluster name with the -pxc suffix as the naming convention for the configmap. To find the cluster name, you can use the following command:

    $ kubectl get pxc
    

    The syntax for kubectl create configmap command is:

    $ kubectl create configmap <cluster-name>-pxc <resource-type=resource-name>
    

    The following example defines cluster1-pxc as the configmap name and the my.cnf file as the data source:

    $ kubectl create configmap cluster1-pxc --from-file=my.cnf
    

    To view the created configmap, use the following command:

    $ kubectl describe configmaps cluster1-pxc
    

    Use a Secret Object¶

    The Operator can also store configuration options in Kubernetes Secrets. This can be useful if you need additional protection for some sensitive data.

    You should create a Secret object with a specific name, composed of your cluster name and the pxc suffix.

    Note

    To find the cluster name, you can use the following command:

    $ kubectl get pxc
    

    Configuration options should be put inside a specific key inside of the data section. The name of this key is my.cnf for Percona XtraDB Cluster Pods.

    Actual options should be encoded with Base64.

    For example, let’s define a my.cnf configuration file and put there a pair of MySQL options we used in the previous example:

    [mysqld]
    wsrep_debug=CLIENT
    [sst]
    wsrep_debug=CLIENT
    

    You can get a Base64 encoded string from your options via the command line as follows:

    $ cat my.cnf | base64 --wrap=0
    
    $ cat my.cnf | base64
    

    Note

    Similarly, you can read the list of options from a Base64 encoded string:

    $ echo "W215c3FsZF0Kd3NyZXBfZGVidWc9T04KW3NzdF0Kd3NyZXBfZGVidWc9T04K" | base64 --decode
    

    Finally, use a yaml file to create the Secret object. For example, you can create a deploy/my-pxc-secret.yaml file with the following contents:

    apiVersion: v1
    kind: Secret
    metadata:
      name: cluster1-pxc
    data:
      my.cnf: "W215c3FsZF0Kd3NyZXBfZGVidWc9T04KW3NzdF0Kd3NyZXBfZGVidWc9T04K"
    

    When ready, apply it with the following command:

    $ kubectl create -f deploy/my-pxc-secret.yaml
    

    Note

    Do not forget to restart Percona XtraDB Cluster to ensure the cluster has updated the configuration.

    Make changed options visible to Percona XtraDB Cluster¶

    Do not forget to restart Percona XtraDB Cluster to ensure the cluster has updated the configuration (see details on how to connect in the Install Percona XtraDB Cluster on Kubernetes page).

    Auto-tuning MySQL options¶

    Few configuration options for MySQL can be calculated and set by the Operator automatically based on the available Pod resource limits (memory and CPU) if constant values for these options are not specified by user (either in CR.yaml or in ConfigMap).

    Options which can be set automatically are the following ones:

    • innodb_buffer_pool_size

    • max_connections

    If Percona XtraDB Cluster Pod limits are defined, then limits values are used to calculate these options. If Percona XtraDB Cluster Pod limits are not defined, auto-tuning is not done.

    Also, starting from the Operator 1.12.0, there is another way of auto-tuning. You can use "{{ containerMemoryLimit }}" as a value in spec.pxc.configuration as follows:

    pxc:
        configuration: |
        [mysqld]
        innodb_buffer_pool_size={{containerMemoryLimit * 3 / 4}}
        ...
    

    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 consulting services , contact Percona Sales.


    Last update: 2023-02-09
    Back to top
    Percona LLC and/or its affiliates, © 2009 - 2022
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.