Import data.sql MySQL Docker Container

Question

If I have a data.sql, how I can import database to my mysql docker container? How I can import database data. In a dockerised world this adds a layer of complexity. some methods please.

Here my docker-compose.yml:

nginx:
  build: ./nginx/
  container_name: nginx-container
  ports:
    - 80:80
  links:
    - php
  volumes_from:
    - app-data

php:
  build: ./php/
  container_name: php-container
  expose:
    - 9000
  links:
    - mysql
  volumes_from:
    - app-data

app-data:
  image: php:7.0-fpm
  container_name: app-data-container
  volumes:
    - ./www/html/:/var/www/html/
  command: "true"

mysql:
  image: mysql:latest
  container_name: mysql-container
  ports:
    - 3306:3306
  volumes_from:
    - mysql-data
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: name_db
    MYSQL_USER: user
    MYSQL_PASSWORD: password

mysql-data:
  image: mysql:latest
  container_name: mysql-data-container
  volumes:
    - /var/lib/mysql
  command: "true"

Copy the SQL file to the container then load data infile. – Jay Blanchard May 09 '17 at 21:21 — Jay Blanchard, May 09 '17 at 21:21

score 230 · Answer 1 · answered May 10 '17 at 08:22

230

You can import database afterwards:

docker exec -i mysql-container mysql -uuser -ppassword name_db < data.sql

answered May 10 '17 at 08:22

Lauri

4,336
3
18
18

31

If using docker-compose, you have to get the container by: `docker exec -i $(docker-compose ps -q mysql-container) mysql …`. It does not support reading from stdin at the moment, as described [here](https://github.com/docker/compose/issues/3352). – slhck Sep 07 '17 at 07:32
2

how to do this on docker-compose? – iamjc015 Jul 02 '18 at 12:12
10

I am. getting error "the input device is not a TTY" – kamal May 10 '19 at 19:21
1

**FYI:** use `-p` without password for security purpose. – Abdulla Nilam Jul 08 '19 at 06:55
3

@kamal did you use the `-t` option for `docker exec`? Juse use `-i` to avoid the error – Wolfgang Sep 04 '19 at 06:11
`docker exec -i {{CONTAINER ID}} mysql -u{{USERNAME}} -p{{PASSWORD}} {{DATABASE NAME}} < {{DUMP DATA}}.sql` , worked fo me – Amit Dwivedi Jun 09 '21 at 11:33
@iamjc015: just use docker-compose exec - no need to parse docker-compose ps output like this – funder7 Jun 12 '21 at 19:56
I was able to import sql zip file `data.sql.zip` in localhost/phpmyadmin. I cannot import in docker container, any idea how to? – Ankit.Z Nov 15 '22 at 11:19
if you use docker compose you can just use `docker compose exec ` – btx May 08 '23 at 14:27

Eugen Mayer · Answer 2 · 2018-10-29T08:10:06.197

129

Mount your sql-dump under/docker-entrypoint-initdb.d/yourdump.sql utilizing a volume mount

mysql:
  image: mysql:latest
  container_name: mysql-container
  ports:
    - 3306:3306
  volumes:
    - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: name_db
    MYSQL_USER: user
    MYSQL_PASSWORD: password

This will trigger an import of the sql-dump during the start of the container, see https://hub.docker.com/_/mysql/ under "Initializing a fresh instance"

edited Oct 29 '18 at 08:10

answered May 09 '17 at 22:00

Eugen Mayer

8,942
4
33
57

3

+1 @EugenMayer. Definitely anyone using Compose v1 should move to latest v3 syntax: https://docs.docker.com/compose/compose-file/ – Mano Marks May 10 '17 at 00:25
2

V3 is not a successor of V2, even if that's inconvenience. V3 is basically swarm only, it's rather a fork then a successor. See Dockers comments on this. One of the huge issues is the missing volumr_from mount possibility – Eugen Mayer Sep 20 '17 at 06:05
32

Guys, remember that if you create a persistent volume for the database service, it will not check for new .sql files unless you recreate it. Lost few hours to this, I hope no one else goes through the same. – Dazag Jul 16 '18 at 13:21
1

@DhwanilPatel you have to use --build --force-recreate options. https://docs.docker.com/compose/reference/up/ E.g. docker-compose up --build --force-recreate – Dazag Nov 08 '19 at 09:09
1

@Dazag Actually i already use this command : "sudo docker-compose up -d --force-recreate --build " but nothing happen – Dhwanil Patel Nov 08 '19 at 09:14

Hana Alaydrus · Accepted Answer · 2018-07-25T11:06:03.023

77

I can't seem to make this work with the latest mysql or mysql:5.7. So I use mariaDB instead. Here is my docker-compose.yaml code.

version: '3'

services:
  mysql:
    image: mariadb:10.3
    container_name: mariadb
    volumes:
      - container-volume:/var/lib/mysql
      - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: name_db
    ports:
      - "3306:3306"

volumes:
  container-volume:

edited Jul 25 '18 at 11:06

answered Apr 05 '18 at 20:14

Hana Alaydrus

2,225
16
19

1

When I try to use this method, I end up with a directory for dump.sql? Any idea on why? eg: bash into mariadb container & : `cd /docker-entrypoint-initdb.d/dump.sql/` & then I'm in a directory? What would cause a directory to be created instead of either not having the .sql file there at all or throwing some kind of error? my relevant docker-compose.yml for the mariadb container: `mariadb: image: mariadb:latest ... volumes: - app-vhosts:/path/before/sqldump #creates a directory? - ./path/to/sql/dump.sql:/docker-entrypoint-initdb.d/dump.sql ` – K8sN0v1c3 Sep 22 '20 at 19:14
Remember to clear volumes first before testing, as the file will only be run on initial up. `docker-compose down --volumes && docker-compose rm -v && docker-compose up` – FooBar Mar 21 '22 at 13:17

Excellence Ilesanmi · Answer 4 · 2018-07-31T22:35:00.983

32

Another option if you don't wanna mount a volume, but wanna dump a file from your local machine, is to pipe cat yourdump.sql. Like so:

cat dump.sql | docker exec -i mysql-container mysql -uuser -ppassword db_name

See: https://gist.github.com/spalladino/6d981f7b33f6e0afe6bb

edited Jul 31 '18 at 22:35

answered Jul 31 '18 at 22:21

Excellence Ilesanmi

3,295
1
18
17

Works perfectly if `dump.sql` contains `source` files – Chu Nov 22 '18 at 18:30
Just what I needed to make a sql import work in my shell script. Thanks! – Jono Jun 02 '23 at 12:14

score 26 · Answer 5 · answered Feb 10 '21 at 15:20

26

Just write docker ps and get the container id and then write the following;

docker exec -i your_container_id mysql -u root -p123456 your_db_name < /Users/your_pc/your_project_folder/backup.sql

answered Feb 10 '21 at 15:20

Tuncay Elvanagac

1,048
11
13

Slipstream · Answer 6 · 2019-03-28T18:48:03.647

15

Import using docker-compose

cat dump.sql | docker-compose exec -T <mysql_container> mysql -u <db-username> -p<db-password> <db-name>

edited Mar 28 '19 at 18:48

answered Feb 14 '19 at 16:25

Slipstream

13,455
3
59
45

I'm getting error - mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump: [Warning] Using a password on the command line interface can be insec' at line 1 read unix @->/var/run/docker.sock: read: connection reset by peer – R Sun May 22 '19 at 22:12
pls check, that this warning does not already appear in your dump.sql: When you produced the file using mysqldump and gave the password on the command-line, the warning will end up in the dumped file. – Stefan Frank Apr 02 '20 at 08:54

score 9 · Answer 7 · answered Jul 23 '19 at 04:05

9

combine https://stackoverflow.com/a/51837876/1078784 and answers in this question, I think the best answer is:

cat {SQL FILE NAME} | docker exec -i {MYSQL CONTAINER NAME} {MYSQL PATH IN CONTAINER} --init-command="SET autocommit=0;"

for example in my system this command should look like:

cat temp.sql | docker exec -i mysql.master /bin/mysql --init-command="SET autocommit=0;"

also you can use pv to moniter progress:

cat temp.sql | pv | docker exec -i mysql.master /bin/mysql --init-command="SET autocommit=0;"

And the most important thing here is "--init-command" which will speed up the import progress 10 times fast.

answered Jul 23 '19 at 04:05

Matt.Cai

136
1
3

i get stdin is not a tty...getting really bored with this, what is the solution to this? – Bram B Aug 07 '19 at 15:14
Don't understand what your mean, maybe you can give me more detail? – Matt.Cai Aug 08 '19 at 14:53
1

using `pv temp.sql | ...` instead of `cat temp.sql | pv | ...` allows you to see the progress of loading your sql into mysql. After the file is "sent" do mysql it will take a bit to process the content, so 100% means it's received fully but not executed fully. Still it already is an improvement for big files. – luckydonald Feb 09 '21 at 18:15
Docker-compose variant is replacing `docker exec -i` with `docker-compose exec -T`. – luckydonald Feb 09 '21 at 18:16

score 9 · Answer 8 · edited Oct 05 '19 at 14:59

9

I can import with this command

docker-compose exec -T mysql mysql -uroot -proot mydatabase < ~/Desktop/mydatabase_2019-10-05.sql

edited Oct 05 '19 at 14:59

Dharman

30,962
25
85
135

answered Oct 05 '19 at 10:16

iamyusuf

157
2
9

it hard to tell what is what, but i'm doing it the same way `docker-compose exec -T [host] mysql -u [mysql user] -p[password] [database] < localfile.sql`. Besides that I think there is an error in your statement. There should be a space between `-u` and the username itself. – Gordon Freeman Nov 06 '19 at 10:55

Sakhri Houssem · Answer 9 · 2021-03-07T07:35:51.473

8

do docker cp file.sql <CONTAINER NAME>:/file.sql first

then docker exec -it <CONTAINER NAME> mysql -u user -p

then inside mysql container execute source \file.sql

edited Mar 07 '21 at 07:35

answered Dec 20 '20 at 10:54

Sakhri Houssem

975
2
16
32

Thakur Amit · Answer 10 · 2020-07-21T05:59:48.827

you can follow these simple steps:

FIRST WAY :

first copy the SQL dump file from your local directory to the mysql container. use docker cp command

docker cp [SRC-Local path to sql file] [container-name or container-id]:[DEST-path to copy to]

docker cp ./data.sql mysql-container:/home

and then execute the mysql-container using (NOTE: in case you are using alpine version you need to replace bash with sh in the given below command.)

docker exec -it -u root mysql-container bash

and then you can simply import this SQL dump file.

mysql [DB_NAME] < [SQL dump file path]

mysql movie_db < /home/data.sql

SECOND WAY : SIMPLE

docker cp ./data.sql mysql-container:/docker-entrypoint-initdb.d/

As mentioned in the mysql Docker hub official page.

Whenever a container starts for the first time, a new database is created with the specified name in MYSQL_DATABASE variable - which you can pass by setting up the environment variable see here how to set environment variables

By default container will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d folder. Files will be executed in alphabetical order. this way your SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

for more details you can always visit the official page

score 5 · Answer 11 · answered Nov 10 '20 at 13:59

Trying "docker exec ... < data.sql" in Window PowerShell responses with:

The '<' operator is reserved for future use.

But one can wrap it out with cmd /c to eliminate the issue:

cmd /c "docker exec -i mysql-container mysql -uuser -ppassword name_db < data.sql"

score 2 · Answer 12 · answered Apr 27 '20 at 11:14

This one work for me

$ docker exec -i NAME_CONTAINER_MYSQL mysql -u DB_USER -pPASSWORD DATABASE < /path/to/your/file.sql

First if do you want to know what is the NAME_CONTAINER_MYSQL, you should use this command below :

$ docker ps

In the output column NAME you will see the NAME_CONTAINER_MYSQL that do you need to replace in the command above.

score 1 · Answer 13 · edited Oct 05 '19 at 14:59

You can run a container setting a shared directory (-v volume), and then run bash in that container. After this, you can interactively use mysql-client to execute the .sql file, from inside the container. obs: /my-host-dir/shared-dir is the .sql location in the host system.

docker run --detach --name=test-mysql -p host-port:container-port  --env="MYSQL_ROOT_PASSWORD=my-root-pswd" -v /my-host-dir/shared-dir:/container-dir mysql:latest


docker exec -it test-mysql bash

Inside the container...

mysql -p < /container-dir/file.sql

Custom parameters:

test-mysql (container name)
host-port and container-port
my-root-pswd (mysql root password)
/my-host-dir/shared-dir and /container-dir (the host directory that will be mounted in the container, and the container location of the shared directory)

score -3 · Answer 14 · answered Aug 15 '19 at 20:13

-3

you can copy the export file for e.g dump.sql using docker cp into the container and then import the db. if you need full instructions, let me know and I will provide

answered Aug 15 '19 at 20:13

Jarjob

1

Import data.sql MySQL Docker Container

14 Answers14

Linked