K
Q

How to initialize mysql container when created on Kubernetes?

8/14/2017

I want to set initial data on MySQL of container. In docker-compose.yml, such code can create initial data when running container.

volumes:
  - db:/var/lib/mysql
  - "./docker/mysql/conf.d:/etc/mysql/conf.d"
  - "./docker/mysql/init.d:/docker-entrypoint-initdb.d"

However, how can I create initial data on Kubernetes when running?

-- Harry
docker
kubernetes
mysql

Similar Questions

What would be an ideal way to share writable volume across containers for a web server?
Failed to start Kubernetes API Server duo to unknown reason
How to add multiple users to openidc based gmail authentication in K8S
App deployed on Kubernetes cannot be accessed from Internet

3 Answers

8/14/2017

According to the MySQL Docker image README, the part that is relevant to data initialization on container start-up is to ensure all your initialization files are mount to the container's /docker-entrypoint-initdb.d folder.

You can define your initial data in a ConfigMap, and mount the corresponding volume in your pod like this:

apiVersion: extensions/v1beta1
kind: Pod
metadata:
  name: mysql
spec:
  containers:
  - name: mysql
    image: mysql        
    ports:
      - containerPort: 3306
    volumeMounts:
      - name: mysql-initdb
        mountPath: /docker-entrypoint-initdb.d
  volumes:
    - name: mysql-initdb
      configMap:
        name: mysql-initdb-config
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-initdb-config
data:
  initdb.sql: |
    CREATE TABLE friends (id INT, name VARCHAR(256), age INT, gender VARCHAR(3));
    INSERT INTO friends VALUES (1, 'John Smith', 32, 'm');
    INSERT INTO friends VALUES (2, 'Lilian Worksmith', 29, 'f');
    INSERT INTO friends VALUES (3, 'Michael Rupert', 27, 'm');
-- ivan.sim
Source: StackOverflow
3/11/2019

First: create persistent volume that contains your SQL scripts

kind: PersistentVolume
apiVersion: v1
metadata:
  name: mysql-initdb-pv-volume
  labels:
    type: local
    app: mysql
spec:
  storageClassName: manual
  capacity:
    storage: 1Mi
  accessModes:
    - ReadOnlyMany
  hostPath:
    path: "/path/to/initdb/sql/scripts"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mysql-initdb-pv-claim
  labels:
    app: mysql
spec:
  storageClassName: manual
  accessModes:
    - ReadOnlyMany
  resources:
    requests:
      storage: 1Mi

Note: assume that you have your SQL scripts in /path/to/initdb/sql/scripts

Second: mount the volume to /docker-entrypoint-initdb.d

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 3306
          volumeMounts:
            - mountPath: /docker-entrypoint-initdb.d
              name: mysql-initdb
      volumes:
        - name: mysql-initdb
          persistentVolumeClaim:
            claimName: mysql-initdb-pv-claim

That's it.

Note: this applies to PostgreSQL too.

-- Yuci
Source: StackOverflow
8/14/2017

you need to create pv and pvclaim like this then deploy the mysql database

kind: PersistentVolume
apiVersion: v1
metadata:
  name: sfg-dev-mysql-pv-volume
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/tmp/data"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: sfg-dev-mysql-pv-claim
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi

create secret:

kubectl create secret generic mysql-secret --from-literal=mysql-root-password=kube1234 --from-literal=mysql-user=testadm --from-literal=mysql-password=kube1234
kubectl create configmap db --from-literal=mysql-database: database

mysql deployment:

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: sfg-dev-mysql-db
  labels:
    app: sfg-dev-mysql
spec:
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: sfg-dev-mysql
        tier: db
    spec:
      containers:
      - image: mysql:8.0.2
        name: mysql
        env:
        - name: MYSQL_DATABASE
          valueFrom:
            configMapKeyRef:
              name: db
              key: mysql-database        
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-root-password
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-user
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-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: sfg-dev-mysql-pv-claim
-- sfgroups
Source: StackOverflow