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

Thanks!!

Nice one! Thanks

Thanks!

You're Awesome spalladino! Thanks!

@spalladino thanks for the snippet.
In our projects, we use procedures and triggers. So following commands works for me.
For Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root --routines --triggers DATABASE > backup.sql

Thanks bro!

Thanks, man!

I use this command to backup all my mysql containters in oneline

for docker in $(docker ps |grep mysql|awk '{print$1}'); do docker exec $docker sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"'> /root/dumpsmysql/"$(date '+%d%m%Y')"-$docker; done

I have a different apprach. I want to have a progress bar to see how my database is being uploaded.

I use a MySQL image with pv installed.

FROM mysql:8.0

RUN apt-get update 
RUN apt-get install -y pv

COPY my.cnf /etc/mysql/conf.d/my.cnf

I share a backup volume(/app/backup/) with a database container. then I have a sh script that looks like this:

# load .env
export $(cat .env | sed 's/#.*//g' | xargs)

restore_database() {
  container_id=$(docker ps -f name=$MYSQL_HOST -q | tail -n1)
  docker exec -it $container_id sh -c 'pv /app/backup/backup.sql | /usr/bin/mysql -u $MYSQL_ROOT_USER --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE'
}

# anable running different fuctions from console
$@

backup.sql I swapped for the backup I want to restore.
The result*:

* I use makefile as a wrapper and sh scripts as my fuctions

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!

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!