Automated MySQL MariaDB tables optimization in Kubernetes

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
  name: mariadb-optimize-tables
  schedule: "2 2 * * 0"
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 1
  failedJobsHistoryLimit: 1
          - name: mariadb-optimize-tables
            image: ectobit/mariadb-client
            imagePullPolicy: Always
            - name: PASSWORD
                  name: mariadb
                  key: mariadb-root-password
            - 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 🙃.