Answers: <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>
  
Answers: CC BY-SA 4.0
Answers: 2020-11-24T16:52:40.900Z
Answers: 2020-11-24T17:41:04.183Z
Answers: 2020-11-24T17:41:04.183Z
Answers: bcag2
Answers: <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>
  
Answers: CC BY-SA 4.0
Answers: 2020-11-24T08:54:57.180Z
Answers: In my case, in the docker container, it is MySQL, not MariaDB, so I will use *mysqldump*, Thanks
Answers: bcag2
Answers: CC BY-SA 4.0
Answers: 2020-11-23T16:38:11.900Z
Answers: 2020-11-23T16:38:11.900Z
Answers: seism0saurus
Body: <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>
  
Tags: <docker><mysql><volume>
Title: backup dockerise mysql database from host volume
UserDisplayName: bcag2
ThreadText: # 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** :
  
  ```bash
  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:
  
  ```bash
  ## 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.
  
  ```bash
  #!/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  
  
  ---
  
  