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

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?

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

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

Very sweet. Thank you very much.

Thank you!

This helped a bunch, thank you!

Thanks

:-*

thank you so much!

Thanks a lot!

Thank you!

Great! Thanks!

Very nice... Thank you so much.

Exactly what I looking for, thanks!

Exactly what I needed. Thank you so much!

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

this works thank you

good, bro. THANKS.

This is awesome, thank you!

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

Thanks!

Still works in 2023!

Still works in 2023!

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

#!/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

sudo docker

sudo docker?

sudo docker

sudo docker?

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

Gracias por el script de respaldo @muzzammil194

thanks a lot! still work