Skip to content

Instantly share code, notes, and snippets.

@geraldvillorente
Created June 14, 2017 05:03
Star You must be signed in to star a gist
Save geraldvillorente/4c60e7fdb5562f443f16ad2bbe4235ce to your computer and use it in GitHub Desktop.
How to import database in MySQL in Docker?

This is a simple way of importing MySQL database in Docker.

  1. In you Dockerfile you must have a shared folder. Shared folder is a directory in your host machine that is mounted to Docker instance.

  2. Put the exported sql file in the shared folder.

  3. Login to your Docker instance via docker exec -it DOCKER_CONTAINER_ID bin/bash.

  4. Login to MySQL via mysql -u USERNAME -p.

  5. While in MySQL CLI, create a database via create database DB_NAME;.

  6. While in MySQL CLI, use the database you just created via use DB_NAME;.

  7. While in MySQL CLI, import the sql file via source /path/to/file.sql.

Done

@sandipklevu
Copy link

  1. change directory to where your db is located using cd command i.e. cd /var/www/html/db
  2. docker exec -i container_name mysql -uroot -ppassword db_name < db.sql

change container_name, username and password, db_name and db_sql file if need

@arc-arnob
Copy link

Guys where the data.sql file must be exactly ?

Same question. I pass my database name in docker-compose file but the service creates its own table and I don't know where. Can some help with keeping beginners in mind?

@JuanVqz
Copy link

JuanVqz commented Sep 7, 2020

@arc-arnob depends on what kind of guide you follow, in my example, the SQL file must be in the host machine.

@arc-arnob
Copy link

@arc-arnob depends on what kind of guide you follow, in my example, the SQL file must be in the host machine.

Yeah I figured it out, did the following configuration in docker-compose.yml:
mysqlcontainer:
image: mysql
container_name: mysqlcontainer
volumes:
- ./docker/mysql/data:/var/lib/mysql:rw
- ./database/schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro
environment:
MYSQL_DATABASE: employee
MYSQL_USER: arnob
MYSQL_PASSWORD: root123
MYSQL_ROOT_PASSWORD: root123

@edmundcwm
Copy link

I had to restart Docker after running this - docker exec -i container_id mysql -uroot -ppassword db_name < db.sql. Works beautifully after that!

@Ericwonne
Copy link

docker exec -i container_name mysql -uroot dbname < data.sql;

Saved my hour

@sandy6666
Copy link

Thanks for the manual!
$ docker cp file.sql <CONTAINER ID>:/file.sql worked for me.

worked for me

@yehanny
Copy link

yehanny commented Jan 26, 2021

cmd /c "docker exec -i $mysql_container mysql -uuser -ppassword dbname < data.sql"

Yes, confirmed. This command worked on Windows 10 using docker on a running Mysql 8.0 server

@jea-ms
Copy link

jea-ms commented Feb 19, 2021

Using PowerShell in Window 10 gives

The '<' operator is reserved for future use.

I wrapped it up with cmd /c and it works:

cmd /c "docker exec -i $mysql_container mysql -uuser -ppassword dbname < data.sql"

I also have this problem and this works for me. Thanks

@clubztutoring
Copy link

Put the exported sql file in the shared folder.

Thanks for the manual!
$ docker cp file.sql <CONTAINER ID>:/file.sql worked for me.

worked for me

confirmed (CentOS 7)

@zainulhasan
Copy link

Using docker-compose you can import mysql.sql file as volume thus inserting into database on startup.

volumes: - ./datadump.sql:/docker-entrypoint-initdb.d/datadump.sql

did not work for me. Any solution?

@jeffsta9
Copy link

jeffsta9 commented Jun 18, 2021

Using docker-compose you can import mysql.sql file as volume thus inserting into database on startup.
volumes: - ./datadump.sql:/docker-entrypoint-initdb.d/datadump.sql

did not work for me. Any solution?

On linux (Centos 7), this works for me:
Put the exported SQL file in the shared folder: $ docker cp <FILE_NAME>.sql <CONTAINER ID>:/<FILE_NAME>.sql

Follow the instructions in the original post:
log into mysql: $ mysql -u<USERNAME> -p
create a database: mysql> create database <DB_NAME>;
use the database: mysql> use <DB_NAME>;
import the SQL file: mysql> source ./<FILE_NAME>.sql

@yehanny-ldm
Copy link

You can use an alternative Dockerfile to build your database like this.

docker/mysql/Dockerfile and copy to same folder your .sql file

FROM mysql:5.7.34

# Copy content from .sql to Docker mysql container
COPY ./docker/mysql/mydatabase.sql /docker-entrypoint-initdb.d/init.sql

EXPOSE 3306

Then move to another Dockerfile your server for example:

docker/apache/Dockerfile

FROM php:7.3-apache

USER root

RUN apt-get update

WORKDIR /var/www/html

Then in your docker-compose.yaml file use the Dockerfiles created like this:


services:
    db:
        build:
            context: .
            dockerfile: docker/mysql/Dockerfile
    app:
        build:
            context: .
            dockerfile: docker/apache/Dockerfile

I use this method for all my docker-compose.yaml and it works for me, I hope you found this solution helpful for you... :)

@zainulhasan
Copy link

is method for all my docker-compose.yaml and it works for me, I hope you found this solution helpful for you... :)

Thanks,
I will try your solution. Let's hope for the best

@dan-developer
Copy link

Just run the simple command below:

docker exec -i mysql_container mysql -uroot -psecret mysql < db.sql

The best method.

@maurisrx
Copy link

maurisrx commented Aug 5, 2021

docker exec -i mysql_container mysql -uroot -psecret database_name < db.sql

Thanks! The db.sql file is relative to the current path of your command line.

@dongshou
Copy link

I execute docker exec -i mysql_container mysql -uroot -psecret database_name < db.sql, throw error following:
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

@dragon99k
Copy link

dragon99k commented Jun 13, 2022

I run docker exec -i [mysql-container] mysql -u[user] -p[password] [db_name] < [sql path] in powershell.
but below error occured.

< is not allowed in powershell.

so I used command prompt and the operation succeeded.

Thanks! I hope this comment to help you.

@dragon99k
Copy link

dragon99k commented Jun 13, 2022

I execute docker exec -i mysql_container mysql -uroot -psecret database_name < db.sql, throw error following: 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

this error is unexpected error.
you should check your environment.

@bruteforks
Copy link

Just run the simple command below:

docker exec -i mysql_container mysql -uroot -psecret mysql < db.sql

so simple and elegant. thank you!

@fontanon
Copy link

fontanon commented Feb 1, 2023

Just run the simple command below:

docker exec -i mysql_container mysql -uroot -psecret mysql < db.sql

+1

@dragon99k
Copy link

so simple and elegant. thank you!

Thank you for your comments.

@1kevinson
Copy link

Just run the simple command below:

docker exec -i mysql_container mysql -uroot -psecret mysql < db.sql

The best method.

So simple, thanks!

@dragon99k
Copy link

dragon99k commented Jun 28, 2023 via email

@dongshou
Copy link

I execute docker exec -i mysql_container mysql -uroot -psecret database_name < db.sql, throw error following: 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

I have resolved this issue because the first line in the generated SQL file is not an SQL command, but a warning message

@aginanjar
Copy link

You can use an alternative Dockerfile to build your database like this.

docker/mysql/Dockerfile and copy to same folder your .sql file

FROM mysql:5.7.34

# Copy content from .sql to Docker mysql container
COPY ./docker/mysql/mydatabase.sql /docker-entrypoint-initdb.d/init.sql

EXPOSE 3306

Then move to another Dockerfile your server for example:

docker/apache/Dockerfile

FROM php:7.3-apache

USER root

RUN apt-get update

WORKDIR /var/www/html

Then in your docker-compose.yaml file use the Dockerfiles created like this:


services:
    db:
        build:
            context: .
            dockerfile: docker/mysql/Dockerfile
    app:
        build:
            context: .
            dockerfile: docker/apache/Dockerfile

I use this method for all my docker-compose.yaml and it works for me, I hope you found this solution helpful for you... :)

thanks for sharing.. 👍

@asadbekkuz
Copy link

@itsmeDontworry it means your backup file

docker exec -i mysql_container mysql -uroot -psecret database < database_backup.sql

From left to right: docker It's the docker command exec When you want to run something in the container mysql_container It's your container’s name mysql It's the command itself like mysql --version -u It asks for your MySQL user like -uroot -p It asks for your MySQL password like -ppassword database It's your database name, previously created database_backup.sql It's the backup file you want to import

Help, It's no worked for me, I gave error message like "bash:database_backup.sql: no such file or directory:"
my database_backup.sql file where must be located.

@thamvtn123
Copy link

@itsmeDontworry it means your backup file

docker exec -i mysql_container mysql -uroot -psecret database < database_backup.sql

From left to right: docker It's the docker command exec When you want to run something in the container mysql_container It's your container’s name mysql It's the command itself like mysql --version -u It asks for your MySQL user like -uroot -p It asks for your MySQL password like -ppassword database It's your database name, previously created database_backup.sql It's the backup file you want to import

Help, It's no worked for me, I gave error message like "bash:database_backup.sql: no such file or directory:" my database_backup.sql file where must be located.

Put it in the same folder that you put file docker-compose.yml

@nc3man
Copy link

nc3man commented Nov 6, 2023

Using PowerShell in Window 10 gives

The '<' operator is reserved for future use.

I wrapped it up with cmd /c and it works:
cmd /c "docker exec -i $mysql_container mysql -uuser -ppassword dbname < data.sql"

I also have this problem and this works for me. Thanks
Thanks you for the powershell tip re: "<" !

@still-breathing
Copy link

Thanks a lot this helped me a lot, i wish I stumbled upon it an hour ago. Kudos to you brother

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment