Automated MySQL MariaDB tables optimization in Kubernetes

2020-12-26
Kubernetes MariaDB MySQL optimization schedule

Kubernetes simplified applications management a lot and this also applies to databases like MySQL/MariaDB, but lot of DevOps forget to apply traditional maintenance and optimization which is still necessary unless you are using some operator capable of doing so. The optimization method that will be described here applies just to single and master-slave replicated MySQL/MariaDB instances. If you run some clustered version like Galera , you probably have to find another way because of possible consistence and performance problems during the eventual optimization. This method would still work but you may have unpredictable consequences.

The command that should be run periodically is the following:

mysqlcheck -h mariadb-host -u root -p -o --all-databases

But how to run it on Kubernetes? It is actually quite simple using CronJob resource and an optimized image ectobit/mariadb-client . The image is based on Alpine Linux and additionally contains just mariadb-client package which includes commands like mysql (client), mysqldump, mysqlcheck and mysqladmin. Here is the manifest:

apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: mariadb-optimize-tables
spec:
  schedule: "2 2 * * 0"
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 1
  failedJobsHistoryLimit: 1
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: mariadb-optimize-tables
            image: ectobit/mariadb-client
            imagePullPolicy: Always
            env:
            - name: PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mariadb
                  key: mariadb-root-password
            command:
            - sh
            - -c
            - mysqlcheck -h mariadb-host -u root -p${PASSWORD} -o --all-databases
          restartPolicy: OnFailure
      ttlSecondsAfterFinished: 172800

You also need to create the secret mariadb in the same namespace where your database resides, containing the key mariadb-root-password. Of course you can name these differently and in that case you have to replace the names in the YAML code above.

You can count that the image ectobit/mariadb-client will be regularly updated in the future because it’s automatized using GitHub Dependabot . If you like the image, please give it a star on GitHub and Docker Hub . Thank you 🙃.

A first impression of Rust from the perspective of a Go developer

Rust is very powerful, but let's see how it compares to Go
Go Rust Kubernetes controller secret replicator

Develop Kubernetes native applications in Golang with auto-recompile and auto-restart

Learn how to have automatic recompile and restart of your Go project running on Kubernetes
Kubernetes Go Golang development controller operator native application autorecompile autorestart

Using Traefik 2.2 middlewares with Kubernetes Ingress resources

Learn how to write and use Traefik middlewares
traefik middleware Kubernetes ingress redirect auth