How can I create a mysql db with Docker compose?

Question

I'm trying to host on docker an application which uses MySQL db. I'm using docker compose. my yml file looks like this:

version: '2'
volumes:
  data_sql:    
services:  
medical-mysql:
    image: mysql
    hostname: medical-mysql    
    volumes:    
     - data_sql:/dbcreation.sql
    environment:
      MYSQL_DATABASE: Medical
      MYSQL_ROOT_PASSWORD: root
      STARTUP_SQL: data_sql
    ports:
    - "3306:3306"
  medical-main:
    build: .
    ports:
     - "8080:8080"    
    depends_on:
     - medical-mysql

I have a dbcreation.sql which creates the db schema and is kept in the yml folder. When I run the yml file it seems as if the file is not running.

What have I missed?

data_sql is simply the volume I created to host the creation file. I couldn't find any other way to do it, and this one also doesn't work. — Izhar Lotem
– Izhar Lotem, Commented Mar 8, 2016 at 4:33
It seems you are mounting data_sql in dbcreation.sql. You never create the db. You need to copy in /var/lib/mysql (and not dbcreation.sql if you want to use it you need to execute something like that : mysql -u user db < dbcreation.sql ) — Isky
– Isky, Commented Mar 8, 2016 at 10:13

StumpDK · Accepted Answer · 2017-08-04 11:23:20Z

31

Simply just put your .sql file (dbcreation.sql) in a folder (ie. /mysql_init) and add the folder as a volume like this:

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

The MySQL image will execute all .sql, .sh and .sql.gz files in /docker-entrypoint-initdb.d on startup.

answered Aug 4, 2017 at 11:23

StumpDK

4415 silver badges6 bronze badges

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

1 Comment

catch32 Over a year ago

where this folder should be? At project level or a docker container?

ashatrov · Accepted Answer · 2016-03-08 14:38:03Z

26

1) Create dump file dbcreation.sql

2) Create import.sh file:

#!/usr/bin/env bash
mysql -u root -p$MYSQL_ROOT_PASSWORD < /tmp/dbcreation.sql

3) Create docker-compose.yaml

database:
  image: mysql
  container_name: database.dev
  command: mysqld --user=root --verbose
  volumes:
    - ./dbcreation.sql:/tmp/dbcreation.sql
    - ./import.sh:/tmp/import.sh
  ports:
    - "3306:3306"
  environment:
    MYSQL_DATABASE: "test"
    MYSQL_USER: "test"
    MYSQL_PASSWORD: "test"
    MYSQL_ROOT_PASSWORD: "root"
    MYSQL_ALLOW_EMPTY_PASSWORD: "yes"

"- ./dbcreation.sql:/tmp/dbcreation.sql" - "- local_path:path_inside_container"

4) Run

docker-compose up
docker exec database.dev bash /tmp/import.sh

edited Mar 8, 2016 at 14:38

answered Mar 8, 2016 at 14:23

ashatrov

1,1227 silver badges11 bronze badges

4 Comments

Izhar Lotem Over a year ago

I had a problem implementing your answer since once my main container is up, it needs the db constructed. It did give me many clues of getting where I wanted. I split the mysql container creation into a seperate yml file. I made an sh file with the following: docker-compose -f mysql.yml up -d sleep 10; docker exec medical-mysql bash /tmp/import.sh docker-compose up It works. Is there a better way to make sure the mysql container is ready than the sleep?

ashatrov Over a year ago

@IzharLotem it is not about container, it is about application inside. Container is ready, but application inside the container doesn't. You need to resolve it on application level. You can check health of MySQL database in your "main container" or you can overwrite CMD inside Dockerfile and put flag somewhere after import will be done. Anyway it is not docker job.

lovebeer84 Over a year ago

Running against 8.0.11, I needed to change "command" to: command: mysqld --user=root --default-authentication-plugin=mysql_native_password This allowed me to connect via MySQL Workbench (v6.3)

eli Over a year ago

I get: /tmp/import.sh: /tmp/import.sh: Is a directory

Collectives™ on Stack Overflow

How can I create a mysql db with Docker compose?

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related