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

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