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?

Answer 1

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"