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
@ramsesdelr
Copy link

ramsesdelr commented Oct 17, 2021

Works like a charm!

@mkdizajn
Copy link

mkdizajn commented Oct 18, 2021

thanks a bunch

@mkdizajn
Copy link

mkdizajn commented Oct 18, 2021

how I like this kind of simple cli solutions - win over UI apps / mouse based ones every day :)

@Prajapatisantu
Copy link

Prajapatisantu commented Nov 12, 2021

after 1 day this result got and finally solved thanks a lot!

@espring
Copy link

espring commented Dec 1, 2021

Thanks a lot!

@rodrigopuls
Copy link

rodrigopuls commented Dec 2, 2021

Thanks!

@mattiasorgato
Copy link

mattiasorgato commented Jan 25, 2022

You are my hero of the day.

@Frodox
Copy link

Frodox commented Jan 31, 2022

it doesn't work in desired way for mysql:5.7, for example, becasuse there are mysqldump's warnings and errors inside dump =( :

$ head db-2022-01-31-17-33-02.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 5.7.36, for Linux (x86_64)
--
-- Host: localhost    Database: db_test
-- ------------------------------------------------------
-- Server version	5.7.36
...
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

--
-- Table structure for table `b_admin_notify`
--
...

@EnnyMautin
Copy link

EnnyMautin commented Feb 9, 2022

Hello guys, can the database be shared in real time, not dumping, i mean share on GitHub while other team pull it down on the other end???

@jigarakatidus
Copy link

jigarakatidus commented Feb 16, 2022

@EnnyMautin better your team connect's to common db server from their local. Just a suggestion.

@overcomzi
Copy link

overcomzi commented Feb 22, 2022

@EnnyMautin I am also interested in this question. Is it possible or not?

@blurname
Copy link

blurname commented Mar 12, 2022

Thanks for saving my life

@jmv74211
Copy link

jmv74211 commented Mar 29, 2022

Thanks!

@ondiekisteven
Copy link

ondiekisteven commented Apr 8, 2022

thanks

@breaker92
Copy link

breaker92 commented Apr 21, 2022

thanks 👍

@DaviCecilio
Copy link

DaviCecilio commented May 3, 2022

Thanks

@cdanielzt
Copy link

cdanielzt commented May 19, 2022

You are my hero, newbie here

@Necromant1k
Copy link

Necromant1k commented May 25, 2022

Thanks!

@otterDeveloper
Copy link

otterDeveloper commented Jun 14, 2022

Excuse my ignorance, but why the -i flag in the restore but not in the backup??

@hhhien2903
Copy link

hhhien2903 commented Jun 16, 2022

Excuse my ignorance, but why the -i flag in the restore but not in the backup??

https://gist.github.com/v0lkan/c413cf9477b607db1ea1117c9de853df

@otterDeveloper
Copy link

otterDeveloper commented Jun 16, 2022

@hhhien2903 thanks a lot!

@lnasc256
Copy link

lnasc256 commented Jun 18, 2022

thanks

@Harshit2377
Copy link

Harshit2377 commented Jun 19, 2022

i am using this command for restore but the restore in not getting .
plzz help

@jrmarqueshd
Copy link

jrmarqueshd commented Jun 24, 2022

Awesome!

@HorselessName
Copy link

HorselessName commented Jun 29, 2022

Hey... What about a crontab for this?
Lets say: backupZabbix.sh

I do have a backup script that works well with crontab using Zabbix outside docker - For some reason if i use docker exec mysqldump it doesnt work... :(

Can u give us a tip?

@nhossaincse
Copy link

nhossaincse commented Jul 17, 2022

Can someone please tell me, how I get the database dump out of the docker container on my host? The command

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

seems to dump the file inside the docker container, but I need it on my host. Thanks!

I think, you may use this command to get the files in host from container:
docker cp :/file/path/within/container /host/path/target

@ambrosiora
Copy link

ambrosiora commented Jul 19, 2022

Can someone please tell me, how I get the database dump out of the docker container on my host? The command
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql
seems to dump the file inside the docker container, but I need it on my host. Thanks!

I think, you may use this command to get the files in host from container: docker cp :/file/path/within/container /host/path/target

have you tried to map a volume to your host?
For example, if you dump the .sql file INSIDE the container on /home/dump (or whatever folder you want)
just map this folder as a volume on you host.

I can give you an example with docker-compose.yml. With pure docker I don't know how to map a volume (never needed since I always worked with docker-compose hahaha)

version: '3'
services:
  mydb:
    container_name: mydb
    image: mariadb:latest
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: mydb
      MYSQL_USER: user
      MYSQL_PASSWORD: 123
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - /home/host_folder_dump:/home/container_folder_dump

Since you take a mysql dump, automatically the .sql file will appear in your host folder.
Tell me what you think?

@blu-IT
Copy link

blu-IT commented Jul 19, 2022

Can someone please tell me, how I get the database dump out of the docker container on my host? The command
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql
seems to dump the file inside the docker container, but I need it on my host. Thanks!

I think, you may use this command to get the files in host from container: docker cp :/file/path/within/container /host/path/target

@nhossaincse

Thanks! I already solved its (inside my script) this way:

docker cp <container_id>:/file/path/within/container /host/path/target

@blu-IT
Copy link

blu-IT commented Jul 19, 2022

Since you take a mysql dump, automatically the .sql file will appear in your host folder. Tell me what you think?

@ambrosiora

Thanks! I already solved its (inside my script) this way:

docker cp <container_id>:/file/path/within/container /host/path/target

@behai-nguyen
Copy link

behai-nguyen commented Aug 7, 2022

Very sweet. Thank you very much.

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