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.