'How to restore MySQL dump from host to Docker container

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.

mysqldockercontainersrestoredump


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

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

Solution 5:[5]

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;

Solution 6:[6]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Handsome Nerd
Solution 2 Daniel Delgado
Solution 3 agusgambina
Solution 4 KARUPPASAMY MARIAPPAN
Solution 5 Mahadi Hasan
Solution 6 Andrew Smith

Related Questions