Automatically initalized and version controlled MySQL database in kubernetes+helm development environment
2019-08-30
Today I was hacking together a kitchen sink project to play with development
using kubernetes, skaffold and helm. I spent a bit of time thinking about how
to set up MySQL. At my previous company we used docker-compose and created a
MySQL
container along with a container called migrations
that initialized
our database with schema and sample data. The migrations container would run
once when the development environment started and exit when complete.
For this latest project, I decided to go the route of creating a custom docker
image based on the mysql:5.6
image (https://hub.docker.com/_/mysql). This
custom image simply copies a schema dump file to /docker-entrypoint-initdb.d/
.
My goal is to create a single source of truth for the latest database schema in
a project as well as use version control to document the history of changes.
Dockerfile
https://github.com/cflynn07/rgbm-mysql
FROM mysql:5.6 COPY rgbm.sql /docker-entrypoint-initdb.d/rgbm.sql
I use Github Actions to automatically build & push to the docker hub image registry on any push to the master branch of the repository. https://github.com/cflynn07/rgbm-mysql/blob/master/.github/workflows/dockerimage.yml
Github Action
name: Docker Image CI on: push: paths: - 'rgbm.sql' - 'Dockerfile-mysql' branches: - master jobs: build_and_push: name: build and push runs-on: ubuntu-latest steps: - uses: actions/[email protected] - uses: 'actions/docker/[email protected]' name: 'docker login' env: DOCKER_USERNAME: ${{ "{{" }} secrets.DOCKER_USERNAME {{ "}}" }} DOCKER_PASSWORD: ${{ "{{" }} ssecrets.DOCKER_PASSWORD {{ "}}" }} - uses: 'actions/docker/[email protected]' name: 'docker build' with: args: 'build . --file Dockerfile-mysql --tag cflynnus/rgbm-mysql:${{ "{{" }} github.sha {{ "}}" }}' - uses: 'actions/docker/[email protected]' name: 'docker push hash' with: args: 'push cflynnus/rgbm-mysql:${{ "{{" }} github.sha {{ "}}" }}' - uses: 'actions/docker/[email protected]' name: 'docker tag latest' with: args: 'tag cflynnus/rgbm-mysql:${{ "{{" }} github.sha {{ "}}" }} cflynnus/rgbm-mysql:latest' - uses: 'actions/docker/[email protected]' name: 'docker push latest' with: args: 'push cflynnus/rgbm-mysql:latest'
Then in my helm templates I add a deployment object that references this image.
Note I set the imagePullPolicy
to Always
so that each time I start my
development environment docker will pull the latest image. The default value of
imagePullPolicy is IfNotPresent
.
https://github.com/cflynn07/rgbm/blob/master/helm/templates/deployment.yaml
Kubernetes (helm managed) Deployment Object
apiVersion: apps/v1 kind: Deployment metadata: name: mysql spec: selector: matchLabels: app: mysql strategy: type: Recreate template: metadata: labels: app: mysql spec: containers: - image: cflynnus/mysql-rgbm name: mysql env: - name: MYSQL_ROOT_PASSWORD value: password - name: MYSQL_DATABASE value: rgbm - name: MYSQL_USER value: user - name: MYSQL_PASSWORD value: 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
When I modify the database I perform a MySQL dump and overwrite the .sql file in my cflynn07/rgbm-mysql repository. Then I commit and push my changes to the remote repository.
$ docker ps | grep mysql | awk ‘{print $1}’ bb4f90b37b92 $ docker exec -i bb4f90b37b92 sh -c ‘exec mysqldump –all-databases -uuser -ppassword’ > ./rgbm.sql