Example script to backup databases and remove old by threshold

Introduction

There may be a requirement to backup databases more frequently than backups executed via WHM, or you may want to have a specific database backed up.

Procedure

A script can be created and executed via cron.

The example script would be

#!/bin/bash

BACKUPDIR="/backup/databases/"
DATE=`date +%s`

if [ ! -e $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
chmod 700 $BACKUPDIR
fi

for x in `mysqlshow | grep -v \_schema | awk -F "| " '{print $2}'`; do mysqldump $x | bzip2 -9czq > $BACKUPDIR$x-sql-$DATE.bz2; done

/usr/bin/find $BACKUPDIR ! -mtime -3|/usr/bin/xargs rm -f

chmod 600 $BACKUPDIR/*

This script will create /backup/databases/ if it doesn't exist, then backup all databases into that path, remove databases older than three days, and then change permissions to more restricted permissions on those SQL dumps.

If you wanted to just backup a single database, then simply modify the script to that database, for example, to backup the mysql MySQL database.

#!/bin/bash

BACKUPDIR="/backup/databases/"

DBNAME="mysql"
DATE=`date +%s`

if [ ! -e $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
chmod 700 $BACKUPDIR
fi

mysqldump $DBNAME | bzip2 -9czq > $BACKUPDIR$DBNAME-sql-$DATE.bz2

/usr/bin/find $BACKUPDIR ! -mtime -3|/usr/bin/xargs rm -f

chmod 600 $BACKUPDIR/*

Whichever one is needed would be placed into a file like /root/scripts/backupdb.sh, and then permissions are set to execute it.

chmod 750 /root/scripts/backupdb.sh 

And then, this can be added to roots crontab, so it is executed at the interval desired.

This is done as root running

crontab -e

And then adding this example

0 */8 * * * /root/scripts/backupdb.sh >/dev/null 2>&1

This will run every 8 hours and not send any output to cron.