[mysql] 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:

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

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

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

  image: mysql:latest
  container_name: mysql-container
    - 3306:3306
    - mysql-data
    MYSQL_DATABASE: name_db
    MYSQL_USER: user
    MYSQL_PASSWORD: password

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

This question is related to mysql docker

The answer is

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

  image: mysql:latest
  container_name: mysql-container
    - 3306:3306
    - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
    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"

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

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 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


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

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

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

then inside mysql container execute source \file.sql

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"

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

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

Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with docker tag:

standard_init_linux.go:190: exec user process caused "no such file or directory" - Docker What is the point of WORKDIR on Dockerfile? E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation How do I add a user when I'm using Alpine as a base image? docker: Error response from daemon: Get https://registry-1.docker.io/v2/: Service Unavailable. IN DOCKER , MAC How to fix docker: Got permission denied issue pull access denied repository does not exist or may require docker login Docker error: invalid reference format: repository name must be lowercase Docker: "no matching manifest for windows/amd64 in the manifest list entries" OCI runtime exec failed: exec failed: (...) executable file not found in $PATH": unknown Docker: Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock Can't create a docker image for COPY failed: stat /var/lib/docker/tmp/docker-builder error Is it safe to clean docker/overlay2/ Docker - Bind for failed: port is already allocated docker : invalid reference format Docker CE on RHEL - Requires: container-selinux >= 2.9 /bin/sh: apt-get: not found Docker: How to delete all local Docker images How to see docker image contents How to remove docker completely from ubuntu 14.04 Kubernetes Pod fails with CrashLoopBackOff Cannot connect to the Docker daemon at unix:/var/run/docker.sock. Is the docker daemon running? How to assign more memory to docker container Cannot connect to the Docker daemon on macOS Import data.sql MySQL Docker Container COPY with docker but with exclusion How to check if the docker engine and a docker container are running? Docker "ERROR: could not find an available, non-overlapping IPv4 address pool among the defaults to assign to the network" How to get IP address of running docker container How to run docker-compose up -d at system start up? Dockerfile if else condition with external arguments What does --net=host option in Docker command really do? Docker build gives "unable to prepare context: context must be a directory: /Users/tempUser/git/docker/Dockerfile" Error "The input device is not a TTY" How to create a DB for MongoDB container on start up? How to install "ifconfig" command in my ubuntu docker image? How to use local docker images with Minikube? How to clear the logs properly for a Docker container? accessing a docker container from another container How to stop docker under Linux How to specify Memory & CPU limit in docker compose version 3 docker build with --build-arg with multiple arguments How to mount a single file in a volume Add Insecure Registry to Docker Settings to Windows Firewall to allow Docker for Windows to share drive Can Windows Containers be hosted on linux? denied: requested access to the resource is denied : docker Understanding "VOLUME" instruction in DockerFile ARG or ENV, which one to use in this case? How to persist data in a dockerized postgres database using volumes