How to import database in MySQL in Docker?

import.md

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

@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

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

👍

Adding host and port working for me
docker exec -i container_name mysql -h 0.0.0.0 -P 3306 -uUser -pPassword db_name < db_18_07.sql

1. After creating a new database, press Ctrl+D. Then exit.
2. Go to the directory where your dump is placed
3. docker exec -i container_name mysqldump -uusername -ppassword db_name<dump_file_name.sql

Use 'username' and password' as the username and ROOT_PASSWORD of mysql.

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

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?

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

@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

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

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

Saved my hour

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

worked for me

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

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

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)

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?

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

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... :)

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

Just run the simple command below:

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

The best method.

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.

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

Just run the simple command below:

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

so simple and elegant. thank you!

Just run the simple command below:

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

+1

Just run the simple command below:

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

The best method.

So simple, thanks!

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

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

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

@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

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: "<" !

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