CODEWITHSUNDEEP
mysqldocker
Israel Morales
Israel Morales

Reputation: 1643

Import data.sql MySQL Docker Container

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"

Upvotes: 164

Views: 248542

Answers (15)

Muhammad Fatkurozi
Muhammad Fatkurozi

Reputation: 31

These are my steps.

  1. Jump to MySQL and create a new database, ex: schools
  2. Open your terminal and type the command bellow, don't forget to change {user} and {pass}
docker exec -i mysqldb mysql -u {user} -p{pass} schools < schools_bak.sql
  1. Done!

Upvotes: 0

Sakhri Houssem
Sakhri Houssem

Reputation: 1063

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

Upvotes: 11

Tuncay Elvanagac
Tuncay Elvanagac

Reputation: 1342

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

Upvotes: 33

Denys Horobchenko
Denys Horobchenko

Reputation: 539

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"

Upvotes: 7

Thakur Amit
Thakur Amit

Reputation: 437

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

Upvotes: 6

Irwuin
Irwuin

Reputation: 623

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.

Upvotes: 3

Caleb Santos
Caleb Santos

Reputation: 89

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)

Upvotes: 1

iamyusuf
iamyusuf

Reputation: 137

I can import with this command

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

Upvotes: 9

Jarjob
Jarjob

Reputation: 1

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

Upvotes: -3

Matt.Cai
Matt.Cai

Reputation: 136

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.

Upvotes: 9

Slipstream
Slipstream

Reputation: 14742

Import using docker-compose

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

Upvotes: 16

Eugen Mayer
Eugen Mayer

Reputation: 9896

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"

Upvotes: 144

Excellence Ilesanmi
Excellence Ilesanmi

Reputation: 3525

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

Upvotes: 36

Hana Alaydrus
Hana Alaydrus

Reputation: 2220

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:

Upvotes: 78

Lauri
Lauri

Reputation: 4669

You can import database afterwards:

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

Upvotes: 259

Related Questions