How to initialize mysql container when created on Kubernetes?
18,962
Solution 1
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: v1
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');
Solution 2
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.
Related videos on Youtube
Author by
Harry
Updated on September 14, 2022Comments
-
Harry over 1 year
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 over 6 yearsI have some sql file to create tables and insert data. And I want to use for initialization when creating container. This code seems nothing to do that.
-
sfgroups over 6 yearsAs ivan responded you need to mount the sql files file system using PV, PVC
-
Harry over 6 yearsThough I set hostPath, it seems requiring absolute path. However I faced this error. error: code = 2 desc = failed to start container "ae00a8dc51d8895506881960a310c447b67643ccd088d164891a68f32f124634": Error response from daemon: mkdir /gcptmp: read-only file system
-
ivan.sim over 6 years@Harry I have updated my answer to use volume with
ConfigMap
. UsinghostPath
requires you to either run your container as a privileged container or modify the file permission on your host to be writable to thehostPath
volume, which in most cases aren't necessary. -
Harry over 6 yearsIt seems to be possible that configured yaml file bacome messy if sql is big. In my current situation, I created extended mysql image including initial sql files. However, I learned many from you. Thanks!!
-
Harry over 6 yearsit seems nice!! I'll try it! Thanks. After that I'll feed back it!
-
pizza over 3 yearsWhen I add the configmap to the volumes field, I get
spec.template.spec.volumes[0].configMap: Forbidden: may not specify more than 1 volume type * spec.template.spec.containers[0].volumeMounts[0].name: Not found: "mysql-initdb"
Does anybody know what the problem is?? This is my configvolumes: - name: mysql-persistent-storage configMap: name: mysql-initdb-config persistentVolumeClaim: claimName: mysql-volumeclaim
-
reactor about 3 yearswhen I use this exact pod config and run skaffold on it, I noticed that the pod is still in ContainerCreating status after 13 minutes and was wondering how to get it going. Thank you!
-
user658182 over 2 yearsThe problem with this approach is that data mapped to the configMap is limited to 3M. Take this example:
kubectl create configmap init-script --from-file=db.sql
error: failed to create configmap: Request entity too large: limit is 3145728
The challenge , especially for those of us coming from a LAMP stack background, your db backup is always going to be larger than 3M for an existing application. I'm facing the same question - When deploying an existing app, how do I get my existing data, files and db data, into that deployed app? Preferably automatically as part of a CI / CD pipeline. -
user658182 over 2 yearsReferences: github.com/bitnami/charts/issues/8378 and stackoverflow.com/q/70297296/658182. If anyone has overcome this issue and can provide insight, please contribute to the linked issues.