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

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"

This question is related to mysql docker

The answer is


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

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


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 import database afterwards:

docker exec -i mysql-container mysql -uuser -ppassword name_db < data.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.


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"

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


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.


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


Import using docker-compose

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

I can import with this command

docker-compose exec -T mysql mysql -uroot -proot mydatabase < ~/Desktop/mydatabase_2019-10-05.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"


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


Examples related to mysql

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

Examples related to docker

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