Skip to main content

Example script to backup databases and remove old by threshold

Eric Delorme
  • Updated

Introduction

There may be a requirement to backup databases more frequently than backups that are 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 3 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 set so that it can be executed.

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 in this example

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

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

 

 

 

 

Related articles