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

cristian100 commented Oct 28, 2016

this really saved my day, thanks

@lionaneesh

This comment has been minimized.

Copy link

lionaneesh commented Feb 10, 2017

Thanks so much! :)

@abdulbasitkay

This comment has been minimized.

Copy link

abdulbasitkay commented Feb 14, 2017

This is really helpful, Thanks a million

@Davor111

This comment has been minimized.

Copy link

Davor111 commented Feb 15, 2017

Awesome, thanks!

@nicksterx

This comment has been minimized.

Copy link

nicksterx 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

wubin1989 commented Mar 22, 2017

Thank you very much!

@alwinjoseph02

This comment has been minimized.

Copy link

alwinjoseph02 commented Apr 12, 2017

thanks a lot :)

@aavetis

This comment has been minimized.

Copy link

aavetis commented May 3, 2017

👍

@mdelorimier

This comment has been minimized.

Copy link

mdelorimier commented May 5, 2017

Thank you very much :)

@rolandsaven

This comment has been minimized.

Copy link

rolandsaven commented May 14, 2017

Thanks

@avijitdhar

This comment has been minimized.

Copy link

avijitdhar commented May 17, 2017

Thanks a lot :)

@wangchenshu

This comment has been minimized.

Copy link

wangchenshu commented May 22, 2017

Thanks a lot

@likhit-jansawang

This comment has been minimized.

Copy link

likhit-jansawang commented May 24, 2017

Thank you!

@giuseppelopes

This comment has been minimized.

Copy link

giuseppelopes commented May 30, 2017

Thanks a lot!

@jansanchez

This comment has been minimized.

Copy link

jansanchez commented May 30, 2017

Thanks!

@ilyaglow

This comment has been minimized.

Copy link

ilyaglow 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

ravindravv 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

jointdev commented Jul 26, 2017

Thanks!

@alex-ross

This comment has been minimized.

Copy link

alex-ross commented Aug 3, 2017

So easy. Thanks! <3

@MikeiLL

This comment has been minimized.

Copy link

MikeiLL 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

wangay commented Sep 16, 2017

tks from China.

useful to me~

@aurawindsurfing

This comment has been minimized.

Copy link

aurawindsurfing commented Sep 24, 2017

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

@huybui02

This comment has been minimized.

Copy link

huybui02 commented Oct 3, 2017

Thanks you.
It's very useful!

@IgorShkarin

This comment has been minimized.

Copy link

IgorShkarin commented Oct 4, 2017

where the dump is placing?

@g105b

This comment has been minimized.

Copy link

g105b 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

bibinamt commented Oct 27, 2017

thanks
it worked!!

@Pobx

This comment has been minimized.

Copy link

Pobx commented Nov 6, 2017

Thank you!

@Dantiff

This comment has been minimized.

Copy link

Dantiff commented Nov 6, 2017

Awome.. Thanks

@oramirez1

This comment has been minimized.

Copy link

oramirez1 commented Nov 7, 2017

👍

@danielsotopino

This comment has been minimized.

Copy link

danielsotopino commented Nov 15, 2017

Nice!

@mrosata

This comment has been minimized.

Copy link

mrosata commented Nov 22, 2017

Awesome, thanks!

@keviocastro

This comment has been minimized.

Copy link

keviocastro commented Dec 20, 2017

Thanks!

@thanhlq

This comment has been minimized.

Copy link

thanhlq commented Jan 3, 2018

Thanks very much!

@vainkop

This comment has been minimized.

Copy link

vainkop 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

p000 commented Feb 4, 2018

@spalladino Thanks allot Mr.

@Ertai

This comment has been minimized.

Copy link

Ertai commented Feb 7, 2018

sapeee

@vmpartner

This comment has been minimized.

Copy link

vmpartner 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

davidjeddy commented Mar 1, 2018

This saved me a LOT of time!

@vschoener

This comment has been minimized.

Copy link

vschoener 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

pierreallard commented Mar 29, 2018

Awesome ! Exactly what i was looking for :-)

@Nutura

This comment has been minimized.

Copy link

Nutura commented Apr 3, 2018

thanks <3

@PgrNascimento

This comment has been minimized.

Copy link

PgrNascimento commented Apr 4, 2018

thanks a lot

I love you

@stevus

This comment has been minimized.

Copy link

stevus 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

Vingtoft commented Apr 18, 2018

You just saved me time! Thanks!

@alexsandro-xpt

This comment has been minimized.

Copy link

alexsandro-xpt commented Apr 18, 2018

Super!!!

It's works pretty nice!!

@BirkhoffLee

This comment has been minimized.

Copy link

BirkhoffLee commented Apr 19, 2018

Thanks, this comes to be very useful :)

@teror4uks

This comment has been minimized.

Copy link

teror4uks 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

rafffael commented Apr 27, 2018

just what I needed, thanks a lot

@rafaelbeecker

This comment has been minimized.

Copy link

rafaelbeecker commented Apr 30, 2018

Thanks a lot man!

@r3lik

This comment has been minimized.

Copy link

r3lik commented May 11, 2018

thanks!

@mateuszrusiecki

This comment has been minimized.

Copy link

mateuszrusiecki 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

cassioKenji commented May 22, 2018

👍

@pablotrianda

This comment has been minimized.

Copy link

pablotrianda commented Jun 16, 2018

thanks a lot!!

@przemoli

This comment has been minimized.

Copy link

przemoli 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

Salaander commented Jun 27, 2018

Saved me too today, thanks! :) 👍

@k7eon

This comment has been minimized.

Copy link

k7eon commented Jun 27, 2018

Thanks a lot 👍

@wayanjimmy

This comment has been minimized.

Copy link

wayanjimmy commented Jul 13, 2018

Saved me too today, thanks!

@ssarioglu

This comment has been minimized.

Copy link

ssarioglu commented Jul 18, 2018

it does not works with an scheduled job :)

@taki

This comment has been minimized.

Copy link

taki commented Aug 2, 2018

Greate!!!

@smeagol91

This comment has been minimized.

Copy link

smeagol91 commented Aug 7, 2018

Thanks. 👍

@0m3gaC0d3

This comment has been minimized.

Copy link

0m3gaC0d3 commented Aug 15, 2018

Thank you!

@zachariahtimothy

This comment has been minimized.

Copy link

zachariahtimothy commented Aug 15, 2018

You rock, thanks a ton!

@dendrochronology

This comment has been minimized.

Copy link

dendrochronology 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

gvelizlasteniasoftware commented Aug 17, 2018

Gracias

@arevars

This comment has been minimized.

Copy link

arevars commented Aug 27, 2018

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

@ConConovaloff

This comment has been minimized.

Copy link

ConConovaloff 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

VboxNick commented Sep 3, 2018

Thanks!

@suraneti

This comment has been minimized.

Copy link

suraneti commented Sep 9, 2018

You shall pass, thanks!

@tesmojones

This comment has been minimized.

Copy link

tesmojones commented Sep 13, 2018

great

@kid304

This comment has been minimized.

Copy link

kid304 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

khachornchit commented Sep 17, 2018

thank so much :)

@BBavouzet

This comment has been minimized.

Copy link

BBavouzet 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

jefersonc commented Sep 28, 2018

Thanks ;)

@Robert-turbo

This comment has been minimized.

Copy link

Robert-turbo 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

mhardaniel 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

rossanoua commented Oct 30, 2018

Thankyou! Very helpfull :-)

@automationleg

This comment has been minimized.

Copy link

automationleg commented Oct 30, 2018

Super. Works well!

@shtrih

This comment has been minimized.

Copy link

shtrih 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

zumoshi 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

SalahAbdelhamid commented Dec 6, 2018

thanks for sharing, very useful

@kyberorg

This comment has been minimized.

Copy link

kyberorg commented Dec 19, 2018

Thanks!

@Stradivarius23

This comment has been minimized.

Copy link

Stradivarius23 commented Dec 27, 2018

Thank you a lot! Very useful.

@aghayari

This comment has been minimized.

Copy link

aghayari commented Dec 29, 2018

Awesome, thanks!

@TiE23

This comment has been minimized.

Copy link

TiE23 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

ignitz commented Jan 30, 2019

Thanks

@ignitz

This comment has been minimized.

Copy link

ignitz 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

saniaky 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

markusait commented Feb 22, 2019

awesome thank you

@EfrainLayCastillo

This comment has been minimized.

Copy link

EfrainLayCastillo commented Feb 28, 2019

god

@IslaMurtazaev

This comment has been minimized.

Copy link

IslaMurtazaev 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

arngani commented Mar 1, 2019

Thank you

@renakim

This comment has been minimized.

Copy link

renakim commented Mar 5, 2019

Thank you 👍

@lemartva

This comment has been minimized.

Copy link

lemartva commented Mar 15, 2019

Awesome Thanks!

@Rustymage

This comment has been minimized.

Copy link

Rustymage 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

vjohnk commented Apr 26, 2019

Thank-you

@guilhermeKodama

This comment has been minimized.

Copy link

guilhermeKodama commented May 1, 2019

Thanks man :)

@ccolunga

This comment has been minimized.

Copy link

ccolunga commented May 13, 2019

Excellent thank you man!!

@tamnguyentps

This comment has been minimized.

Copy link

tamnguyentps commented May 20, 2019

Thanks man, very helpful

@isneezy

This comment has been minimized.

Copy link

isneezy commented Jun 5, 2019

You saved my day! Tank you

@sealionking

This comment has been minimized.

Copy link

sealionking commented Jun 8, 2019

many thanks!

@ZoiaoDePeixe

This comment has been minimized.

Copy link

ZoiaoDePeixe commented Jun 12, 2019

Great!!! Thanks

@VikashChoudahry

This comment has been minimized.

Copy link

VikashChoudahry commented Jun 21, 2019

Thank you so much!!

@chem1st

This comment has been minimized.

Copy link

chem1st commented Jul 7, 2019

Many thanx!

@houbaron

This comment has been minimized.

Copy link

houbaron 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

psuzzi 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

v3nt 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
@iamsimakov

This comment has been minimized.

Copy link

iamsimakov commented Jul 30, 2019

for restore one table preparation step

sed -n -e '/-- Table structure for table `my_table`/,/UNLOCK TABLES/p' db_full_dump.sql > my_table.sql
@SET-SKYNET

This comment has been minimized.

Copy link

SET-SKYNET commented Jul 31, 2019

Thanks a lot! 👍

@sachinswagh-opcito

This comment has been minimized.

Copy link

sachinswagh-opcito commented Aug 14, 2019

Thanks. It worked.

@robert-t-taylor

This comment has been minimized.

Copy link

robert-t-taylor commented Aug 15, 2019

Thanks so much! 👍 👍 👍

@leandroo

This comment has been minimized.

Copy link

leandroo commented Aug 17, 2019

Using env variables set on docker-compose.yml

docker-compose exec -T <container> sh -c 'mysql -h$$DB_HOST -u$$DB_USER -p$$DB_PASS $$DB_NAME < bkp.sql'

@rgaleev

This comment has been minimized.

Copy link

rgaleev commented Aug 18, 2019

Thanks a lot!

@marcosdipaolo

This comment has been minimized.

Copy link

marcosdipaolo commented Aug 19, 2019

thanks a bunch

@kaka8shagy

This comment has been minimized.

Copy link

kaka8shagy commented Aug 19, 2019

Thanks a lot man!

@Gautam-Patel

This comment has been minimized.

Copy link

Gautam-Patel commented Sep 6, 2019

but how to run this using docker-compose ?

@margach

This comment has been minimized.

Copy link

margach commented Sep 16, 2019

Another thank you.

@boxiaozhi

This comment has been minimized.

Copy link

boxiaozhi commented Sep 20, 2019

About mysql "[Warning] Using a password on the command line interface can be insecure"

docker exec -i -e MYSQL_PWD=xxx containername mysqldump -uusername tablename > xxxx.sql
or
docker exec -i containername sh -c "export MYSQL_PWD=xxx && mysqldump -uusername tablename" > xxxx.sql

@williammellof5

This comment has been minimized.

Copy link

williammellof5 commented Sep 24, 2019

Woow Great! Thank you very much!

@ycjcl868

This comment has been minimized.

Copy link

ycjcl868 commented Oct 11, 2019

nice

@EnriqueTejeda

This comment has been minimized.

Copy link

EnriqueTejeda commented Oct 11, 2019

Thanks, very useful! 👍

@edisonex

This comment has been minimized.

Copy link

edisonex commented Oct 14, 2019

Thanks! It is exactly what I was looking for

@catbadger

This comment has been minimized.

Copy link

catbadger commented Oct 16, 2019

This saved my butt today. Thanks.

@phptek

This comment has been minimized.

Copy link

phptek commented Oct 31, 2019

In case you needed to hear it again - nice work. Works perfik.

@Mac-Rae

This comment has been minimized.

Copy link

Mac-Rae commented Nov 12, 2019

That method of import has made my day. Been looking for a command that works to finish off a script for weeks 🥇 😀

@bf4648

This comment has been minimized.

Copy link

bf4648 commented Nov 18, 2019

Awesome!

@domicio

This comment has been minimized.

Copy link

domicio commented Nov 19, 2019

Thanks!

@shashikapradeep

This comment has been minimized.

Copy link

shashikapradeep commented Nov 20, 2019

:O works perfectly...

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.