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

Works like a charm!

thanks a bunch

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

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

Thanks a lot!

Thanks!

You are my hero of the day.

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`
--
...

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???

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

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

Thanks for saving my life

Thanks!

thanks

thanks 👍

Thanks

You are my hero, newbie here

Thanks!

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

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

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

@hhhien2903 thanks a lot!

thanks

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

Awesome!

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?

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

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.