<p>based on @ulrich answer, but with <strong>mysqldump</strong> :</p>
<pre class="lang-bash prettyprint-override"><code>DATE_TIME=`date +&quot;%Y%m%d_%H%M&quot;
CONTAINER_NAME=&quot;mysql_server&quot;
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUP_FILE=${DATE_TIME}_${CONTAINER_NAME}.backup.sql.gz
CONTAINER_BACKUP_DIR=&quot;/var/lib/mysql/&quot;
HOST_DATA_DIR=&quot;/path/on/my/VM/correspond/to/docker/volume/&quot;
HOST_BACKUP_DIR=&quot;/other/path/on/VM/&quot;
## run backup in container
docker exec ${CONTAINER} sh -c &quot;/usr/bin/mysqldump -h localhost -u root -p${PASSWORD} --all-databases | gzip -9 -c &gt; ${CONTAINER_BACKUP_DIR}${BACKUP_FILE}&quot;
## move to final folder 
mv ${HOST_DATA_DIR}${BACKUP_FILE} ${HOST_BACKUP_DIR}
</code></pre>
<p>HOST_DATA_DIR and HOST_BACKUP_DIR are differents because I have an other daemon to save backup to a data center, and because in HOST_DATA_DIR, root can only read and execute (r_x).<br />
For all folder paths, be careful to have a final '/', otherwise you should be surprised!</p>
<p>and if you run it automatically with a CRON job, it should be helpfull to manage rotate to delete old backup:</p>
<pre class="lang-bash prettyprint-override"><code>## rotate save management to keep only last MAX_BACKUP files
MAX_BACKUP=3
${HOST_DATA_DIR}rotate-databases-backup.log
echo ${HOST_BACKUP_DIR}${BACKUP_FILE} &gt;&gt; ${HOST_DATA_DIR}rotate-databases-backup.log
BACKUPED_NB=$(ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz | wc --lines)
TOTAL=$(wc --lines &lt; ${HOST_DATA_DIR}rotate-databases-backup.log)
FILES_NB_TO_DELETE=$(expr $TOTAL - $MAX_BACKUP)
if [[ $FILES_NB_TO_DELETE -gt 0 ]]; then
    head --lines=$FILES_NB_TO_DELETE ${HOST_DATA_DIR}rotate-databases-backup.log | xargs rm
    ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz &gt; ${HOST_DATA_DIR}rotate-databases-backup.log
fi
</code></pre>

CC BY-SA 4.0
2020-11-24T16:52:40.900Z
2020-11-24T17:41:04.183Z
2020-11-24T17:41:04.183Z
bcag2
<p>I have a little script for my MariaDb database inside a docker container.
The setup is comparable. I have a database container with a mounted volume. Furthermore I have a second volume for backups mounted under <code>/backup</code>.</p>
<p>The script is run on the host via cron and uses the database instance inside the container, so only one database accesses the data. That is the docker exec part.
I'm sure, you can adopt it to mysql. You only have to configure the CONTAINER_NAME and change the command inside the container.</p>
<pre class="lang-bash prettyprint-override"><code>#!/bin/bash
DATE_TIME=`date +&quot;%Y_%m_%d_%H_%M&quot;`
CONTAINER_NAME=&quot;mariadb&quot;
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUPFILE=${DATE_TIME}_${CONTAINER_NAME}.backup.gz

echo &quot;Backup of MariaDb&quot;
echo &quot;&quot;
echo &quot;Old backps will be deleted.&quot;
echo &quot;Datenbank Container Name: $CONTAINER_NAME&quot;
echo &quot;Datenbank Container:  $CONTAINER&quot;

echo &quot;Writing backup to ${BACKUPFILE}&quot;
echo &quot;&quot;
docker exec ${CONTAINER} sh -c &quot;find /backup/ -mindepth 1 -mtime +8 -delete; /usr/bin/mariabackup --backup --user root --password=${PASSWORD} --stream=xbstream | gzip &gt; /backup/${BACKUPFILE}&quot;
echo &quot;&quot;
echo &quot;Backup finished&quot;
</code></pre>

CC BY-SA 4.0
2020-11-24T08:54:57.180Z
In my case, in the docker container, it is MySQL, not MariaDB, so I will use *mysqldump*, Thanks
bcag2
CC BY-SA 4.0
2020-11-23T16:38:11.900Z
2020-11-23T16:38:11.900Z
seism0saurus
<p>I use a VPS (my host) where a <em>LAMP server</em> was installed.
I already have a database associated to host <em>mariaDB server Ver 15.1 Distrib 10.1.47</em> and <em>automysqlbackup</em> backups it. This part works well.</p>
<p>For a &quot;new project&quot;, we use now docker servers and one with a <em>mysql 5.7</em> and a volume to save this new project database in a host folder, for exemple: <em>/path/on/host/dockerise/db/</em></p>
<p>I want to backup too this new database.</p>
<p>What is the right way?</p>
<ol>
<li>Is there a way with automysqlbackup? I think no because it uses host mariaDb server and <em>mysqldump</em>, and you shouldn't have two servers working on the same database (<a href="https://dev.mysql.com/doc/refman/5.7/en/multiple-data-directories.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/multiple-data-directories.html</a>)</li>
<li>Directly with <em>mysqldump</em> ?</li>
<li>Or just create a bash script and CRON it?</li>
</ol>

<docker><mysql><volume>
backup dockerise mysql database from host volume
bcag2
# backup dockerise mysql database from host volume

(Asked by: username_0 on 2020-11-23)

I use a VPS (my host) where a *LAMP server* was installed.
I already have a database associated to host *mariaDB server Ver 15.1 Distrib 10.1.47* and *automysqlbackup* backups it. This part works well.

For a "new project", we use now docker servers and one with a *mysql 5.7* and a volume to save this new project database in a host folder, for exemple: */path/on/host/dockerise/db/*

I want to backup too this new database.

What is the right way?

1. Is there a way with automysqlbackup? I think no because it uses host mariaDb server and *mysqldump*, and you shouldn't have two servers working on the same database (<https://dev.mysql.com/doc/refman/5.7/en/multiple-data-directories.html>)
2. Directly with *mysqldump* ?
3. Or just create a bash script and CRON it?

**Tags:** docker, mysql, volume


---

## 2 answers

### Answer by username_0 on 2020-11-24. Score: 1 | Accepted answer

based on @ulrich answer, but with mysqldump :
DATE_TIME=`date +"%Y%m%d_%H%M"
CONTAINER_NAME="mysql_server"
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUP_FILE=${DATE_TIME}_${CONTAINER_NAME}.backup.sql.gz
CONTAINER_BACKUP_DIR="/var/lib/mysql/"
HOST_DATA_DIR="/path/on/my/VM/correspond/to/docker/volume/"
HOST_BACKUP_DIR="/other/path/on/VM/"
## run backup in container
docker exec ${CONTAINER} sh -c "/usr/bin/mysqldump -h localhost -u root -p${PASSWORD} --all-databases | gzip -9 -c > ${CONTAINER_BACKUP_DIR}${BACKUP_FILE}"
## move to final folder 
mv ${HOST_DATA_DIR}${BACKUP_FILE} ${HOST_BACKUP_DIR}

HOST_DATA_DIR and HOST_BACKUP_DIR are differents because I have an other daemon to save backup to a data center, and because in HOST_DATA_DIR, root can only read and execute (r_x).
For all folder paths, be careful to have a final '/', otherwise you should be surprised!
and if you run it automatically with a CRON job, it should be helpfull to manage rotate to delete old backup:
## rotate save management to keep only last MAX_BACKUP files
MAX_BACKUP=3
${HOST_DATA_DIR}rotate-databases-backup.log
echo ${HOST_BACKUP_DIR}${BACKUP_FILE} >> ${HOST_DATA_DIR}rotate-databases-backup.log
BACKUPED_NB=$(ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz | wc --lines)
TOTAL=$(wc --lines < ${HOST_DATA_DIR}rotate-databases-backup.log)
FILES_NB_TO_DELETE=$(expr $TOTAL - $MAX_BACKUP)
if [[ $FILES_NB_TO_DELETE -gt 0 ]]; then
    head --lines=$FILES_NB_TO_DELETE ${HOST_DATA_DIR}rotate-databases-backup.log | xargs rm
    ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz > ${HOST_DATA_DIR}rotate-databases-backup.log
fi


---

### Answer by username_1 on 2020-11-23. Score: 1

I have a little script for my MariaDb database inside a docker container.
The setup is comparable. I have a database container with a mounted volume. Furthermore I have a second volume for backups mounted under /backup.
The script is run on the host via cron and uses the database instance inside the container, so only one database accesses the data. That is the docker exec part.
I'm sure, you can adopt it to mysql. You only have to configure the CONTAINER_NAME and change the command inside the container.
#!/bin/bash
DATE_TIME=`date +"%Y_%m_%d_%H_%M"`
CONTAINER_NAME="mariadb"
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUPFILE=${DATE_TIME}_${CONTAINER_NAME}.backup.gz

echo "Backup of MariaDb"
echo ""
echo "Old backps will be deleted."
echo "Datenbank Container Name: $CONTAINER_NAME"
echo "Datenbank Container:  $CONTAINER"

echo "Writing backup to ${BACKUPFILE}"
echo ""
docker exec ${CONTAINER} sh -c "find /backup/ -mindepth 1 -mtime +8 -delete; /usr/bin/mariabackup --backup --user root --password=${PASSWORD} --stream=xbstream | gzip > /backup/${BACKUPFILE}"
echo ""
echo "Backup finished"

> **Comments:**
> **username_0**: In my case, in the docker container, it is MySQL, not MariaDB, so I will use *mysqldump*, Thanks  

---

