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 cristian100 commented Oct 28, 2016

this really saved my day, thanks

@lionaneesh

This comment has been minimized.

Copy link

@lionaneesh lionaneesh commented Feb 10, 2017

Thanks so much! :)

@abdulbasitkay

This comment has been minimized.

Copy link

@abdulbasitkay abdulbasitkay commented Feb 14, 2017

This is really helpful, Thanks a million

@Davor111

This comment has been minimized.

Copy link

@Davor111 Davor111 commented Feb 15, 2017

Awesome, thanks!

@nicksterx

This comment has been minimized.

Copy link

@nicksterx 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 wubin1989 commented Mar 22, 2017

Thank you very much!

@alwinjoseph02

This comment has been minimized.

Copy link

@alwinjoseph02 alwinjoseph02 commented Apr 12, 2017

thanks a lot :)

@aavetis

This comment has been minimized.

Copy link

@aavetis aavetis commented May 3, 2017

๐Ÿ‘

@mdelorimier

This comment has been minimized.

Copy link

@mdelorimier mdelorimier commented May 5, 2017

Thank you very much :)

@rolandsaven

This comment has been minimized.

Copy link

@rolandsaven rolandsaven commented May 14, 2017

Thanks

@avijitdhar

This comment has been minimized.

Copy link

@avijitdhar avijitdhar commented May 17, 2017

Thanks a lot :)

@wangchenshu

This comment has been minimized.

Copy link

@wangchenshu wangchenshu commented May 22, 2017

Thanks a lot

@likhit-jansawang

This comment has been minimized.

Copy link

@likhit-jansawang likhit-jansawang commented May 24, 2017

Thank you!

@giuseppelopes

This comment has been minimized.

Copy link

@giuseppelopes giuseppelopes commented May 30, 2017

Thanks a lot!

@jansanchez

This comment has been minimized.

Copy link

@jansanchez jansanchez commented May 30, 2017

Thanks!

@ilyaglow

This comment has been minimized.

Copy link

@ilyaglow 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 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 jointdev commented Jul 26, 2017

Thanks!

@alex-ross

This comment has been minimized.

Copy link

@alex-ross alex-ross commented Aug 3, 2017

So easy. Thanks! <3

@MikeiLL

This comment has been minimized.

Copy link

@MikeiLL 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 wangay commented Sep 16, 2017

tks from China.

useful to me~

@aurawindsurfing

This comment has been minimized.

Copy link

@aurawindsurfing aurawindsurfing commented Sep 24, 2017

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

@huybui02

This comment has been minimized.

Copy link

@huybui02 huybui02 commented Oct 3, 2017

Thanks you.
It's very useful!

@IgorShkarin

This comment has been minimized.

Copy link

@IgorShkarin IgorShkarin commented Oct 4, 2017

where the dump is placing?

@g105b

This comment has been minimized.

Copy link

@g105b 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 bibinamt commented Oct 27, 2017

thanks
it worked!!

@Pobx

This comment has been minimized.

Copy link

@Pobx Pobx commented Nov 6, 2017

Thank you!

@Dantiff

This comment has been minimized.

Copy link

@Dantiff Dantiff commented Nov 6, 2017

Awome.. Thanks

@oramirez1

This comment has been minimized.

Copy link

@oramirez1 oramirez1 commented Nov 7, 2017

๐Ÿ‘

@danielsotopino

This comment has been minimized.

Copy link

@danielsotopino danielsotopino commented Nov 15, 2017

Nice!

@mrosata

This comment has been minimized.

Copy link

@mrosata mrosata commented Nov 22, 2017

Awesome, thanks!

@keviocastro

This comment has been minimized.

Copy link

@keviocastro keviocastro commented Dec 20, 2017

Thanks!

@thanhlq

This comment has been minimized.

Copy link

@thanhlq thanhlq commented Jan 3, 2018

Thanks very much!

@vainkop

This comment has been minimized.

Copy link

@vainkop 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 p000 commented Feb 4, 2018

@spalladino Thanks allot Mr.

@Ertai

This comment has been minimized.

Copy link

@Ertai Ertai commented Feb 7, 2018

sapeee

@vmpartner

This comment has been minimized.

Copy link

@vmpartner 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 davidjeddy commented Mar 1, 2018

This saved me a LOT of time!

@vschoener

This comment has been minimized.

Copy link

@vschoener 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 pierreallard commented Mar 29, 2018

Awesome ! Exactly what i was looking for :-)

@Nutura

This comment has been minimized.

Copy link

@Nutura Nutura commented Apr 3, 2018

thanks <3

@PgrNascimento

This comment has been minimized.

Copy link

@PgrNascimento PgrNascimento commented Apr 4, 2018

thanks a lot

I love you

@stevus

This comment has been minimized.

Copy link

@stevus 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 Vingtoft commented Apr 18, 2018

You just saved me time! Thanks!

@alexsandro-xpt

This comment has been minimized.

Copy link

@alexsandro-xpt alexsandro-xpt commented Apr 18, 2018

Super!!!

It's works pretty nice!!

@BirkhoffLee

This comment has been minimized.

Copy link

@BirkhoffLee BirkhoffLee commented Apr 19, 2018

Thanks, this comes to be very useful :)

@teror4uks

This comment has been minimized.

Copy link

@teror4uks 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 rafffael commented Apr 27, 2018

just what I needed, thanks a lot

@rafaelbeecker

This comment has been minimized.

Copy link

@rafaelbeecker rafaelbeecker commented Apr 30, 2018

Thanks a lot man!

@r3lik

This comment has been minimized.

Copy link

@r3lik r3lik commented May 11, 2018

thanks!

@mateuszrusiecki

This comment has been minimized.

Copy link

@mateuszrusiecki 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 cassioKenji commented May 22, 2018

๐Ÿ‘

@pablotrianda

This comment has been minimized.

Copy link

@pablotrianda pablotrianda commented Jun 16, 2018

thanks a lot!!

@przemoli

This comment has been minimized.

Copy link

@przemoli 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 Salaander commented Jun 27, 2018

Saved me too today, thanks! :) ๐Ÿ‘

@k7eon

This comment has been minimized.

Copy link

@k7eon k7eon commented Jun 27, 2018

Thanks a lot ๐Ÿ‘

@wayanjimmy

This comment has been minimized.

Copy link

@wayanjimmy wayanjimmy commented Jul 13, 2018

Saved me too today, thanks!

@ssarioglu

This comment has been minimized.

Copy link

@ssarioglu ssarioglu commented Jul 18, 2018

it does not works with an scheduled job :)

@taki

This comment has been minimized.

Copy link

@taki taki commented Aug 2, 2018

Greate!!!

@smeagol91

This comment has been minimized.

Copy link

@smeagol91 smeagol91 commented Aug 7, 2018

Thanks. ๐Ÿ‘

@0m3gaC0d3

This comment has been minimized.

Copy link

@0m3gaC0d3 0m3gaC0d3 commented Aug 15, 2018

Thank you!

@zachariahtimothy

This comment has been minimized.

Copy link

@zachariahtimothy zachariahtimothy commented Aug 15, 2018

You rock, thanks a ton!

@dendrochronology

This comment has been minimized.

Copy link

@dendrochronology 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 gvelizlasteniasoftware commented Aug 17, 2018

Gracias

@arevars

This comment has been minimized.

Copy link

@arevars arevars commented Aug 27, 2018

ัะฟะฐัะธะฑะพ, ั‡ัƒะฒะฐะบ.!

@ConConovaloff

This comment has been minimized.

Copy link

@ConConovaloff 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 VboxNick commented Sep 3, 2018

Thanks!

@suraneti

This comment has been minimized.

Copy link

@suraneti suraneti commented Sep 9, 2018

You shall pass, thanks!

@tesmojones

This comment has been minimized.

Copy link

@tesmojones tesmojones commented Sep 13, 2018

great

@kid304

This comment has been minimized.

Copy link

@kid304 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 khachornchit commented Sep 17, 2018

thank so much :)

@BBavouzet

This comment has been minimized.

Copy link

@BBavouzet 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 jefersonc commented Sep 28, 2018

Thanks ;)

@Robert-turbo

This comment has been minimized.

Copy link

@Robert-turbo 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 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 rossanoua commented Oct 30, 2018

Thankyou! Very helpfull :-)

@automationleg

This comment has been minimized.

Copy link

@automationleg automationleg commented Oct 30, 2018

Super. Works well!

@shtrih

This comment has been minimized.

Copy link

@shtrih 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 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 SalahAbdelhamid commented Dec 6, 2018

thanks for sharing, very useful

@kyberorg

This comment has been minimized.

Copy link

@kyberorg kyberorg commented Dec 19, 2018

Thanks!

@Stradivarius23

This comment has been minimized.

Copy link

@Stradivarius23 Stradivarius23 commented Dec 27, 2018

Thank you a lot! Very useful.

@aghayari

This comment has been minimized.

Copy link

@aghayari aghayari commented Dec 29, 2018

Awesome, thanks!

@TiE23

This comment has been minimized.

Copy link

@TiE23 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 ignitz commented Jan 30, 2019

Thanks

@ignitz

This comment has been minimized.

Copy link

@ignitz 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 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 markusait commented Feb 22, 2019

awesome thank you

@EfrainLayCastillo

This comment has been minimized.

Copy link

@EfrainLayCastillo EfrainLayCastillo commented Feb 28, 2019

god

@IslaMurtazaev

This comment has been minimized.

Copy link

@IslaMurtazaev IslaMurtazaev commented Mar 1, 2019

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

@rasheednagoorgani-gep

This comment has been minimized.

Copy link

@rasheednagoorgani-gep rasheednagoorgani-gep commented Mar 1, 2019

Thank you

@renakim

This comment has been minimized.

Copy link

@renakim renakim commented Mar 5, 2019

Thank you ๐Ÿ‘

@lemartva

This comment has been minimized.

Copy link

@lemartva lemartva commented Mar 15, 2019

Awesome Thanks!

@Rustymage

This comment has been minimized.

Copy link

@Rustymage 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 vjohnk commented Apr 26, 2019

Thank-you

@guilhermeKodama

This comment has been minimized.

Copy link

@guilhermeKodama guilhermeKodama commented May 1, 2019

Thanks man :)

@ccolunga

This comment has been minimized.

Copy link

@ccolunga ccolunga commented May 13, 2019

Excellent thank you man!!

@tamnguyentps

This comment has been minimized.

Copy link

@tamnguyentps tamnguyentps commented May 20, 2019

Thanks man, very helpful

@isneezy

This comment has been minimized.

Copy link

@isneezy isneezy commented Jun 5, 2019

You saved my day! Tank you

@sealionking

This comment has been minimized.

Copy link

@sealionking sealionking commented Jun 8, 2019

many thanks๏ผ

@ZoiaoDePeixe

This comment has been minimized.

Copy link

@ZoiaoDePeixe ZoiaoDePeixe commented Jun 12, 2019

Great!!! Thanks

@VikashChoudahry

This comment has been minimized.

Copy link

@VikashChoudahry VikashChoudahry commented Jun 21, 2019

Thank you so much!!

@chem1st

This comment has been minimized.

Copy link

@chem1st chem1st commented Jul 7, 2019

Many thanx!

@houbaron

This comment has been minimized.

Copy link

@houbaron 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 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 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 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 SET-SKYNET commented Jul 31, 2019

Thanks a lot! ๐Ÿ‘

@sachinswagh-opcito

This comment has been minimized.

Copy link

@sachinswagh-opcito sachinswagh-opcito commented Aug 14, 2019

Thanks. It worked.

@robert-t-taylor

This comment has been minimized.

Copy link

@robert-t-taylor robert-t-taylor commented Aug 15, 2019

Thanks so much! ๐Ÿ‘ ๐Ÿ‘ ๐Ÿ‘

@leandroo

This comment has been minimized.

Copy link

@leandroo 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 rgaleev commented Aug 18, 2019

Thanks a lot!

@marcosdipaolo

This comment has been minimized.

Copy link

@marcosdipaolo marcosdipaolo commented Aug 19, 2019

thanks a bunch

@kaka8shagy

This comment has been minimized.

Copy link

@kaka8shagy kaka8shagy commented Aug 19, 2019

Thanks a lot man!

@gautampatel5317

This comment has been minimized.

Copy link

@gautampatel5317 gautampatel5317 commented Sep 6, 2019

but how to run this using docker-compose ?

@margach

This comment has been minimized.

Copy link

@margach margach commented Sep 16, 2019

Another thank you.

@ConteMan

This comment has been minimized.

Copy link

@ConteMan ConteMan 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 williammellof5 commented Sep 24, 2019

Woow Great! Thank you very much!

@ycjcl868

This comment has been minimized.

Copy link

@ycjcl868 ycjcl868 commented Oct 11, 2019

nice

@EnriqueTejeda

This comment has been minimized.

Copy link

@EnriqueTejeda EnriqueTejeda commented Oct 11, 2019

Thanks, very useful! ๐Ÿ‘

@edisonex

This comment has been minimized.

Copy link

@edisonex edisonex commented Oct 14, 2019

Thanks! It is exactly what I was looking for

@catbadger

This comment has been minimized.

Copy link

@catbadger catbadger commented Oct 16, 2019

This saved my butt today. Thanks.

@phptek

This comment has been minimized.

Copy link

@phptek 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 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 bf4648 commented Nov 18, 2019

Awesome!

@domicio

This comment has been minimized.

Copy link

@domicio domicio commented Nov 19, 2019

Thanks!

@shashikapradeep

This comment has been minimized.

Copy link

@shashikapradeep shashikapradeep commented Nov 20, 2019

:O works perfectly...

@eshaparenko

This comment has been minimized.

Copy link

@eshaparenko eshaparenko commented Nov 27, 2019

Thanks a lot, you made my day

@blakefrederick

This comment has been minimized.

Copy link

@blakefrederick blakefrederick commented Dec 7, 2019

100th thank you. This results always shows up when I google this question.

@maxhellwig

This comment has been minimized.

Copy link

@maxhellwig maxhellwig commented Dec 17, 2019

If you are using this command via docker-compose you have to detach the tty via -T switch like this:
docker-compose exec -T database sh -c "mysqldump etc."
Otherwise the output
mysqldump: [Warning] Using a password on the command line interface can be insecure.
also gets added to your dump file and the import fails.

@Lanatel

This comment has been minimized.

Copy link

@Lanatel Lanatel commented Jan 14, 2020

Thank you!

@bigsony

This comment has been minimized.

Copy link

@bigsony bigsony commented Jan 17, 2020

Thank You!

@mkraibt

This comment has been minimized.

Copy link

@mkraibt mkraibt commented Jan 31, 2020

Thanks.. This helped!!

@skyblue197

This comment has been minimized.

Copy link

@skyblue197 skyblue197 commented Feb 4, 2020

Thanks a lot bro :D

@adamrosloniec

This comment has been minimized.

Copy link

@adamrosloniec adamrosloniec commented Feb 5, 2020

works fine, thanks!

@inomasadji

This comment has been minimized.

Copy link

@inomasadji inomasadji commented Feb 10, 2020

Thank you!

@declankeyesbevan

This comment has been minimized.

Copy link

@declankeyesbevan declankeyesbevan commented Feb 12, 2020

YEEEEESSS! Thank you!

@shahzadpydev

This comment has been minimized.

Copy link

@shahzadpydev shahzadpydev commented Feb 12, 2020

Thanks a lot +1

@AlirezaInGitHub

This comment has been minimized.

Copy link

@AlirezaInGitHub AlirezaInGitHub commented Feb 20, 2020

If you have Unicode characters in your db, to avoid problem with encoding use this:

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

@mikedixson

This comment has been minimized.

Copy link

@mikedixson mikedixson commented Feb 27, 2020

OMG So simple but so helpful!! Thank you!

@asolopovas

This comment has been minimized.

Copy link

@asolopovas asolopovas commented Mar 10, 2020

thanks thats really helpful

@niallobr

This comment has been minimized.

Copy link

@niallobr niallobr commented Mar 11, 2020

Excellent info, thanks everyone!

@itzurabhi

This comment has been minimized.

Copy link

@itzurabhi itzurabhi commented Apr 16, 2020

Thanks.

@jbheuschen

This comment has been minimized.

Copy link

@jbheuschen jbheuschen commented Apr 26, 2020

Thanks, really helpful!

@sarfraz-cd

This comment has been minimized.

Copy link

@sarfraz-cd sarfraz-cd commented Apr 28, 2020

Works well,
Thank you!

@colm-anseo

This comment has been minimized.

Copy link

@colm-anseo colm-anseo commented May 2, 2020

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

This requires a local mysql client to work.

The original gist version uses the docker image's native tools, so avoids any such local requirements.

@PaulFrambot

This comment has been minimized.

Copy link

@PaulFrambot PaulFrambot commented May 16, 2020

So simple, so amazing! Thanks a lot!

@lindseybradford

This comment has been minimized.

Copy link

@lindseybradford lindseybradford commented Jun 4, 2020

Thanks!

@abraaoneves

This comment has been minimized.

Copy link

@abraaoneves abraaoneves commented Jun 5, 2020

Thanks a lot!

@5SMNOONMS5

This comment has been minimized.

Copy link

@5SMNOONMS5 5SMNOONMS5 commented Jun 17, 2020

Thanks!

@devjord

This comment has been minimized.

Copy link

@devjord devjord commented Jun 26, 2020

works, thanks a lot!

@vineetkumar03

This comment has been minimized.

Copy link

@vineetkumar03 vineetkumar03 commented Jul 4, 2020

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

@ambrosiora

This comment has been minimized.

Copy link

@ambrosiora ambrosiora commented Jul 18, 2020

Thank you!!

@jandresleiva

This comment has been minimized.

Copy link

@jandresleiva jandresleiva commented Aug 5, 2020

Thank you!

@Rozkalns

This comment has been minimized.

Copy link

@Rozkalns Rozkalns commented Aug 21, 2020

I found this article a bit helpful - https://medium.com/@ricardolsmendes/mysql-mariadb-with-scheduled-backup-jobs-running-in-docker-1956e9892e78
and the repo even more helpful - https://github.com/ricardolsmendes/docker-samples
This is to automatically make backups using container.

Sharing this, because most of syntax examples of backup are almost the same.

@wellisonraul

This comment has been minimized.

Copy link

@wellisonraul wellisonraul commented Aug 29, 2020

Thank you very much!

@pushp1997

This comment has been minimized.

Copy link

@pushp1997 pushp1997 commented Sep 16, 2020

thanks!

@joshxyzhimself

This comment has been minimized.

Copy link

@joshxyzhimself joshxyzhimself commented Sep 25, 2020

Thanks @vineetkumar03!

@oneNevan

This comment has been minimized.

Copy link

@oneNevan oneNevan commented Oct 6, 2020

thanks!

@LabN36

This comment has been minimized.

Copy link

@LabN36 LabN36 commented Oct 8, 2020

on a side note those who are getting ERROR 1064 (42000) error
Solution is: check your .sql file and remove the first line from it ie. line with this sentence
"mysqldump: [Warning] Using a password on the command line interface can be insecure."

stackoverflow reference
Cheers,
https://tutehub.dev

@eduardomarcos

This comment has been minimized.

Copy link

@eduardomarcos eduardomarcos commented Oct 14, 2020

Thanks!, really helpful

@grootsadmin

This comment has been minimized.

Copy link

@grootsadmin grootsadmin commented Oct 20, 2020

Thanks! Bro. it's really helped me

@smhmh

This comment has been minimized.

Copy link

@smhmh smhmh commented Nov 12, 2020

This is great!

@ivictbor

This comment has been minimized.

Copy link

@ivictbor ivictbor commented Nov 15, 2020

We are using this open-source container which autoamatically uploads to cheap jucy ๐Ÿ˜‹ aws glacier service https://hinty.io/vprotasenia/how-to-backup-sql-database-simple-ready-to-use-script/

@kaitcoa

This comment has been minimized.

Copy link

@kaitcoa kaitcoa commented Dec 10, 2020

Hey guys, all of those commands work just fine when inserting up to 3-5Gig database.
But it constantly breaks down when I have to import a 15GB database.

I even copy my DB into MySQL container,
I log in to shell, use the database;

SET autocommit=0; SOURCE big_database_15GB.sql; COMMIT;

And suddenly my container times out and it's broken.
I increased max_allowed_packet, I even increased Docker memory but no luck. Any idea when inserting a really big DB?

@abiliocoelho

This comment has been minimized.

Copy link

@abiliocoelho abiliocoelho commented Jan 19, 2021

Great!!!!

@folarinmartins

This comment has been minimized.

Copy link

@folarinmartins folarinmartins commented Jan 28, 2021

Thanks man. God bless you and @vineetkumar03 @colm-anseo

@grootsadmin

This comment has been minimized.

Copy link

@grootsadmin grootsadmin commented Jan 28, 2021

Hey guys, all of those commands work just fine when inserting up to 3-5Gig database.
But it constantly breaks down when I have to import a 15GB database.

I even copy my DB into MySQL container,
I log in to shell, use the database;

SET autocommit=0; SOURCE big_database_15GB.sql; COMMIT;

And suddenly my container times out and it's broken.
I increased max_allowed_packet, I even increased Docker memory but no luck. Any idea when inserting a really big DB?

Refer these steps
docker exec -it my_mysql bash -c "echo 'max_allowed_packet = 1024M' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

Restart container
docker restart my_mysql

@ezralazuardy

This comment has been minimized.

Copy link

@ezralazuardy ezralazuardy commented Feb 10, 2021

this saved me many hours. thanks!

@AsmitNepali

This comment has been minimized.

Copy link

@AsmitNepali AsmitNepali commented Apr 15, 2021

Thank You ......... Man ๐Ÿ™

@andreycas

This comment has been minimized.

Copy link

@andreycas andreycas commented Apr 24, 2021

Hello, I have a problem, when I run I get the following error trying to restore a db

docker exec -i 4e06fb81a408 /usr/bin/mysql -u root --password=EV7es snipe < mysql-snipeit.sql

Error
OCI runtime exec failed: exec failed: container_linux.go:367: starting container process caused: exec: "/usr/bin/mysql": permission denied: unknown

I don't know what's going on, has anyone had this problem, I'm trying to restore in a docker container, thanks

@svendster

This comment has been minimized.

Copy link

@svendster svendster commented May 10, 2021

Thanks!

@kaitcoa

This comment has been minimized.

Copy link

@kaitcoa kaitcoa commented May 11, 2021

Hello there,

I managed long time ago to insert my 15gb db, and much bigger later.
The method I used is
docker cp big_file.sql mysql:/
and later in shell

SET autocommit=0; SET unique_checks=0; SET foreign_key_check=0; SOURCE big_file.sql; COMMIT; SET unique_checks=1; SET foregin_key_checks=1;

Hope it helps somebody in nasty situation :)

@thoaiky1992

This comment has been minimized.

Copy link

@thoaiky1992 thoaiky1992 commented May 19, 2021

thanks so much

@AlexSmi

This comment has been minimized.

Copy link

@AlexSmi AlexSmi commented May 27, 2021

thanks so much

@IkhyeonJo

This comment has been minimized.

Copy link

@IkhyeonJo IkhyeonJo commented Jun 2, 2021

Thanks!!

@hkanizawa

This comment has been minimized.

Copy link

@hkanizawa hkanizawa commented Jun 3, 2021

Nice one! Thanks

@dvadvaa

This comment has been minimized.

Copy link

@dvadvaa dvadvaa commented Jun 6, 2021

Thanks!

@m33k

This comment has been minimized.

Copy link

@m33k m33k commented Jun 11, 2021

You're Awesome spalladino! Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment