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.

A quick search on SO has provided me [this](https://stackoverflow.com/questions/29145370/docker-initialize-mysql-database-with-schema). Check if this helps! — N00b Pr0grammer, May 29 '17 at 10:24
Possible duplicate of [Docker - Initialize mysql database with schema](https://stackoverflow.com/questions/29145370/docker-initialize-mysql-database-with-schema) — N00b Pr0grammer, May 29 '17 at 10:25

tgogos · Answer 1 · 2017-05-29T10:43:40.773

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

score 2 · Accepted Answer · answered May 29 '17 at 10:35

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.

answered May 29 '17 at 10:35

Nigel Ren

56,122
11
43
55

I use Dockerfile. Does schema.sql contain "create database DBNAME; " too? Or it only contains the two create table? – pier92 May 29 '17 at 10:37
The one I use only has create tables - but you can put anything you want in there, users, databases etc. – Nigel Ren May 29 '17 at 10:38
Ok. So, i add this .sql with the two tables. But how can i create the related database? – pier92 May 29 '17 at 10:40
1

If you look at MYSQL_DATABASE, in the dockerfile - `ENV MYSQL_DATABASE=` – Nigel Ren May 29 '17 at 10:45

Dockerize MySQL with database and tables

2 Answers2

Linked