Backup and restore a mysql database from a running Docker mysql container

mysql-docker.sh

# 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

this really saved my day, thanks

Thanks so much! :)

This is really helpful, Thanks a million

Awesome, thanks!

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.

Thank you very much!

thanks a lot :)

👍

Thank you very much :)

Thanks

Thanks a lot :)

Thanks a lot

Thank you!

Thanks a lot!

Thanks!

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

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

Thanks!

So easy. Thanks! <3

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"

tks from China.

useful to me~

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

Thanks you.
It's very useful!

where the dump is placing?

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

thanks
it worked!!

Thank you!

Awome.. Thanks

👍

Nice!

Awesome, thanks!

Thanks!

Thanks very much!

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")

@spalladino Thanks allot Mr.

sapeee

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

This saved me a LOT of time!

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

Awesome ! Exactly what i was looking for :-)

thanks <3

thanks a lot

I love you

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

You just saved me time! Thanks!

Super!!!

It's works pretty nice!!

Thanks, this comes to be very useful :)

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

just what I needed, thanks a lot

Thanks a lot man!

thanks!

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

👍

thanks a lot!!

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.

Saved me too today, thanks! :) 👍

Thanks a lot 👍

Saved me too today, thanks!

it does not works with an scheduled job :)

Greate!!!

Thanks. 👍

Thank you!

You rock, thanks a ton!

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.

Gracias

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

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

Thanks!

You shall pass, thanks!

great

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.

thank so much :)

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

Thanks ;)

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

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

Thankyou! Very helpfull :-)

Super. Works well!

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

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

thanks for sharing, very useful

Thanks!

Thank you a lot! Very useful.

Awesome, thanks!

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.

Thanks

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.

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

awesome thank you

god

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

Thank you

Thank you 👍

Awesome Thanks!

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!

Thank-you

Thanks man :)

Excellent thank you man!!

Thanks man, very helpful

You saved my day! Tank you

many thanks！

Great!!! Thanks

Thank you so much!!

Many thanx!

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

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

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

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

Thanks a lot! 👍

Thanks. It worked.

Thanks so much! 👍 👍 👍

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'

Thanks a lot!

thanks a bunch

Thanks a lot man!

but how to run this using docker-compose ?

Another thank you.

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

Woow Great! Thank you very much!

nice

Thanks, very useful! 👍

Thanks! It is exactly what I was looking for

This saved my butt today. Thanks.

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

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

Awesome!

Thanks!

:O works perfectly...

Thanks a lot, you made my day

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

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.

Thank you!

Thank You!

Thanks.. This helped!!

Thanks a lot bro :D

works fine, thanks!

Thank you!

YEEEEESSS! Thank you!

Thanks a lot +1

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

OMG So simple but so helpful!! Thank you!

thanks thats really helpful

Excellent info, thanks everyone!

Thanks.

Thanks, really helpful!

Works well,
Thank you!

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.

So simple, so amazing! Thanks a lot!

Thanks!

Thanks a lot!

Thanks!

works, thanks a lot!

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

Thank you!!

Thank you!

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.

Thank you very much!

thanks!

Thanks @vineetkumar03!

thanks!

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

Thanks!, really helpful

Thanks! Bro. it's really helped me

This is great!

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/

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?

Great!!!!

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

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

this saved me many hours. thanks!