Skip to main content

Backup DB

Backup

  1. 5x per dag wordt een dump van de database op de server gemaakt. -> cron job
  2. Oude back-up worden verwijderd, de 23:10 back up wordt 400 dagen bewaard,
    de andere worden eerder verwijderd.
  3. Elk dag wordt via rsync en ssh (via public/privat keys) de hele document root plus alle database dumps gekopieerd. Script wordt geïnitieerd door NAS.
  4. Een controle script - nog niet geïmplementeerd.

NAS

Cron job draait om 11:10, 14:10, 17:10, 20:10 en 23:10. Er worden dus 5 DB dumps per dag gemaakt.

# create backup of Bookstack Database
10 23,20,17,14,11 * * * /home/max/mysql/backupDB.sh > /home/max/mysql/backupDB.log 2>&1

Backup Script plus clean up

Script backupDB.sh voor backup

#!/bin/bash

export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`
TODAY=`date +"%m%d-%H00"`

################################################################
################## Update below values  ########################

DB_BACKUP_PATH='/home/max/mysql/Backup-DB'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='user'
MYSQL_PASSWORD='secret'
DATABASE_NAME='sb_name'
BACKUP_RETAIN_DAYS=180   ## Number of days to keep local backup copy

#################################################################

echo "Backup started for database - ${DATABASE_NAME}"

mkdir -p ${DB_BACKUP_PATH}

mysqldump -h ${MYSQL_HOST} \
   -P ${MYSQL_PORT} \
   -u ${MYSQL_USER} \
   -p${MYSQL_PASSWORD} \
   ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${DATABASE_NAME}-${TODAY}.sql.gz

if [ $? -eq 0 ]; then
  # remove all files older than 400 days
  find ${DB_BACKUP_PATH} -type f -not -name '*.sql.gz' -mtime +400 -exec rm {} \;
  # remove all files older than 90 days excpet those made on the 1ste of the month
  find ${DB_BACKUP_PATH} -type f -not -name '*01-????.sql.gz' -mtime +90 -exec rm {} \;
  # remove all files not created ar 23:00 and older than 10 days
  find ${DB_BACKUP_PATH} -type f -not -name '*300.sql.gz' -mtime +10 -exec rm {} \;
  echo "Database backup successfully completed"
else
  echo "Error found during backup"
  exit 1
fi


##### Remove backups older than {BACKUP_RETAIN_DAYS} days  #####
find ${DB_BACKUP_PATH}/* -mtime +${BACKUP_RETAIN_DAYS} -exec rm {} \;

NAS Script

(let op dat niet-standaard poorten worden gebruikt ivm security)

TARGET=/volume1/archives/1/

mkdir -p $TARGET/server/var/www
mkdir -p $TARGET/server/home/abc

nohup rsync -av -e 'ssh -p 9999' servername.domain.net:/var/www/* $TARGET/server/var/www &
nohup rsync -av -e 'ssh -p 9999' servername.domain.net:/home/abc/ $TARGET/server/home/abc &

 

Back to top