Database Backup and Restore

Option 1 - Docker

Full Backup, Including Configuration Files

The following procedure make a backup of everything. Disadvantage: Requires to shutdown the containers for the backup.

Backup:

In a Docker installation run docker-compose down, then create a tar file of the complete installation directory. This way, a backup of the database and all configuration files is performed. When done run docker-compose up -d to restart the service.

Restore:

Un-tar the backup and use it in Docker

Backup/Restore the Database (Only) to SQL text file

The following commands can be used backup/restore of the current state of the database to a text file. This can be done without shutting down the containers. IMPORTANT: Only the database is saved, not the configuration files in the volume directories!

# Replace the following variables in the commands:
#
# BACKUP-FILE.SQL: The file to/from which the SQL database is to be dumped/restored
#
# DOCKER-CONTAINTER-NAME: do a 'docker ps' to find the name of the database container
#
# DATBASE-NAME: See docker-compose.yml --> MYSQL_DATABASE

# Backup
docker exec DOCKER-CONTAINTER-NAME \
  sh -c 'exec mysqldump -uroot -p"$MYSQL_ROOT_PASSWORD" 'DATABASE-NAME \
  > BACKUP-FILE.SQL

# Restore
cat BACKUP-FILE.sql | docker exec -i DOCKER-CONTAINTER-NAME \
  sh -c '/usr/bin/mysql -u root --password="$MYSQL_ROOT_PASSWORD" DATABASE-NAME'

Option 2: Traditional Installation

Backup

Apart from the configuration information stored in the "config" directory, all user data is stored in the SQL database. A database backup can be performed with the mysqldump command. The following script might be helpful to do this in an automated way:

#!/bin/bash

# replace 'drdb_example' with the SQL database name used by your project

NOW=$(date +"%Y-%m-%d-%H%M")
DB_FILE="drdb_example.$NOW.sql"

mysqldump -u root drdb_example > /home/xxxxxxxxx/$DB_FILE

Note: The SQL dump is in clear text. Have a look with a text editor if the backup contains the data that you would expect to be inside!

Restore

Restoring the database to a previous state can be done by overwriting the current database with a backup file. The backup file contains SQL commands to drop existing tables and recreate them from the backup file. It is NOT necessary to delete the tables manually.

mysql -u root -p drdb_example <./backupfile.sql