JIYIK CN >

Kubernetes support for stateful applications has matured considerably over the past few years. It is now possible to co-locate your database within your cluster, allowing it to benefit from the same scalability as your other workloads.

MySQL, one of the most popular relational database engines, is now maintained by an official Kubernetes Operator. The Oracle-led open source project provides an easy way to create a managed MySQL cluster in Kubernetes.

In this article, you will learn how to install Operatorand start configuring the database. OperatorThe service is automatically created so that applications in your other containers can connect to MySQL.

What are MySQL operators?

Oracle's MySQL operator is a component that runs inside the cluster to automate database initialization. You don't need an operations person to use MySQL in Kubernetes—you can StatefulSetdeploy the official container image yourself using . But this approach is cumbersome and requires you to write and maintain long manifest files to create a reliable environment.

The Operator provides a set of custom resources that can be used to create a database. InnoDBClusterAdding the object to your Kubernetes cluster prompts the Operator to set up StatefulSets , storage , and networking for you . It also automates upgrades and backups, greatly reducing the burden on administrators.

Installing MySQL Operator

The included Helm chart is the easiest way to install in your cluster Operator. If you don’t have Helm in your environment, you can use the manifest file as an alternative.

First add the operator to your list of Helm repositories:

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

Next update Helm's repository database so that it can discover the available charts:

$ helm repo update

Now install the Operator into mysql-operatora new namespace called with the following command:

$ 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

The process may take several seconds to complete.

Create Root User Credentials Key

Each database cluster you create must come with a Kubernetes secret that contains the credentials for the MySQL root user. The Operator will create a root privileged account using the username and password provided in the secret.

Copy the following YAML listing and rootPasswordchange the values ​​of the fields to secure values. You can also change the username and user hosts settings if applicable to your situation. Keep in mind that the account named here will have full control over MySQL; you should set up a separate user for your application later 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 add the secret to the cluster using Kubectl:

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

Creating a Basic Cluster

Next create a new YAML file called 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 a custom resource provided by the MySQL Operator InnoDBCluster. Applying the manifest to your cluster will create a new MySQL database with replication automatically configured. Review and change the following properties in the manifest before continuing:

• spec.secretName – This must match the name of the secret you created earlier metadata.name. The referenced secret will be read to set up the MySQL root user account.
• spec.instances – This defines how many MySQL replicas (Pods) to run. Currently up to 10 replicas are supported.
• spec.tlsUseSelfSigned – This field is set to true in this example to use the included self-signed TLS certificate. You can choose to provide your own certificate spec.tlsSecretName/spec.tlsCASecretNamethrough a Kubernetes secret by setting the field.
• spec.router.instances – The MySQL Router component is responsible for directing service traffic between available MySQL replicas. This field defines the number of router instances to run. Multiple instances will improve performance and resiliency in high traffic situations.

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

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

The MySQL initialization process may take several minutes to complete. The Operator uses several init containers to set up user accounts and configure MySQL’s data directory. Wait a few moments, then kubectl get podscheck what’s running by 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 the single router instance are in Runningthe state. The database is now ready for use.

Connect 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 example cluster shown above is mysql-cluster.default.svc.cluster.local. Configure your application to connect to MySQL at this address using port 3306 and the user credentials you defined in the secret.

Accessing your database from outside

You can use Kubectl's port forwarding feature to access MySQL from outside the cluster. Run the following command to open a new port forwarding session:

$ kubectl port-forward service/mysql-cluster 3306

Replace mysql-clusterwith InnoDBClusterthe name of the database you want to connect to. Now you can use local tools to interact with the database:

$ mysql -h127.0.0.1 -u root -p

kubectl port-forwardPressing Ctrl+C in the terminal window running the command will close the connection.

Customizing MySQL Server Settings

You can provide any MySQL configuration file options your application requires by InnoDBClustersetting the fields in the manifest of the MySQL object :spec.mycnf

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

The Operator will use the value of this field to write the my.cnf file to the database Pod ’s file system.

Setting storage capacity

The Operator automatically creates a persistent volume (PV) and a persistent volume claim (PVC) to store database data. It provides 2Gi of storage space by default.

datadirVolumeClaimTemplateThis can be changed using the manifest field, which allows you to override the properties of the PVC resource generated by the Operator. resources.requests.storageSet the property to the capacity you require.

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

You should set this value high enough so that your data has enough room to grow in the future. The Operator does not support in-place resizing of volumes, so you will see errors if you try to increase capacity in the future. Switching to a larger volume will require a manual migration step.

Fixed MySQL version

You can pin to a specific MySQL version using the spec.versionand spec.router.versionfields. This will prevent inadvertent automatic upgrades. Choose the same version for your MySQL Pod and router instance to ensure compatibility.

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

Summarize

Oracle's MySQL Operator provides a convenient mechanism for running a MySQL database in a Kubernetes cluster. You can InnoDBClusterprovision a new replicated database by creating a MySQL object. This StatefulSetis much simpler than manually creating a MySQL container and a service that exposes a regular MySQL container.

Using the Operator does not hinder your ability to control your MySQL or Kubernetes environment. You can spec.podSpeccustomize the Pod by providing your own manifest attributes in the field and use your own MySQL configuration file by following the steps above. The Operator also provides integrated backup support , allowing you to periodically copy the database to external storage.