Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Backup and restore a mysql database from a running Docker mysql container
# 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
@cristian100

This comment has been minimized.

Copy link

commented Oct 28, 2016

this really saved my day, thanks

@lionaneesh

This comment has been minimized.

Copy link

commented Feb 10, 2017

Thanks so much! :)

@abdulbasitkay

This comment has been minimized.

Copy link

commented Feb 14, 2017

This is really helpful, Thanks a million

@Davor111

This comment has been minimized.

Copy link

commented Feb 15, 2017

Awesome, thanks!

@nicksterx

This comment has been minimized.

Copy link

commented Mar 8, 2017

For Windows using Powershell with a large database you should use

# Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root -r DATABASE | Set-Content backup.sql

It will preserve encoding from the Linux container.

@wubin1989

This comment has been minimized.

Copy link

commented Mar 22, 2017

Thank you very much!

@alwinjoseph02

This comment has been minimized.

Copy link

commented Apr 12, 2017

thanks a lot :)

@aavetis

This comment has been minimized.

Copy link

commented May 3, 2017

👍

@mdelorimier

This comment has been minimized.

Copy link

commented May 5, 2017

Thank you very much :)

@rolandsaven

This comment has been minimized.

Copy link

commented May 14, 2017

Thanks

@avijitdhar

This comment has been minimized.

Copy link

commented May 17, 2017

Thanks a lot :)

@wangchenshu

This comment has been minimized.

Copy link

commented May 22, 2017

Thanks a lot

@likhit-jansawang

This comment has been minimized.

Copy link

commented May 24, 2017

Thank you!

@giuseppelopes

This comment has been minimized.

Copy link

commented May 30, 2017

Thanks a lot!

@jansanchez

This comment has been minimized.

Copy link

commented May 30, 2017

Thanks!

@ilyaglow

This comment has been minimized.

Copy link

commented Jun 5, 2017

Thanks! Official mysql image readme shows even more convenient way to do this if you use $MYSQL_ROOT_PASSWORD env variable:

docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql
@ravindravv

This comment has been minimized.

Copy link

commented Jun 27, 2017

Hi All, Hope all are doing great... I would like to know, how to restore MS SQL database from my host machine ( Docker engine running on this machine) to WINDOWS SQL container. Backup file exists in host machine C drive. Can anyone help on this? Please.... Thanks in Advance

@jointdev

This comment has been minimized.

Copy link

commented Jul 26, 2017

Thanks!

@alex-ross

This comment has been minimized.

Copy link

commented Aug 3, 2017

So easy. Thanks! <3

@MikeiLL

This comment has been minimized.

Copy link

commented Sep 4, 2017

You could also create a function in your .bash_profile:

dumpdb () {
   docker exec "${PWD##*/}_my-db_1" \
       mysqldump -uroot --password=password "$1" >backup.sql
}

dumpdb "my_database"
@wangay

This comment has been minimized.

Copy link

commented Sep 16, 2017

tks from China.

useful to me~

@aurawindsurfing

This comment has been minimized.

Copy link

commented Sep 24, 2017

Hehe keep on coming back to this gist! Thank you!

@huybui02

This comment has been minimized.

Copy link

commented Oct 3, 2017

Thanks you.
It's very useful!

@IgorShkarin

This comment has been minimized.

Copy link

commented Oct 4, 2017

where the dump is placing?

@g105b

This comment has been minimized.

Copy link

commented Oct 5, 2017

Commenting my thanks after landing here from Google what feels like 10,000 times. Thanks!

@bibinamt

This comment has been minimized.

Copy link

commented Oct 27, 2017

thanks
it worked!!

@Pobx

This comment has been minimized.

Copy link

commented Nov 6, 2017

Thank you!

@Dantiff

This comment has been minimized.

Copy link

commented Nov 6, 2017

Awome.. Thanks

@oramirez1

This comment has been minimized.

Copy link

commented Nov 7, 2017

👍

@danielsotopino

This comment has been minimized.

Copy link

commented Nov 15, 2017

Nice!

@mrosata

This comment has been minimized.

Copy link

commented Nov 22, 2017

Awesome, thanks!

@keviocastro

This comment has been minimized.

Copy link

commented Dec 20, 2017

Thanks!

@thanhlq

This comment has been minimized.

Copy link

commented Jan 3, 2018

Thanks very much!

@vainkop

This comment has been minimized.

Copy link

commented Jan 29, 2018

I use the following:
docker exec $(docker ps | awk '{print $NF}' | grep -w app1-db) mysqldump DATABASE > app1-db.$(date +"%Y%m%d_%H%M%S").sql
Where "app1-db" is a part of the name of the docker container with database (like "r-prod-app1-db-1-fdeb0d85")

@p000

This comment has been minimized.

Copy link

commented Feb 4, 2018

@spalladino Thanks allot Mr.

@Ertai

This comment has been minimized.

Copy link

commented Feb 7, 2018

sapeee

@vmpartner

This comment has been minimized.

Copy link

commented Feb 18, 2018

Please check my new solution. Special docker image that fast connect to any project for backup mysql database https://github.com/vmpartner/mysql-buckup

@davidjeddy

This comment has been minimized.

Copy link

commented Mar 1, 2018

This saved me a LOT of time!

@vschoener

This comment has been minimized.

Copy link

commented Mar 22, 2018

Awesome! Didn't think to make it this way with the pipe <3 👍 💯

@pierreallard

This comment has been minimized.

Copy link

commented Mar 29, 2018

Awesome ! Exactly what i was looking for :-)

@Nutura

This comment has been minimized.

Copy link

commented Apr 3, 2018

thanks <3

@PgrNascimento

This comment has been minimized.

Copy link

commented Apr 4, 2018

thanks a lot

I love you

@stevus

This comment has been minimized.

Copy link

commented Apr 12, 2018

Any solution here that doesn't require putting the password in the command? It will be available in the history

@Vingtoft

This comment has been minimized.

Copy link

commented Apr 18, 2018

You just saved me time! Thanks!

@alexsandro-xpt

This comment has been minimized.

Copy link

commented Apr 18, 2018

Super!!!

It's works pretty nice!!

@BirkhoffLee

This comment has been minimized.

Copy link

commented Apr 19, 2018

Thanks, this comes to be very useful :)

@teror4uks

This comment has been minimized.

Copy link

commented Apr 26, 2018

That my solution when container is running

# get IP 
docker inspect CONTAINER | grep IPAddress 

# for restore, db must be exist
mysql -uroot -ppass -h IPAddress DB_NAME -P 3306 < db_backup.sql

# for backup 
mysqldump -uroot -ppass IPAddress DB_NAME -P 3306 > db_backup.sql
@rafffael

This comment has been minimized.

Copy link

commented Apr 27, 2018

just what I needed, thanks a lot

@rafaelbeecker

This comment has been minimized.

Copy link

commented Apr 30, 2018

Thanks a lot man!

@r3lik

This comment has been minimized.

Copy link

commented May 11, 2018

thanks!

@mateuszrusiecki

This comment has been minimized.

Copy link

commented May 16, 2018

I get "mysqldump: [Warning] Using a password on the command line interface can be insecure." and no backup is created

@cassioKenji

This comment has been minimized.

Copy link

commented May 22, 2018

👍

@pablotrianda

This comment has been minimized.

Copy link

commented Jun 16, 2018

thanks a lot!!

@przemoli

This comment has been minimized.

Copy link

commented Jun 20, 2018

Alternative pipe syntax for restore action:

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

# Restore
docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE < backup.sql

Note that pipe "points" in opposite direction, and there is no longer a need for 'cat' as shell will use file as stdin for docker exec with above syntax.

@Salaander

This comment has been minimized.

Copy link

commented Jun 27, 2018

Saved me too today, thanks! :) 👍

@k7eon

This comment has been minimized.

Copy link

commented Jun 27, 2018

Thanks a lot 👍

@wayanjimmy

This comment has been minimized.

Copy link

commented Jul 13, 2018

Saved me too today, thanks!

@ssarioglu

This comment has been minimized.

Copy link

commented Jul 18, 2018

it does not works with an scheduled job :)

@taki

This comment has been minimized.

Copy link

commented Aug 2, 2018

Greate!!!

@smeagol91

This comment has been minimized.

Copy link

commented Aug 7, 2018

Thanks. 👍

@0m3gaC0d3

This comment has been minimized.

Copy link

commented Aug 15, 2018

Thank you!

@zachariahtimothy

This comment has been minimized.

Copy link

commented Aug 15, 2018

You rock, thanks a ton!

@dendrochronology

This comment has been minimized.

Copy link

commented Aug 17, 2018

To spin up a one-time container, and also avoid polluting the output with [Warning] Using a password on the command line interface can be insecure., I use this:

docker run -e "MYSQL_PWD=OMG_SO_SECURE" -a stdout -i -t --rm mysql:5.7 \
mysqldump --host=hostname.tld --user=i_am_groot --skip-lock-tables \
--extended-insert --quick --disable-keys database_name | pv > dump.sql

I like putting this in projects (via npm or whatever task runner is present), so I don't have to tweak Dockerfile or docker-compose.yml for something that I only occasionally need, like bootstrapping a local project with a remote data dump.

If you use the same image tag as your other container(s) (e.g., mysql:5.7), then Docker can leverage your local layer cache to speed up booting (under a second on my system).

pv is just there to keep me entertained when dumping gigabytes of data.

@gvelizlasteniasoftware

This comment has been minimized.

Copy link

commented Aug 17, 2018

Gracias

@arevars

This comment has been minimized.

Copy link

commented Aug 27, 2018

спасибо, чувак.!

@ConConovaloff

This comment has been minimized.

Copy link

commented Aug 31, 2018

docker-compose exec CONTAINER mysql -ppass db_name -e "SOURCE initial-dump.sql"

@VboxNick

This comment has been minimized.

Copy link

commented Sep 3, 2018

Thanks!

@suraneti

This comment has been minimized.

Copy link

commented Sep 9, 2018

You shall pass, thanks!

@tesmojones

This comment has been minimized.

Copy link

commented Sep 13, 2018

great

@kid304

This comment has been minimized.

Copy link

commented Sep 14, 2018

After running it shows: mysql: [Warning] Using a password on the command line interface can be insecure
The file back.sql was generated but the size = 0.

@Khachornchit

This comment has been minimized.

Copy link

commented Sep 17, 2018

thank so much :)

@BBavouzet

This comment has been minimized.

Copy link

commented Sep 25, 2018

Thank you !
I'm using this command line for restore : docker exec -i CONTAINER mysql -u root DATABASE < BACKUP.SQL

@jefersonc

This comment has been minimized.

Copy link

commented Sep 28, 2018

Thanks ;)

@Robert-turbo

This comment has been minimized.

Copy link

commented Oct 1, 2018

Does anyone know, how to run this command in cron?
I get an "the input device is not a TTY" error :(

@mhardaniel

This comment has been minimized.

Copy link

commented Oct 3, 2018

Does anyone know, how to run this command in cron?
I get an "the input device is not a TTY" error :(

use this code for cron job
it will run every day

0 0 * * * docker exec CONTAINER_NAME /usr/bin/mysqldump -u root --password=PASSWORD DATABASE_NAME > backup.sql_date +\%F.sql

@rossanoua

This comment has been minimized.

Copy link

commented Oct 30, 2018

Thankyou! Very helpfull :-)

@automationleg

This comment has been minimized.

Copy link

commented Oct 30, 2018

Super. Works well!

@shtrih

This comment has been minimized.

Copy link

commented Nov 14, 2018

Same, but with compression:

# Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE | gzip > `date +%Y-%m-%d-%T%z`-NAME.sql.gz`
# Restore
zcat 2018-11-14-backup.sql.gz | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE
@zumoshi

This comment has been minimized.

Copy link

commented Nov 25, 2018

using these commands all emojis in the database was broken for me. I needed to add the --default-character-set=utf8mb4 flag to them to get it to work.

# Backup
docker exec CONTAINER /usr/bin/mysqldump --default-character-set=utf8mb4 -u root --password=root DATABASE > backup.sql

# Restore
cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql --default-character-set=utf8mb4 -u root --password=root DATABASE
@SalahAbdelhamid

This comment has been minimized.

Copy link

commented Dec 6, 2018

thanks for sharing, very useful

@kyberorg

This comment has been minimized.

Copy link

commented Dec 19, 2018

Thanks!

@Stradivarius23

This comment has been minimized.

Copy link

commented Dec 27, 2018

Thank you a lot! Very useful.

@aghayari

This comment has been minimized.

Copy link

commented Dec 29, 2018

Awesome, thanks!

@TiE23

This comment has been minimized.

Copy link

commented Jan 10, 2019

Just in case anyone had an issue where it just didn't seem like the backup.sql file was being written anywhere here's what I used:

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

This worked for me the same exact way. Dunno why exec directly on mysqldump wasn't working... ¯\_(ツ)_/¯

Want to add: I personally used the extra argument --default-character-set=utf8mb4 thanks to @zumoshi a few comments above because I was worried about my emojis being screwed up.

By the way, if you have a tiny DB like me you might consider just using --all-databases in place of a specific DATABASE name. Just throwing that out there.

@ignitz

This comment has been minimized.

Copy link

commented Jan 30, 2019

Thanks

@ignitz

This comment has been minimized.

Copy link

commented Jan 30, 2019

Just in case anyone had an issue where it just didn't seem like the backup.sql file was being written anywhere here's what I used:

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

This worked for me the same exact way. Dunno why exec directly on mysqldump wasn't working... ¯_(ツ)_/¯

Want to add: I personally used the extra argument --default-character-set=utf8mb4 thanks to @zumoshi a few comments above because I was worried about my emojis being screwed up.

By the way, if you have a tiny DB like me you might consider just using --all-databases in place of a specific DATABASE name. Just throwing that out there.

Just to fix.

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

or will backup inside the container.

@saniaky

This comment has been minimized.

Copy link

commented Feb 22, 2019

Backup and compress using $MYSQL_ROOT_PASSWORD env variable


This solution backups all databases, compress output file using gzip and more over you don't need to pass root password for it:

# Backup
docker exec db sh -c \
    'mysqldump --all-databases --quick --single-transaction --skip-lock-tables --flush-privileges -uroot -p"$MYSQL_ROOT_PASSWORD"' \
    | gzip > ./backup.sql.gz

# Restore
gunzip -k ./backup.sql.gz
cat backup.sql | docker exec -i db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD"'

https://gist.github.com/saniaky/30985d144374b09bf5118dad52d721fa

@markusait

This comment has been minimized.

Copy link

commented Feb 22, 2019

awesome thank you

@EfrainLayCastillo

This comment has been minimized.

Copy link

commented Feb 28, 2019

god

@IslaMurtazaev

This comment has been minimized.

Copy link

commented Mar 1, 2019

Three years has past since this gist was published and it still helps people. Thanks)

@arngani

This comment has been minimized.

Copy link

commented Mar 1, 2019

Thank you

@renakim

This comment has been minimized.

Copy link

commented Mar 5, 2019

Thank you 👍

@lemartva

This comment has been minimized.

Copy link

commented Mar 15, 2019

Awesome Thanks!

@Rustymage

This comment has been minimized.

Copy link

commented Mar 30, 2019

For Windows using Powershell with a large database you should use

Backup

docker exec CONTAINER /usr/bin/mysqldump -u root --password=root -r DATABASE | Set-Content backup.sql

It will preserve encoding from the Linux container.

Just found your comment! Life saver!

@vjohnk

This comment has been minimized.

Copy link

commented Apr 26, 2019

Thank-you

@guilhermeKodama

This comment has been minimized.

Copy link

commented May 1, 2019

Thanks man :)

@ccolunga

This comment has been minimized.

Copy link

commented May 13, 2019

Excellent thank you man!!

@tamnguyentps

This comment has been minimized.

Copy link

commented May 20, 2019

Thanks man, very helpful

@isneezy

This comment has been minimized.

Copy link

commented Jun 5, 2019

You saved my day! Tank you

@sealionking

This comment has been minimized.

Copy link

commented Jun 8, 2019

many thanks!

@ZoiaoDePeixe

This comment has been minimized.

Copy link

commented Jun 12, 2019

Great!!! Thanks

@VikashChoudahry

This comment has been minimized.

Copy link

commented Jun 21, 2019

Thank you so much!!

@chem1st

This comment has been minimized.

Copy link

commented Jul 7, 2019

Many thanx!

@houbaron

This comment has been minimized.

Copy link

commented Jul 16, 2019

docker run -it --rm --network NETWORKNAME mysql:5.7 mysqldump -hHOSTNAME-uUSERNAME -pPASSWORD DATABASE > db.sql

@psuzzi

This comment has been minimized.

Copy link

commented Jul 18, 2019

Thanks, I was in a black hole for quite a few hours, and your code saved me!

@v3nt

This comment has been minimized.

Copy link

commented Jul 19, 2019

think i've been pulley's into a black hole now! On MacOS.

docker exec c6457df768cd /usr/bin/mysqldump -u root --password=ChangeMeIfYouWant wordpress-site_my-wpdb_1 > backup.sql

I can run mysqldump from normal terminal if that means anything?

backup.sql :
OCI runtime exec failed: exec failed: container_linux.go:344: starting container process caused "exec: \"/usr/bin/mysqldump\": stat /usr/bin/mysqldump: no such file or directory": unknown

docker-compose:

version: "2"
services:
  my-wpdb:
    image: mariadb
    ports:
      - "8081:3306"
    environment:
      MYSQL_ROOT_PASSWORD: ChangeMeIfYouWant
  my-wp:
    image: wordpress
    volumes:
      - ./:/var/www/html
    ports:
      - "8080:80"
    links:
      - my-wpdb:mysql
    environment:
      WORDPRESS_DB_PASSWORD: ChangeMeIfYouWant
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.