Kubernetes: Error when creating a StatefulSet with a MySQL container

Question

Good morning,

I'm very new to Docker and Kubernetes, and I do not really know where to start looking for help. I created a database container with Docker and I want manage it and scale with Kubernetes. I started installing minikube in my machine, and tried to create a Deployment first and then a StatefulSet for a database container. But I have a problem with the StatefulSet when creating a Pod with a database (mariadb or mysql). When I use a Deployment the Pods are loaded and work fine. However, the same Pods are not working when using them in a StatefulSet, returning errors asking for the MYSQL constants. This is the Deployment, and I use the command kubectl create -f deployment.yaml:

apiVersion: apps/v1beta1
kind: Deployment
metadata:
 name: mydb-deployment
spec:
 template:
  metadata:
   labels: 
    name: mydb-pod
  spec:
   containers:
    - name: mydb
      image: ignasiet/aravomysql
      ports:
       - containerPort: 3306

And when listing the deployments: kubectl get Deployments:

NAME               DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE
mydb-deployment    1         1         1            1           2m

And the pods: kubectl get pods:

NAME                                READY   STATUS    RESTARTS   AGE
mydb-deployment-59c867c49d-4rslh    1/1     Running   0          50s

But since I want to create a persistent database, I try to create a statefulSet object with the same container, and a persistent volume. Thus, when creating the following StatefulSet with kubectl create -f statefulset.yaml:

apiVersion: apps/v1beta1
kind: StatefulSet
metadata:
 name: statefulset-mydb
spec:
 serviceName: mydb-pod
 template:
  metadata:
   labels: 
    name: mydb-pod
  spec:
   containers:
    - name: aravo-database
      image: ignasiet/aravomysql
      ports:
       - containerPort: 3306
      volumeMounts:
       - name: volume-mydb
         mountPath: /var/lib/mysql
   volumes:
    - name: volume-mydb
      persistentVolumeClaim: 
       claimName: config-mydb

With the service kubectl create -f service-db.yaml:

apiVersion: v1
kind: Service
metadata:
 name: mydb
spec:
 type: ClusterIP
 ports:
  - port: 3306
 selector:
  name: mydb-pod

And the permission file kubectl create -f permissions.yaml:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
 name: config-mydb
spec:
 accessModes: 
  - ReadWriteOnce
 resources:
  requests:
   storage: 3Gi

The pods do not work. They give an error:

NAME                    READY   STATUS             RESTARTS   AGE
statefulset-mydb-0      0/1     CrashLoopBackOff   1          37s

And when analyzing the logs kubectl logs statefulset-mydb-0:

`error: database is uninitialized and password option is not specified
You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD`

How it is possible that it does ask for these variables when the container has already an initialization script and works perfectly? And why it asks only when launching as statefulSet, and not when launching the Deployment?

Thanks in advance.

Answer 1

I pulled your image ignasiet/aravomysql to try to figure out what went wrong. As it turns out, your image already has an initialized MySQL data directory at /var/lib/mysql:

$ docker run -it --rm --entrypoint=sh ignasiet/aravomysql:latest
# ls -al /var/lib/mysql 
total 110616
drwxr-xr-x 1 mysql mysql      240 Nov  7 13:19 .
drwxr-xr-x 1 root  root        52 Oct 29 18:19 ..
-rw-rw---- 1 root  root     16384 Oct 29 18:18 aria_log.00000001
-rw-rw---- 1 root  root        52 Oct 29 18:18 aria_log_control
-rw-rw---- 1 root  root      1014 Oct 29 18:18 ib_buffer_pool
-rw-rw---- 1 root  root  50331648 Oct 29 18:18 ib_logfile0
-rw-rw---- 1 root  root  50331648 Oct 29 18:18 ib_logfile1
-rw-rw---- 1 root  root  12582912 Oct 29 18:18 ibdata1
-rw-rw---- 1 root  root         0 Oct 29 18:18 multi-master.info
drwx------ 1 root  root      2696 Nov  7 13:19 mysql
drwx------ 1 root  root        12 Nov  7 13:19 performance_schema
drwx------ 1 root  root        48 Nov  7 13:19 yypy

However, when mounting a PersistentVolume or just a simple Docker volume to /var/lib/mysql, it's initially empty and therefore the script thinks your database is uninitialized. You can reproduce this issue with:

$ docker run -it --rm --mount type=tmpfs,destination=/var/lib/mysql ignasiet/aravomysql:latest
error: database is uninitialized and password option is not specified 
  You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

If you have a bunch of scripts you need to run to initialize the database, you have two options:

1. Create a Dockerfile based on the mysql Dockerfile, and add shell scripts or SQL scripts to /docker-entrypoint-initdb.d. More details available here under "Initializing a fresh instance".
2. Use the initContainers property in the PodTemplateSpec, something like:

apiVersion: apps/v1beta1
kind: StatefulSet
metadata:
  name: statefulset-mydb
spec:
  serviceName: mydb-pod
  template:
  metadata:
    labels: 
    name: mydb-pod
  spec:
    containers:
    - name: aravo-database
      image: ignasiet/aravomysql
      ports:
        - containerPort: 3306
      volumeMounts:
        - name: volume-mydb
          mountPath: /var/lib/mysql
    initContainers:
    - name: aravo-database-init
      command:
        - /script/to/initialize/database
      image: ignasiet/aravomysql
      volumeMounts:
        - name: volume-mydb
          mountPath: /var/lib/mysql
    volumes:
    - name: volume-mydb
      persistentVolumeClaim: 
        claimName: config-mydb

Answer 2

The issue you are facing is not specific to StatefulSet. It is because of the persistent volume. If you use StatefulSet without the persistent volume, you will not face this problem. Or, if you use Deployment with persistent volume you will face this issue.

Why? Ok, let me explain.

Setting up one of these environment variable MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD or MYSQL_RANDOM_ROOT_PASSWORD is mandatory for creating new database. Read Environment Variables part here.

But, if you initialize database from script, you will not require to provide it. Look at this line of docker-entrypont.sh here. It check if there is already a database in /var/lib/mysql directory. If there is none, it will try to create one. If you don't provide any of the specified environment variable then it will give the error you are getting. But, if it found already one database there, it will not try to create one and you will not see the error.

Now, the question is, you already have initialized the database then why it still complaining about the environment variables?

Here, the persistent volume come into play. As you have mounted the persistent volume at /var/lib/mysql directory, now this directory points to your persistent volume which is currently empty. So, when your container run docker-entrypoint.sh script, it does not found any database on /var/lib/mysql directory as it is now pointing to the persistent volume instead of original /var/lib/mysql directory of your docker image which had initialized database on this directory. So, it will try to create a new database and will complain as you haven't provided MYSQL_ROOT_PASSWORD environment variable.

When you don't use any persistent volume, your /var/lib/mysql directory points to the original directory which contains the initialized database. So, you don't see the error then.

Then, how you can initialize mysql database properly?

In order to initialize MySQL from a script, you just need to put the script into /docker-entrypoint-initdb.d. Just use a vanilla mysql image, put your initialization script into a volume then mount the volume at /docker-entrypoint-initdb.d directory. MySQL will be initialized.

Check this answer for details on how to initialize from script: https://stackoverflow.com/a/45682775/7695859