Docker Compose mysql import .sql

Question

I'm having trouble importing an .sql dump file with docker-compose. I've followed the docs, which apparently will load the .sql file from docker-entrypoint-initdb.d. However, when I run docker-compose up, the sql file is not copied over to the container.

I've tried stopping the containers with -vf flag, but that didn't work either. Am I doing something wrong in my .yml script?

I have dump.sql in the directory database/db-dump/ in the root where my compose file is.

frontend:
  image: myimage
  ports:
   - "80:80"
  links:
   - mysql
mysql:
  image: mysql
  ports:
   - "3306:3306"
  environment:
    MYSQL_ROOT_PASSWORD: rootpass
    MYSQL_USER: dbuser
    MYSQL_PASSWORD: userpass
    MYSQL_DATABASE: myimage_db
  volumes:
   - ./database/db-dump:/docker-entrypoint-initdb.d

Your docker-compose is working on my machine. Attach please dir structure of your dump location on host, and how you are checking what file was copied to container. — Cortwave, Apr 14 '16 at 11:26
The dir structure is "database > db-dump > dump.sql" once the compose is up a conect to the mysql container and check the docker-entrypoint-initdb.d dir which is always empty — GafferG, Apr 14 '16 at 15:05

score 73 · Answer 1 · answered Feb 26 '19 at 13:28

73

This worked for me,

version: '3.1'

services:

  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    volumes:
      - ./mysql-dump:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: example
      MYSQL_DATABASE: ecommerce

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

mysql-dump must be a directory. All the .sql's in the directory will be imported.

answered Feb 26 '19 at 13:28

Sridhar Sg

1,546
13
21

2

remember, your SQL statement may need `CREATE DATABASE [name]` and `USE [name]` statements, you can confirm your SQL file will work with `docker exec -i [container_id] sh -c 'exec mysql -uroot -p"[password]"' < /path/to/db/files/dump.sql`, if it works here it should work in your docker-compose file – farinspace Sep 14 '20 at 23:01

score 45 · Accepted Answer · answered Apr 14 '16 at 15:10

45

After many attempts with the volumes setting i found a workaround

I created another image based on mysql with the following in the Dockerfile

FROM mysql:5.6

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

Then removed the volumes from compose and ran the new image

frontend:
  image: myimage
  ports:
   - "80:80"
  links:
   - mysql
mysql:
  image: mymysql
  ports:
   - "3306:3306"
  environment:
    MYSQL_ROOT_PASSWORD: rootpass
    MYSQL_USER: dbuser
    MYSQL_PASSWORD: userpass
    MYSQL_DATABASE: myimage_db

This way the dump is always copied over and run on startup

answered Apr 14 '16 at 15:10

GafferG

993
1
8
11

2

You should take a look at : https://gist.github.com/spalladino/6d981f7b33f6e0afe6bb – creekorful Dec 02 '17 at 09:07
1

Wait, there's no reason to build a new image just to import some files: it would make sense only if that data is never changing, and you're sharing the image to your collegues, so they don't need to import the file everytime. But for normal cases, using volumes is the solution to adopt. – funder7 Jun 12 '21 at 19:34

score 23 · Answer 3 · edited May 04 '18 at 14:53

This appears on the documentation page of Docker MySQL image: https://hub.docker.com/_/mysql/

Initializing a fresh instance

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. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

/docker-entrypoint-initdb.d is only read at container creation. And OP was asking for a database "update" when he run docker-compose up. — creekorful, Dec 02 '17 at 09:04

score 20 · Answer 4 · edited May 28 '21 at 19:38

Mysql database dump schema.sql is residing in the /mysql-dump/schema.sql directory and it creates tables during the initialization process.

docker-compose.yml:

mysql:
    image: mysql:5.7
    command: mysqld --user=root
    volumes:
      - ./mysql-dump:/docker-entrypoint-initdb.d
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}

score 4 · Answer 5 · edited Sep 23 '19 at 16:16

I was having a similar issue with mysql where I would mount a local directory at /configs/mysql/data containing a mydatabasedump.sql file via docker-compose to the docker-entrypoint-initdb.d volume, the file would get loaded on to the container but not execute or populate the database when the container initialized. My intial docker-compose.yml looke like this:

#docker-compose.yml
version: '3'
services:
    db:
        build: ./build/mysql/ #this is pointing to my Dockerfile
        container_name: MYSQL_Database
        restart: always
        environment:
        MYSQL_PORT: 3306
        MYSQL_ROOT_PASSWORD: admin
        MYSQL_DATABASE: my_app_database
        MYSQL_USER: admin
        MYSQL_PASSWORD: admin
    volumes:
        - ./configs/mysql/data:/docker-entrypoint-initdb.d:

I found two working solutions for this problem:

The first came after I logged in the running container and confirmed that mydatabasedump.sq file was present and executable in the container's docker-entrypoint-initdb.d directory; I created and added a bash script to my local /configs/mysql/data directory called dump.sh that excuted after the container was initialized. It contains a single mysql command that copies my_database_dump.sql to my_app_database. The bash script looks like this

    #!/bin/bash
    #dump.sh
    mysql -uadmin -padmin my_app_database < my_database_dump.sql 
    #end of dump.sh

I executed this script via my Dockerfile in the ENTRYPOINT directive like this:

    #Dockerfile
    FROM mysql:5.5
    ENTRYPOINT [ "dump.sh" ]
    EXPOSE 80
    #end of Dockerfile

After realizing the initial issue was due to the volumes being mouted after the cotainer is built and therefore not intilizing the database with the dump file (or executing any scripts in that directory) at boot time, the second solution was simply to move the volumes directive in my compose-file above the built directive. This worked and allowed me to remove the dump.sh scrip and the DOCKERENTRY directive in my Dockerfile. The modified docker-compose.yml looks like this

#docker-compose.yml
version: '3'
services:
    db:
        volumes:
          - ./configs/mysql/data:/docker-entrypoint-initdb.d
       build: ./build/mysql/ #this is pointing to my Dockerfile
         container_name: MYSQL_Database
         restart: always
     environment:
         MYSQL_PORT: 3306
         MYSQL_ROOT_PASSWORD: admin
         MYSQL_DATABASE: my_app_database
         MYSQL_USER: admin
         MYSQL_PASSWORD: admin

Thank you! This has helped in my case while the others didn't. — PepperAddict, Apr 12 '21 at 03:35

user7555459 · Answer 6 · 2021-05-05T08:40:17.357

I also have this problem. I mount a local directory at ./mysql-dump containing a init.sql file via docker-compose to the docker-entrypoint-initdb.d volume, the file would get loaded on to the container but not execute or populate the database when the container initialized. My intial docker-compose.yml looke like this:

mysqld:
    image: mysql
    container_name: mysqld
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/my.cnf
      - ./init:/docker-entrypoint-initdb.d
    env_file: .env
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_DATABASE=fendou
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci 
             --default-authentication-plugin=mysql_native_password

but it doesn't work for me. I found another working solutions for this problem: add --init-file /data/application/init.sql to mysql command.change above configuration like

mysqld:
    image: mysql
    container_name: mysqld
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/my.cnf
      # - ./init:/docker-entrypoint-initdb.d
    env_file: .env
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_DATABASE=fendou
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci 
             --default-authentication-plugin=mysql_native_password
             --init-file /docker-entrypoint-initdb.d/init.sql  #attention here

hope it help for you

exic · Answer 7 · 2022-12-06T19:59:01.580

I wanted to keep the original setup of the container, so I tried a restore on the already running container. This seemed to work:

cat dump.sql | docker-compose exec -T db mysql -h localhost -u root -psomewordpress -v

But it was very slow and the verbose output seemed to be buffered, so I tried:

docker-compose cp dump.sql db:/tmp/
docker-compose exec db sh -c "mysql -h localhost -u root -psomewordpress -v < /tmp/dump.sql"

Which at least provided faster feedback.

Might be useful for someone? Looks like it was mainly slow because I used --skip-extended-insert on the dump, without the extended inserts it went faster

Docker Compose mysql import .sql

7 Answers7

Linked