How to restore MySQL dump from host to Docker container

Question

I'm sure this is a duplicated topic, but I simply cannot get it done: I like to restore my database dump to MySQL container in run time, without modifying the docker-compose.yml file.

Dockerfile

FROM php:5.4.45-apache
RUN apt-get update
RUN docker-php-ext-install mysql mysqli

docker-compose.yml

version: '2'
services:
  php_service:
    container_name: my_php
    # Use Dockerfile in this dir to build the image
    build: .
    # Stop containers always after exiting.
    restart: always
    ports:
      # 'localhost' does not works from the host, but the IP of docker itself
      # (192.168.99.100 for example - shown on the top of the console)
      - "80:80"
      - "443:443"
    environment:
      # Pass variables
      - API_TOKEN=xxxx
    volumes:
      # The current directory will be mounted to '/var/www/html'
      # WORKS ONLY IN USER'S DIR ON WINDOWS (~/Downloads for example)
      - .:/var/www/html
  # See https://hub.docker.com/_/mysql/ for additional information.
  # To open up console, run `docker exec -it my_mysql bash`.
  # To restore a dump `docker exec -i my_mysql /usr/bin/mysql -u root
  # --password=test_pass DATABASE < DUMP.sql` should work, but it never did.
  mysql_service:
    container_name: my_mysql
    # Use an existing image
    image: mysql:5.6
    restart: always
    ports:
      # Let it accessible for other apps (mysql on host, IDE, etc.)
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass' # TODO: Change this
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes:
      # Named volumes (my-datavolume) has to be listed in the "volumes"
      # section - I don't know how it works or what is it doing at all...
      # (-_-')
      - my-datavolume:/var/lib/mysql
volumes:
  my-datavolume:

Steps to reproduce:

Start Docker Toolbox on Windows 7 host
docker-compose up
Open a new Docker Toolbox terminal
docker exec my_msql /usr/bin/mysql -u root --password=test_pass -e 'CREATE DATABASE testdb;'
docker exec -i my_mysql /usr/bin/mysql -u root --password=test_pass testdb < dump_on_host.sql
docker exec -it my_mysql /usr/bin/mysql -u root --password=test_pass testdb
mysql> SHOW TABLES;

The database is empty. It seems that it does nothing, because the terminal responds too quickly. I tried out the dump by installing MySQL on my host, it can be restored.

Do you get proper output for something like `docker exec -i my_mysql wc < dump_on_host.sql` ? — Thilo, Oct 05 '17 at 06:48
Thanks for the suggestion - it says the input is empty (`0 0 0`). — bimlas, Oct 05 '17 at 07:05
Assuming the file is really there, maybe the pipe did not work? Try `cat dump_on_host.sql | docker exec ...` — Thilo, Oct 05 '17 at 07:06
`cat dump_on_host.sql` in Docker Toolbox (aka: host) shows the whole file, so it's not empty. — bimlas, Oct 05 '17 at 07:08
Are you running this on the Docker host directly, or is there some VM setup in between (like you'd need for Mac OS or Windows)? — Thilo, Oct 05 '17 at 07:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155992/discussion-between-bimlas-and-thilo). — bimlas, Oct 05 '17 at 07:09
@Thilo, StackOverflow warned me to continue in chat, please come there. — bimlas, Oct 05 '17 at 07:17
`docker exec -i database_container mysql -u root -p database --password=you_password < test.sql` — Tarek, Oct 26 '17 at 19:07

score 84 · Answer 1 · edited Feb 26 '23 at 18:56

84

Try below command works fine for me.

Run it from docker host machine.

Backup

docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql

Restore

cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE

Please let me know in case any issue.

edited Feb 26 '23 at 18:56

Dimitrios Desyllas

9,082
15
74
164

answered Oct 05 '17 at 07:10

Rohan J Mohite

2,283
10
19

See the comments for the opening message. – bimlas Oct 05 '17 at 07:16
8

On restore, better to save password in a file Something like `cat bkp.sql | docker exec -i CONTAINER mysql -uroot -p\`cat /root/mariadb-root\`` – Fernando Fabreti Jun 14 '19 at 20:21
2

For larger data sets keeping compressed is helpful too. I use. `zcat < backup.sql.gz | docker exec -i CONTAINER /usr/bin/mysql -u root --password='root' DATABASE` – SmileIT Feb 13 '21 at 19:22
I get the following message: `mysql: [Warning] Using a password on the command line interface can be insecure.` – Pathros Mar 17 '21 at 02:46
Can also use `docker compose exec -T` – Julien Jun 15 '22 at 06:53

score 25 · Answer 2 · answered Mar 02 '21 at 08:40

25

The documentation on dockerhub worked for me: https://hub.docker.com/_/mysql

Backup

docker exec some-mysql sh -c 'exec mysqldump --all-databases -u<user> -p<password> <database>' > /some/path/on/your/host/all-databases.sql

Restore

docker exec -i some-mysql sh -c 'exec mysql -u<user> -p<password> <database>' < /some/path/on/your/host/all-databases.sql

answered Mar 02 '21 at 08:40

Daniel Delgado

4,813
5
40
48

1

For me also helped this way: docker exec -i mysql -u -p < /some/path/on/your/host/all-databases.sql. – Oleg Dmitrochenko Oct 22 '21 at 08:53

score 4 · Answer 3 · answered Oct 26 '17 at 18:11

You are using a volume, that means that after you restore the dump, data will persist.

You are also exposing the database in port 3306, so a solution might be to connect to the database through a client. You can connect to mysql with a graphical client like MySql Workbench and restore the database from there.

Or if you have installed mysql in your command line

$ mysql --host=127.0.0.1 --port=3306 -u test -p testdb < dump_on_host.sql

score 3 · Answer 4 · answered Mar 20 '22 at 20:33

Per the latest instructions at https://hub.docker.com/_/mysql you can do this:

docker-compose exec -T mysql_service sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" testdb' < dump_on_host.sql

This has the nice benefit of not putting the password into your command history.

score 1 · Answer 5 · edited Aug 22 '23 at 13:24

1

ONE DOCKER DB TO ANOTHER DOCKER DB

Backup

docker exec containerid mysqldump -u root --password=root portal-db > lower-portal-db.sql

Restore

cat lower-portal-db.sql | docker exec -i containerid mysql -u root --password=root portal-db

edited Aug 22 '23 at 13:24

Tiago Gouvêa

15,036
4
75
81

answered Dec 01 '21 at 19:49

KARUPPASAMY MARIAPPAN

31
5

score 0 · Answer 6 · edited Sep 07 '22 at 15:03

0

Try the following command

sudo docker exec -i CONTAINER_ID sh -c 'exec mysql -u root -p[YourPassword] database_name' < /your/database/directory/database_name.sql;

edited Sep 07 '22 at 15:03

RiggsFolly

93,638
21
103
149

answered Oct 19 '21 at 10:21

Mahadi Hasan

129
1
8

How to restore MySQL dump from host to Docker container

6 Answers6

Linked