How to create mysql service using local persistent volume to store the data on windows local machine

9/24/2020

I want that mysql pod doesn't remove all mysql data when I restart the computer.

I should be able to store the data in my machine, so when I reboot my computer and the mysql pod starts again, the databases are still there.

here are my yaml's:

storage-class.yaml

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer

mysql-pv.yaml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv-volume
  labels:
    type: local
spec:
  capacity:
    storage: 20Gi
  accessModes:
    - ReadWriteMany
  persistentVolumeReclaimPolicy: Retain
  volumeMode: Filesystem
  storageClassName: local-storage
  local:
    path: "C:\\mysql-volume" #2 \ for escape characters right?
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - docker-desktop
  #hostPath:
   # path: /mysql-volume
    #type: DirectoryOrCreate
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
  labels:
    type: local
spec:
  storageClassName: local-storage
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 20Gi

mysql-deployment.yaml

apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  ports:
  - protocol: TCP
    port: 3306
    nodePort: 30001
  selector:        
    app: mysql
  type: NodePort
---
apiVersion: apps/v1 # for versions before 1.9.0 use apps/v1beta2
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql-custom-img-here
        imagePullPolicy: IfNotPresent
        name: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: db-secret
              key: mysql-root-password
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: db-secret
              key: mysql-user
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: db-secret
              key: mysql-password
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim

After trying that, the first error I got was:

MountVolume.NewMounter initialization failed for volume "mysql-pv-volume" : path "C:\\mysql-volume" does not exist

Since im using windows, I guess that's the correct path right? Im using 2 "\" for a escape character, Maybe the problem is here in the path, but not sure. If it is, how can I give my local path on my windows machine?

Then I changed the local: path: to /opt and the following error apeared:

initialize specified but the data directory has files in it.

log:

2020-09-24 12:53:00+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.31-1debian10 started.
2020-09-24 12:53:00+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-09-24 12:53:00+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.31-1debian10 started.
2020-09-24 12:53:00+00:00 [Note] [Entrypoint]: Initializing database files
2020-09-24T12:53:00.271130Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-24T12:53:00.271954Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2020-09-24T12:53:00.271981Z 0 [ERROR] Aborting

but if I change the mountPath: /var/lib/mysql to for example mountPath: /var/lib/mysql-test

It works, but not as expected(saving the data after rebooting the computer).

Even after removing the PV, PVC and MYSQL deployment/service, that same error keeps appearing. I even removed the volumes using the docker command, and changing my mysql custom image just to 'mysql:5.7' just in case, but the same initialize specified but the data directory has files in it. appears.

How does that happen, even when I remove the pod? mountPath is the container path, so the data should disappear.

And how can I give my local path in the persistentVolume?

Thanks for your time!

edit: forgot the tell that I already saw this: https://stackoverflow.com/questions/54406110/how-to-create-a-mysql-kubernetes-service-with-a-locally-mounted-data-volume

I searched a lot, but no luck

-- Tiago Machado
docker
kubernetes
mysql
persistent-volume-claims
persistent-volumes

2 Answers

9/25/2020

I finally solved the problem...

The problem of initialize specified but the data directory has files in it. was answered by @Jakub

The MountVolume.NewMounter initialization failed for volume "mysql-pv-volume" : path "C:\\mysql-volume" does not exist .... I can't even believe the time spent because of this silly problem...

the correct path is: path: /c/mysql-volume after that, all worked as expected!

-- Tiago Machado
Source: StackOverflow

9/28/2020

I am posting this as a community wiki answer for better visibility.


If you have problem with initialize specified but the data directory has files in it then there is github issue which will help you.


TLDR

  • Use --ignore-db-dir=lost+found in your container
  • Use older version of mysql, for example mysql:5.6

There are answers on github provided by @alexpls and @aklinkert

I had this issue with Kubernetes and MySQL 5.7.15 as well. Adding the suggestion from @yosifki to my container's definition got things working.

Here's an extract of my working YAML definition:

name: mysql-master
image: mysql:5.7
args:
  - "--ignore-db-dir=lost+found"

The exact same configuration is working for MySQL Version 5.6 with.

-- Jakub
Source: StackOverflow