Skip to content

Instantly share code, notes, and snippets.

@spalladino
Created December 22, 2015 13:47
Show Gist options
  • Save spalladino/6d981f7b33f6e0afe6bb to your computer and use it in GitHub Desktop.
Save spalladino/6d981f7b33f6e0afe6bb to your computer and use it in GitHub Desktop.
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
@jrmarqueshd
Copy link

Awesome!

@HorselessName
Copy link

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

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

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

Very sweet. Thank you very much.

@echanobe
Copy link

Thank you!

@Tipset
Copy link

Tipset commented Aug 17, 2022

This helped a bunch, thank you!

@hkanizawa
Copy link

Thanks

@tomaszroot
Copy link

:-*

@botosdavid
Copy link

thank you so much!

@dorimusz
Copy link

Thanks a lot!

@ttcosta11
Copy link

Thank you!

@tgiovanella87
Copy link

Great! Thanks!

@paulovnas
Copy link

Very nice... Thank you so much.

@samplec0de
Copy link

Exactly what I looking for, thanks!

@nnearobot
Copy link

Exactly what I needed. Thank you so much!

@sukruaydinlik
Copy link

Thank you so much. Restore command worked like a charm. Unbelieveable to see that instructions on official Docker image page fail.

@razavistag
Copy link

this works thank you

@meng7171
Copy link

meng7171 commented Jan 8, 2023

good, bro. THANKS.

@paulograbin
Copy link

This is awesome, thank you!

@canuk99
Copy link

canuk99 commented Jan 26, 2023

Anybody figure out this socket issue?
docker exec zabbix-docker-62_zabbix-server_1 /usr/bin/mysqldump -u root --password=secret -S /var/run/mysqd/mysqld.socket zabbix > ~/zabbix.dump

mysqldump: Got error: 2002: "Can't connect to local server through socket '/var/run/mysqd/mysqld.socket' (2)" when trying to connect
"Can't connect to local server through socket '/var/run/mysqd/"Can't connect to local server through socket '/var/run/mysqd/mysqld.socket' (2)" when trying to connect

mysqld.socket' (2)" when trying to connect

mysql> show variables like 'socket';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| socket | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
1 row in set (0.01 sec)

mysql> quit
Bye

bash-4.4# ls -l /var/run/mysqld
total 12
-rw-r----- 1 mysql mysql 2 Jan 24 20:28 mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Jan 24 20:28 mysqld.sock
-rw------- 1 mysql mysql 2 Jan 24 20:28 mysqld.sock.lock
srwxrwxrwx 1 mysql mysql 0 Jan 24 20:28 mysqlx.sock

@cesarinobruno
Copy link

Thanks!

@lnfel
Copy link

lnfel commented Mar 9, 2023

Still works in 2023!

@maksam07
Copy link

Still works in 2023!

I have this page in my bookmarks. I hope it stays up forever and never gets deleted

@muzzammil194
Copy link

muzzammil194 commented Aug 21, 2023

#!/bin/bash

read -p "Select your option number:
(1) BACKUP DB From Container
(2) RESTORE DB From Container
(3) BACKUP DB local
(4) RESTORE DB local
Enter your option: " option

read -p "Enter your database Name: " DATABASE
read -p "Enter your Host: " HOST
read -p "Enter your Port: " PORT
read -p "Enter your Username: " USERNAME
read -p "Enter your Password: " PASSWORD

MY_SQL_DUMP=/usr/bin/mysqldump

if [ "$option" == "1" ]; then
read -p "Enter your Container Name or ID: " Container
docker exec "$Container" "$MY_SQL_DUMP" -u "$USERNAME" --password="$PASSWORD" "$DATABASE" > "$DATABASE.sql"
elif [ "$option" == "2" ]; then
read -p "Enter your Container Name or ID: " Container
read -p "Enter your Restore-File-Path: " PATH
cat "$PATH.sql" | docker exec -i "$Container" "$MY_SQL_DUMP" -u "$USERNAME" --password="$PASSWORD" "$DATABASE"
elif [ "$option" == "3" ]; then
sudo "$MY_SQL_DUMP" -u "$USERNAME" -p"$PASSWORD" -P "$PORT" --host="$HOST" "$DATABASE" > "$DATABASE.sql"
elif [ "$option" == "4" ]; then
read -p "Enter your Restore-File-Path: " PATH
sudo "$MY_SQL_DUMP" -u "$USERNAME" -P "$PORT" -p"$PASSWORD" "$DATABASE" < "$PATH.sql"
else
echo "Invalid option selected."
fi

@maksam07
Copy link

sudo docker

sudo docker?

@muzzammil194
Copy link

sudo docker

sudo docker?

if docker does not add in a group of Linux so it does not execute with full permission

@carlosjuanco
Copy link

Gracias por el script de respaldo @muzzammil194

@icanitam
Copy link

icanitam commented Oct 2, 2023

thanks a lot! still work

@saidbakr
Copy link

You should get Nobel prize for these two lines of code!

@wac-max
Copy link

wac-max commented Feb 9, 2024

Works perfectly. thank you!

@dangduytung
Copy link

It works, thanks.

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