How to execute an sql file after a container of mysql image is built for the first time-CodePudding

I have an data.sql file in the same folder as Dockerfile & docker-compose.yml file. I want when the first time the container is being built with mysql image the commands written in the data.sql file of mine should execute.

In the data.sql I have:

create database db1;
create database db2;
create database db3;
create database db4;
GRANT ALL PRIVILEGES on db1.* to 'NORMAL_USER'@'%';
GRANT ALL PRIVILEGES on db2.* to 'NORMAL_USER'@'%';
GRANT ALL PRIVILEGES on db3.* to 'NORMAL_USER'@'%';
GRANT ALL PRIVILEGES on db4.* to 'NORMAL_USER'@'%';

In the Dockerfile i have written:

ADD data.sql data.sql

In the docker-compose.yml file I have written:

version: '3.3'
services:
  mysql:
    container_name: mysql
    image: mysql
    build:
      dockerfile: ./Dockerfile
    environment:
      MYSQL_ROOT_PASSWORD: ROOT_USER_PASS
      MYSQL_USER: NORMAL_USER
      MYSQL_PASSWORD: NORMAL_USER_PASS
    ports:
      - "5000:3306"
    networks:
      - internal_net
    volumes:
      - mysql
    restart: unless-stopped
networks:
  internal_net:
    driver: bridge

volumes:
  mysql:

CodePudding user response：

Before we start adding this feature, we need the current error in the docker-compose.yml: the volume mysql needs a mount location. I suspect that it should be mounted as data-directory to /var/lib/mysql. Please correct the path if it should be mounted somewhere else:

...
services:
  mysql:
    ...
    volumes:
      - mysql:/var/lib/mysql
    ...

The hub.docker.com page of the MySQL container states under Initializing a fresh instance that:

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. ...

The script is only executed when no data is found in the data directory (/var/lib/mysql by default). Thus, we can simply mount data.sql to this directory through a volume (docs.docker.com):

...
services:
  mysql:
    ...
    volumes:
      - mysql:/var/lib/mysql
      - ./data.sql:/docker-entrypoint-initdb.d/data.sql:ro
    ...