using environment variables in docker-compose mounted files for initializing mysql

Question

I am having trouble initializing mysql via docker-compose with the use of /docker-entrypoint-initdb.d whenever my initializing scripts requires environment variables.

I have the following docker-compose.yml file.

version: '3'

services:
  mysql:
    image: mysql
    container_name: mysql
    restart: always
    env_file: .env
    environment:
      - MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASS
      - MYSQL_USER=$MYSQL_USER
      - MYSQL_PASSWORD=$MYSQL_PASS
      - MYSQL_DB=$MYSQL_DB
    volumes:
      - db-data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - $MYSQL_PORT:3306

volumes:
  db-data:

This is my ./init.sql

CREATE DATABASE IF NOT EXISTS ${MYSQL_DB};
GRANT ALL PRIVILEGES ON ${MYSQL_DB}.* TO '${MYSQL_USER}'@'%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

When I run docker-compose up, I get an error with my ./init.sql and here's what it says:

mysql    | 2021-07-16 14:53:17+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
mysql    | ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{MYSQL_DB}' at line 1

Everything works perfectly if I change my ~/init.sql to use hardcoded values like this :

CREATE DATABASE IF NOT EXISTS testingdb;
GRANT ALL PRIVILEGES ON testingdb.* TO 'testinguser'@'%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
# where testingdb and testinguser is my .env.MYSQL_DB and .env.MYSQL_USER respectively

How do I use environment variables in docker's volume mounted files?

Usually you only have one database in each container, so the name of the database doesn't really matter. Do you need to have more than one database in your container? — Hans Kilian
– Hans Kilian, Commented Jul 16, 2021 at 16:07
hmm.. ok, but I do want to use a non-root user to access the database. to me, that means i'll still need a variable for that? — learningtech
– learningtech, Commented Jul 16, 2021 at 16:11
To me it looks like the container creates the user and grants access if you specify MYSQL_USER and MYSQL_PASSWORD environment variables. So you don't need to do that yourself. See line 314-322 here github.com/docker-library/mysql/blob/… — Hans Kilian
– Hans Kilian, Commented Jul 16, 2021 at 16:26

rockstarr-programmerr · Accepted Answer · 2021-09-19 15:58:01Z

14

Env variables can be used in .sh file, so you can achieve what you want like this:

Create an init_db.sh file (instead of init_db.sql)

Then in the init_db.sh file:

echo "** Creating default DB and users"

mysql -u root -p$MYSQL_ROOT_PASSWORD --execute \
"CREATE DATABASE IF NOT EXISTS $MYSQL_DB;
GRANT ALL PRIVILEGES ON $MYSQL_DB.* TO '$MYSQL_USER'@'%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"

echo "** Finished creating default DB and users"

answered Sep 19, 2021 at 15:58

rockstarr-programmerr

5166 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using environment variables in docker-compose mounted files for initializing mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related