Dockerize MySQL with database and tables

Question

I want to create a MySQL container with an initial database and its tables, from a Dockerfile.

I also have a specific /etc/mysql/mysql.conf.d/mysqld.cnf file (I can simply use ADD directive in Dockerfile.)

I want my Dockerfile to create a MySQL image with a database named, for example, DBNAME and with the following tables:

CREATE TABLE utente (
     ID int  NOT NULL AUTO_INCREMENT,
     nome VARCHAR(255),
     cognome VARCHAR(255),
     username VARCHAR(255),
     password VARCHAR(255),
     PRIMARY KEY (ID)
);

CREATE TABLE sdplines (
    ID int NOT NULL AUTO_INCREMENT,
    value VARCHAR(255),
    session_id VARCHAR(255),
    u_id int,
    PRIMARY KEY (ID),
    FOREIGN KEY (u_id) REFERENCES utente(ID)
);

I've already searched to solve my problem here but I've found no solution.

Answer 1

Copy your .sql file in the docker-entrypoint-initdb.d directory.

.sql file:

CREATE DATABASE  IF NOT EXISTS `your_db_name`
...

---

Dockerfile:

FROM mysql:5.7.17

MAINTAINER ...

ENV MYSQL_ROOT_PASSWORD=...
ENV MYSQL_DATABASE=your_db_name   

COPY your_file.sql /docker-entrypoint-initdb.d/

ENTRYPOINT ["docker-entrypoint.sh"]
CMD ["mysqld"]

Reference: https://hub.docker.com/_/mysql/, "Initializing a fresh instance" section:

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

Answer 2

If your using a dockerfile - then add something like

ADD schema.sql /docker-entrypoint-initdb.d

Or if your using docker-compse

volumes:
 - ./SQL:/docker-entrypoint-initdb.d

In my case the last one has a directory called SQL with the scripts in it.