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"

You can import database afterwards:

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

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"

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:

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

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

Import using docker-compose

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

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.

I can import with this command

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

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

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"

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

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.

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)

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