Running MySQL on Kubernetes with Oracle operator

Image with the MySQL logo

Kubernetes support for stateful applications has matured significantly in recent years. It is now possible to localize your database in your cluster so that it can take advantage of the same scalability as your other workloads.

MySQL is one of the most popular relational database engines and has now been extended with an official Kubernetes operator. Guided by the oracle open source project provides an easy way to create managed MySQL clusters within Kubernetes.

In this article, you’ll learn how to install the operator and get started provisioning a database. The operator automatically creates services so that applications in your other containers can connect to MySQL.

What is the MySQL operator?

Oracle’s MySQL operator is a component that runs within your cluster to automate database initialization. You don’t need the operator to use MySQL in Kubernetes – you can use the official container image yourself by using a StatefulSet. However, this approach is cumbersome and requires you to create and maintain long manifest files to create a reliable environment.

The operator offers: a set of custom resources that you can use to create your databases. Add one InnoDBCluster object to your Kubernetes cluster, asks the operator to set up StatefulSets, storage, and networking for you. It also automates upgrades and backups, greatly reducing the burden on administrators.

Installing the MySQL Operator

The supplied stir chart is the easiest way to install the operator in your cluster. Manifest files are available alternatively if you don’t have Helm in your area.

First add the operator to your Helm repository list:

$ helm repo add mysql-operator https://mysql.github.io/mysql-operator/

Then update Helm’s repository database to discover the available charts:

$ helm repo update

Now use the following command to install the operator in a new namespace called mysql-operator:

$ helm install mysql-operator mysql-operator/mysql-operator \
    --namespace mysql-operator \
    --create-namespace
NAME: mysql-operator
LAST DEPLOYED: Sat Oct 29 15:00:26 2022
NAMESPACE: mysql-operator
STATUS: deployed
REVISION: 1
TEST SUITE: None

It may take a few seconds for the process to complete.

Create a secret for root user credentials

Each database cluster you create must be accompanied by a Kubernetes secret that contains the MySQL root user credentials. The operator creates the root privileged account with the username and password specified in secret.

Copy the following YAML manifest and change the value of the rootPassword field to something safe. Change the username and user host settings also if applicable to your situation. Remember that the account mentioned here has total control over MySQL; you will later need to set up individual users for your applications using the regular MySQL shell.

apiVersion: v1
kind: Secret
metadata:
  name: mysql-root-user
stringData:
  rootHost: "%"
  rootUser: "root"
  rootPassword: "P@$$w0rd"

Save the file as secret.yaml. Now use Kubectl to add the secret to your cluster:

$ kubectl apply -f secret.yaml
secret/mysql-root-user created

Create a base cluster

Then create a new YAML file named mysql.yaml and copy the following content:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1

This defines an object using the InnoDBCluster custom resource provided by the MySQL operator. Applying the manifest to your cluster creates a new MySQL database with automatically configured replication. Review and change the following properties in the manifest before continuing:

  • spec.secretName – This must correspond to the metadata.name of the secret you made before. The referenced secret is read to set the MySQL root user account.
  • spec.instances – This defines how many MySQL replicas (Pods) to run. Up to 10 replicas are currently supported.
  • spec.tlsUseSelfSigned – The field is set to true in this example to use the supplied self-signed TLS certificate. You can optionally provide your own certificate via a Kubernetes secret by the spec.tlsSecretName/spec.tlsCASecretName fields.
  • spec.router.instances – The MySQL Router component is responsible for redirecting the service traffic between the available MySQL replicas. This field defines how many instances of the router to run. Multiple instances will improve performance and resiliency in high traffic situations.

Use Kubectl to apply the manifest and create your database cluster:

$ kubectl apply -f mysql.yaml
innodbcluster.mysql.oracle.com/mysql-cluster created

The MySQL initialization process can take several minutes. The operator uses several init containers to set up user accounts and configure the MySQL data directory. Wait a minute before you run kubectl get pods to check what’s running:

$ kubectl get pods
NAME                                    READY   STATUS    RESTARTS   AGE
mysql-cluster-0                         2/2     Running   0          2m
mysql-cluster-1                         2/2     Running   0          2m
mysql-cluster-2                         2/2     Running   0          2m
mysql-cluster-router-6b68f9b5cb-wbqm5   1/1     Running   0          2m

The three MySQL replicas and a single router instance are all located in the Running state. The database is now ready for use.

Connecting to your cluster

The MySQL operator creates a Kubernetes service that routes traffic from your application pods to your database. The service is assigned a hostname in the following format:

<cluster-name>.<namespace-name>.svc.cluster.local

The correct hostname for the sample cluster shown above is mysql-cluster.default.svc.cluster.local. Configure your applications to connect to MySQL at this address using port 3306 and the user credentials you have defined in your secret.

Remotely access your database

You can access MySQL from outside your cluster using Kubectl’s port forwarding capabilities. Run the following command to open a new port forwarding session:

$ kubectl port-forward service/mysql-cluster 3306

To replace mysql-cluster with the name of the InnoDBCluster you want to connect to. Now you can use your local tools to interact with your database:

$ mysql -h127.0.0.1 -u root -p

By pressing Ctrl+C in the terminal window with the kubectl port-forward command will close the connection.

Adjust MySQL server settings

You can use any MySQL configuration file options that your application requires by the spec.mycnf field in you InnoDBCluster manifest of the object:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
spec:
  # ...
  mycnf: |
    [mysqld]
    max_connections=500
    innodb_ft_min_token_size=5

The operator uses the value of this field to return the . to write my.cnf file in the file system of your Pods database.

Set the storage capacity

The operator automatically creates a Persistent Volume (PV) and Persistent Volume Claim (PVC) to store the data from your database. It offers 2Gi of storage space by default.

This can be changed using the datadirVolumeClaimTemplate manifest field that allows you to override the properties of the operator-produced PVC resource. Set the resources.requests.storage property to the capacity you need.

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
spec:
  # ...
  datadirVolumeClaimTemplate:
    resources:
      requests:
        storage: 10Gi

You need to set this high enough so that there is enough room for your data to grow in the future. The operator does not support internal volume adjustments, so you will see an error if you try to increase the capacity in the future. Switching to a larger volume would require manual migration steps.

Pin the MySQL version

You can pin to a specific MySQL release with the spec.version and spec.router.version fields. This prevents accidental automatic upgrades. Please select the same version for your MySQL Pods and router instances to ensure compatibility.

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
spec:
  # ...
  version: 8.0.31
  router:
    instances: 1
    version: 8.0.31

Overview

Oracle’s MySQL operator provides a convenient mechanism for running MySQL databases within a Kubernetes cluster. You can provision new replicated databases by: InnoDBCluster objects. This is much easier than manually creating StatefulSets and services that expose regular MySQL containers.

Using the operator does not hinder your ability to manage MySQL or your Kubernetes environment. You can customize your pods by specifying your own manifest properties in the spec.podSpec field and use your own MySQL configuration file with the steps shown above. The operator also provides integrated backup support, allowing you to copy your database to external storage on a recurring schedule.

Add Comment