Changing MySQL Options

The Operator can tune MySQL for you, or you can pass your own options from a my.cnf configuration file. Read the following sections before you choose a method.

Choose automatic tuning or your own configuration

These settings aren’t independent. Setting any configuration of your own disables the Operator’s automatic configuration tuning, even if you only set one unrelated option:

Approach	What controls the configuration
Nothing set	The Operator’s basic auto-tuning (innodb_buffer_pool_size and max_connections only)
spec.mysql.autoConfig.enabled: true, no manual configuration	The Operator calculates a full configuration from your resources and workload profile
spec.mysql.configuration, a ConfigMap, or a Secret	Your values only

To let the Operator tune MySQL for you, see Automatic configuration tuning.

Configuration compatibility considerations

The Operator passes configuration options you define to MySQL as-is. It doesn’t check them for typos or for compatibility with your replication type. An incompatible value can stop replication or prevent it from starting. The Custom Resource is still accepted.

Before you apply a change, check the variable’s requirements and restrictions for your topology:

• MySQL Group Replication requirements
• CHANGE REPLICATION SOURCE TO
• Percona Server for MySQL

For example, Group Replication requires log_replica_updates=ON so members record replicated transactions in their binary logs for distributed recovery. Setting it to OFF is incompatible with Group Replication.

How the Operator applies your configuration

The Operator applies your configuration to all MySQL Pods as follows:

• Dynamic variables. The Operator applies these variables at runtime with the SET GLOBAL command and doesn’t restart the MySQL StatefulSet. See Dynamic configuration.
• Static variables. MySQL cannot change these variables while the server is running. The Operator applies them with a rolling restart of the MySQL StatefulSet.
• Removed variables. The Operator applies the removal with a rolling restart of the MySQL StatefulSet.

Pass your own options in one of the following ways:

• Edit the deploy/cr.yaml file
• Use a ConfigMap
• Use a Secret

The Operator applies your configuration to all MySQL Pods as follows:

• Dynamic variables — These variables are applied at runtime with the SET GLOBAL command. The Operator doesn’t restart the MySQL StatefulSet. See Dynamic configuration for details.
• Static variables are variables that MySQL cannot change while the server is running. Applying them requires the rolling restarts of the MySQL StatefulSet.
• If any variable is removed, the Operator applies the changes with a rolling restart of the MySQL StatefulSet.

Configuration compatibility considerations

The Operator passes configuration options you define to MySQL as-is. It does not check them for typos or for compatibility with your replication type. An incompatible value can stop replication or prevent it from starting. The Custom Resource is still accepted.

Before you apply a change, check the variable’s requirements and restrictions for your topology in the MySQL Group Replication requirements , CHANGE REPLICATION SOURCE TO , and Percona Server for MySQL documentation.

For example, Group Replication requires log_replica_updates=ON so members record replicated transactions in their binary logs for distributed recovery. Setting it to OFF is incompatible with Group Replication.

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: ps-cluster1-secrets
  mysql:
    ...
      configuration: |
        max_connections=250

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

Use a ConfigMap

You can use a ConfigMap to pass 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:

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 -mysql suffix as the naming convention for the configmap. To find the cluster name, you can use the following command:

kubectl get ps

The syntax for kubectl create configmap command is:

kubectl create configmap <cluster-name>-mysql <resource-type=resource-name>

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

kubectl create configmap ps-cluster1-mysql --from-file=my.cnf

To view the created configmap, use the following command:

kubectl describe configmaps ps-cluster1-mysql

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 mysql suffix.

Note

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

kubectl get ps

Configuration options should be put inside a specific key inside of the data section. The name of this key is my.cnf for Percona Server for MySQL 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:

max_connections=250

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

in Linuxin macOS

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 "bWF4X2Nvbm5lY3Rpb25zPTI1MAo" | base64 --decode

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

apiVersion: v1
kind: Secret
metadata:
  name: ps-cluster1-mysql
data:
  my.cnf: "bWF4X2Nvbm5lY3Rpb25zPTI1MAo"

When ready, apply it with the following command:

kubectl create -f deploy/mysql-secret.yaml

Note

The Operator applies Secret changes automatically. Dynamic variables take effect without a restart. Static variables or variables you remove from the Secret trigger a rolling restart.

Dynamic configuration

Version added: 1.3.0

Some MySQL system are dynamic and you can change them at runtime while the server is running.

Use dynamic updates when you need to tune a live cluster without a rolling restart. Typical cases include raising max_connections during peak traffic, adjusting I/O-related settings or enabling query logging to investigate an incident.

Warning

The Operator does not validate options against your replication type. Dynamic updates skip a rolling restart, so an incompatible value takes effect on the running cluster. Review Configuration compatibility considerations before you apply a change.

How the Operator treats dynamic options

When you change the Custom Resource, ConfigMap, or Secret, the Operator compares the new configuration with the last configuration it applied.

• If the values already match, the Operator does nothing.
• If you add or change a dynamic variable, the Operator runs the SET GLOBAL command on every ready MySQL Pod. Pods keep running.
• If MySQL rejects the change because the variable is read-only or cannot be modified while Group Replication is running, the Operator rolls the MySQL StatefulSet so Pods reload the configuration file.
• If you remove a variable, the Operator restarts Pods so MySQL falls back to the server default.

The Operator waits until all MySQL Pods are ready before it runs SET GLOBAL. If a variable name is unknown to MySQL, the Operator logs an error and does not restart Pods. Check the Operator logs and fix the configuration.

The Operator does not watch live GLOBAL values on the server. If you connect to a Pod and define a dynamic variable by running the SET GLOBAL command yourself, the Operator leaves that change in place until you update the declared configuration. A later Pod restart reloads the configuration file, so a manual change that is not in the Custom Resource, ConfigMap, or a Secret is lost.

To keep a value you already set with SET GLOBAL, add it to the Custom Resource, ConfigMap or a Secret. The Operator applies SET GLOBAL again and does not restart Pods.

Auto-tuning MySQL options

The Operator calculates a few MySQL options automatically based on the memory available to the MySQL container:

• innodb_buffer_pool_size — about 50% of the container memory,
• innodb_buffer_pool_chunk_size — set together with innodb_buffer_pool_size, so the buffer pool size is a valid multiple of the chunk size,
• max_connections — one connection per ~12 MiB of container memory.

The Operator uses the memory limit from mysql.resources.limits. If no limit is set, it uses the memory request from mysql.resources.requests. If neither is set, auto-tuning is not done.

The Operator doesn’t auto-tune an option you set yourself in spec.mysql.configuration. If you set an option in your own ConfigMap or Secret, your value takes precedence over the auto-tuned one.

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

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

Starting with Operator 1.3.0, a more comprehensive form of automatic tuning is available. See Automatic configuration tuning.

The tuning of the buffer pool and max_connections is what the Operator continues to use whenever that feature is disabled (spec.mysql.autoConfig.enabled: false or unset). This behavior remains and applies to any cluster that doesn’t opt in.

---

Last update: October 27, 2026
Created: September 19, 2022