Run and operate MariaDB in Kubernetes with mariadb-operator

The introduction of the StatefulSet resource was a game changer when it comes to run stateful workloads in Kubernetes, introducing a wide range of features, including:

Predictable DNS names for each Pod, allowing one to individually address them in the network.
Stable persistent storage for each Pod, ensuring that each of them is bound to the same PersistentVolumeClaim.
Ordered graceful deployments and automated rolling updates.

However, this isn’t quite enough for running databases in Kubernetes in a reliable way. We are missing day 1 and day 2 operations, such as configuring high availability and scheduling backups, which is something not managed by vanilla Kubernetes. Normally the DBAs, a.k.a. human operators, will be the designated people for performing these operations by following runbooks and making use of their expertise, but ultimately they are humans and have limited capabilities, specially when it comes to maintaining a large fleet of databases.

In order to address this, the mariadb-operator extends the Kubernetes API by providing a MariaDB custom resource which encapsulates the operational expertise for running and operating MariaDB in Kubernetes without hassle. In this blog, we will be installing mariadb-operator and configuring some of the custom resources it provides.

Installing mariadb-operator

You can easily install mariadb-operator in your Kubernetes cluster via Helm. This will install the required custom resources and the operator itself:

helm repo add mariadb-operator https://mariadb-operator.github.io/mariadb-operator
helm install mariadb-operator mariadb-operator/mariadb-operator

Provisioning a MariaDB server

First of all, we need to create a Kubernetes Secret with the root credentials:

kubectl create secret generic mariadb --from-literal=root-password=mariadb

Then, we can refer to it and create the MariaDB resource:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: MariaDB
metadata:
  name: mariadb
spec:
  rootPasswordSecretKeyRef:
    name: mariadb
    key: root-password
  image:
    repository: mariadb
    tag: "10.11.3"
    pullPolicy: IfNotPresent
  port: 3306
  volumeClaimTemplate:
    resources:
      requests:
        storage: 100Mi
    accessModes:
      - ReadWriteOnce
  myCnf: |
    [mariadb]
    bind-address=0.0.0.0
    default_storage_engine=InnoDB
    binlog_format=row
    innodb_autoinc_lock_mode=2
    max_allowed_packet=256M
  resources:
    requests:
      cpu: 100m
      memory: 128Mi
    limits:
      cpu: 300m
      memory: 512Mi
EOF

The operator will be watching the MariaDB resources and eventually reconcile them into StatefulSets:

kubectl get mariadbs
NAME      READY   STATUS    PRIMARY POD   AGE
mariadb   True    Running   mariadb-0     4m43s

kubectl get statefulsets
NAME      READY   AGE
mariadb   1/1     4m48s

kubectl get pods
NAME                                        READY   STATUS      RESTARTS   AGE
mariadb-0                                   1/1     Running     0          4m52s

Configuring databases, users and grants

With the MariaDB instance successfully deployed and operational, the next step is to configure the Databases and Grant access to Users:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: Database
metadata:
  name: data-test
spec:
  mariaDbRef:
    name: mariadb
  characterSet: utf8mb4
  collate: uca1400_ai_ci
EOF

kubectl create secret generic user --from-literal=password=mariadb

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: User
metadata:
  name: user
spec:
  mariaDbRef:
    name: mariadb
  passwordSecretKeyRef:
    name: user
    key: password
  maxUserConnections: 20
EOF

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: Grant
metadata:
  name: grant
spec:
  mariaDbRef:
    name: mariadb
  privileges:
    - "CREATE"
    - "SELECT"
    - "INSERT"
    - "UPDATE"
  database: "*"
  table: "*"
  username: user
  grantOption: false
EOF

Once the previous manifests have been applied, the operator will create their counterpart SQL resources in MariaDB:

kubectl get databases
NAME        READY   STATUS    CHARSET   COLLATE           MARIADB   AGE
data-test   True    Created   utf8      utf8_general_ci   mariadb   45s

kubectl get users
NAME   READY   STATUS    MAXCONNS   MARIADB   AGE
user   True    Created   20         mariadb   25s

kubectl get grants
NAME    READY   STATUS    DATABASE   TABLE   USERNAME   GRANTOPT   MARIADB   AGE
grant   True    Created   *          *       user       true       mariadb   20s

Configure connections for applications

At this stage, we can declaratively create Connections to enable applications to seamlessly connect to MariaDB just by mounting a Secret created by the operator:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: Connection
metadata:
  name: connection
spec:
  mariaDbRef:
    name: mariadb
  username: user
  passwordSecretKeyRef:
    name: user
    key: password
  database: data-test
  secretName: connection
  secretTemplate:
    key: dsn
EOF

kubectl get connections
NAME         READY   STATUS    SECRET       MARIADB   AGE
connection   True    Healthy   connection   mariadb   6s

kubectl get secret connection -o jsonpath="{.data.dsn}" | base64 -d
user:mariadb@tcp(mariadb.default.svc.cluster.local:3306)/data-test

Orchestrate and schedule SQL scripts

Now that the applications can successfully establish connections, the next consideration is the management of the database schema. The SqlJob resource allows for the execution of SQL scripts within Kubernetes Jobs. By utilizing this resource, you have the flexibility to define the database schema or perform migrations by executing the SQL scripts:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: SqlJob
metadata:
  name: 01-users
spec:
  mariaDbRef:
    name: mariadb
  username: user
  passwordSecretKeyRef:
    name: user
    key: password
  database: data-test
  sql: |
    CREATE TABLE IF NOT EXISTS users (
      id bigint PRIMARY KEY AUTO_INCREMENT,
      username varchar(255) NOT NULL,
      UNIQUE KEY name__unique_idx (username)
    );
    INSERT INTO users(username) VALUES('mmontes11');
EOF

kubectl get sqljobs
NAME       COMPLETE   STATUS    MARIADB   AGE
01-users   True       Success   mariadb   42s

kubectl get jobs
NAME       COMPLETIONS   DURATION   AGE
01-users   1/1           10s        49s

kubectl exec -it mariadb-0 -- mariadb -u user -pmariadb -D data-test -e "SELECT * FROM users;"
+----+-----------+
| id | username  |
+----+-----------+
|  1 | mmontes11 |
+----+-----------+

Backup and restore

Now that the database has been initialized, it is crucial to schedule periodic backups to prevent data loss. You can do so by creating a Backup resource:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: Backup
metadata:
  name: backup-scheduled
spec:
  mariaDbRef:
    name: mariadb
  schedule:
    cron: "*/5 * * * *"
  maxRetentionDays: 30
  storage:
    persistentVolumeClaim:
      resources:
        requests:
          storage: 100Mi
      accessModes:
        - ReadWriteOnce
EOF

This resource will get reconciled into a CronJob that will periodically schedule Jobs to take backups:

kubectl get backups
NAME               COMPLETE   STATUS    MARIADB   AGE
backup-scheduled   True       Success   mariadb   24s

kubectl get cronjobs
NAME               SCHEDULE      SUSPEND   ACTIVE   LAST SCHEDULE   AGE
backup-scheduled   */5 * * * *   False     0        28s             29s

Similarly, we can create a Restore resource to restore a Backup:

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: Restore
metadata:
  name: restore
spec:
  mariaDbRef:
    name: mariadb
  backupRef:
    name: backup-scheduled
EOF

kubectl get restores
NAME      COMPLETE   STATUS    MARIADB   AGE
restore   True       Success   mariadb   11s

kubectl get jobs
NAME                        COMPLETIONS   DURATION   AGE
restore                     1/1           10s        23s

Bootstrap a new MariaDB from a backup

For convenience, it is possible to create a new MariaDB instance from an existing Backup. By leveraging this functionality, you can spin up new instances and quickly restore data from backups as needed. The operator will handle this process by performing a restore operation as soon as the new instance becomes ready.

kubectl apply -f - <<EOF
apiVersion: mariadb.mmontes.io/v1alpha1
kind: MariaDB
metadata:
  name: mariadb-from-backup
spec:
  bootstrapFrom:
    backupRef:
      name: backup-scheduled
  rootPasswordSecretKeyRef:
    name: mariadb
    key: root-password
  image:
    repository: mariadb
    tag: "10.11.3"
    pullPolicy: IfNotPresent
  port: 3306
  volumeClaimTemplate:
    resources:
      requests:
        storage: 100Mi
    accessModes:
      - ReadWriteOnce
  resources:
    requests:
      cpu: 100m
      memory: 128Mi
    limits:
      cpu: 300m
      memory: 512Mi
EOF

kubectl get mariadbs
NAME                  READY   STATUS    PRIMARY POD             AGE
mariadb-from-backup   True    Running   mariadb-from-backup-0   1m

kubectl get pods
NAME                                        READY   STATUS      RESTARTS   AGE
mariadb-from-backup-0                       1/1     Running     0          1m

kubectl exec -it mariadb-from-backup-0 -- mariadb -u root -pmariadb -D data-test  -e "SELECT * FROM users;"
+----+-----------+
| id | username  |
+----+-----------+
|  1 | mmontes11 |
+----+-----------+

Wrapping up

This blog demonstrated the effective utilization of mariadb-operator to manage MariaDB instances in Kubernetes using custom resources, promoting a declarative approach instead of relying on imperative commands. Feel free to take a look at the documentation and join the mariadb-operator Slack channel if you have any questions!

Resources

github.com/mariadb-operator/mariadb-operator

3 replies on “Run and operate MariaDB in Kubernetes with mariadb-operator”

Noman says:

2023-07-25 at 22:55

Great post, Martin! Your comprehensive guide on managing MariaDB with the mariadb-operator in Kubernetes is quite enlightening. I appreciate the detailed steps on setup, configuration, and especially on backup and restoration. The ability to bootstrap a new MariaDB from an existing backup is a game changer. Thanks for sharing your insights!
1. Martin Montes says:
  
  2023-07-26 at 18:25
  
  Thanks a lot for your postive feedback Noman!
Liu JinFeng says:

2023-09-10 at 05:23

Please, why do you use storageClass for mariadb?

Comments are closed.