This is a simple script I created to backup multiple small databases. I used it in a small Linode box I have with ubuntu, but it should work in any linux server.

Replacements:

  • XBACKUPFOLDERX: your folder without trailing and ending slashes relative to the root.
  • XUSERX: A database user with access to all your databases.
  • XPASSWORDX: The XUSERX password .
  • database1 database2 database3 ... databaseN: Your database names separated by spaces.
  • 5: The number of backups to keep per database.

What the script does:

  1. It keeps at most 5 backups for each database
  2. Backups are gzipped
  3. Keeps only one backup for today, and 4 from previous days


Steps to implement


Step 1.

Copy the script into a .sh file in your system. We assume cron_backupdb.sh for example.

#!/bin/bash

for BDNAME in database1 database2 database3 ... databaseN
do
  rm /XBACKUPFOLDERX/${BDNAME}_`date +%Y-%m-%d`*
  mysqldump -uXUSERX -pXPASSWORDX $BDNAME|gzip > /root/_bkp/${BDNAME}_`date +%Y-%m-%d_%H`.sql.gz
  BKTAILRM=$((`ls -t /root/_bkp/${BDNAME}_*.sql*|awk -F: '{print $1}'|wc -l` - 5))
  if [ "$BKTAILRM" -gt "0" ]; then
    ls -t /XBACKUPFOLDERX/${BDNAME}_*.sql*|awk -F: '{print "rm "$1}'|tail -n $BKTAILRM|/bin/sh
  fi
done


Step 2.

Permissions and crontab

:~# chmod u+x cron_backupdb.sh
:~# crontab -e



Step 3.

Your crontab line. This one runs the script every hour. But check the manual for your own setup:

0 * * * * /var/root/cron_backupdb.sh