Docker/MySQL: Update database structure

GitLab CI/CD
1

Hi,
I’ve got a docker/mysql instance running using Rancher. The database is set up, initial tables defined, and the service is running OK.

However, I’m trying to incorporate the Gitlab CI such that if I need to make changes to the DB structure, I can do that via a shell script calling mysql directly. I’m guessing that has to be done via the Docker container, but I’m struggling to get things working.

Here’s my Dockerfile:

FROM mysql:8.0.1

ENV MYSQL_DATABASE=XXX
ENV MYSQL_ROOT_PASSWORD=XXX
ENV MYSQL_USER=XXX
ENV MYSQL_PASSWORD=XXX

# Copy the database initialize scripts, which are then run by rancher-compose
ADD schema.sql /tmp/schema.sql
ADD update_db.sh /tmp/update_db.sh

# Add execute permissions
RUN ["chmod", "+x", "/tmp/update_db.sh"]

And my CI YML:

services:
- docker:dind

variables:
    DOCKER_DRIVER: overlay
    REGISTRY_HOST: registry.gitlab.com

# Select what we should cache between builds
cache:
    paths:
    - vendor/

stages:
    - publish
    - deploy

build_docker:
    image: docker:latest
    stage: publish
    tags:
        - dev
    script:
        - docker login -u gitlab-ci-token -p $CI_JOB_TOKEN $REGISTRY_HOST
        - docker build -t registry.gitlab.com/XXX/mysql .
        - docker push registry.gitlab.com/XXX/mysql


deploy_dev:
  stage: deploy
  image: cdrx/rancher-gitlab-deploy
  tags:
        - dev
  script:
      - echo "Deploy to dev"
      - upgrade --environment DEV --stack mysql --service mysql

The /tmp/update_db.sh script works if I log into the docker shell and run it directly, but if I try to run it via other methods, I the mysql hostname is unknown. I’ve tried 127.0.0.1, localhost, the name of the service (“mysql”), and by defining a docker-compose hostname (again, “mysql)”, and they all result in the same error.

update_db.sh:

#!/bin/bash
echo "Running update_db.sh"
set -e
mysqld &

HOST="mysql"

until mysql -h $HOST -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW DATABASES"; do
  mysql -h $HOST -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW DATABASES"
  >&2 echo "MySQL is unavailable — sleeping"
  sleep 1
done
>&2 echo "MySQL is up — executing GRANT statement"
mysql -h $HOST -uroot -p$MYSQL_ROOT_PASSWORD -e "GRANT ALL ON $MYSQL_DATABASE.* TO $MYSQL_USER@'%' IDENTIFIED BY \"$MYSQL_PASSWORD\""
>&2 echo "Starting to load SQL dump"
mysql -h $HOST -uroot -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE < /tmp/schema.sql
>&2 echo "Finished loading SQL dump"

I’ve tried using the docker CMD, Entrypoint, and docker-compose “command” function, all result in the same issue. It’s like the script is not running inside the docker image.

Does anyone have a working script that does database updates in the CI process?

Thanks in advance for any help.
Andy

2

I’ve just tried adding this to the DockerFile:

CMD mysqld --init-file="/tmp/schema.sql"

And although it runs, I get the error “The MySQL server is running with the --secure-file-priv option so it cannot execute this statement”

I’m assuming this is because the standard mysql docker image has the secure-file-priv set to “”, but I’m not sure how to override that.

3

However, I now see that init-file is about data load, not SQL to change schema structure, so I’m at a loss again.

Any help would be much appreciated.

Andy

4

OK, I think I’ve got there (although I’ve some issues with the SQL itself).

It was a multi-step approach, which I can’t easily document in my current environment. I’ll post an update late for anyone looking for a similar solution, once I get back to my normal development machine.

Phew. That was harder than it should have been!

Andy