Ramin Hossaini (blog)

Simple MySQL backup-script

Being an Oracle database admin for the greater part of the day makes me a paranoid person and I'm usually shocked when I find out people don't have any backups of their blogs or their mySQL databases in particular.

The following shell-script:

• Creates an export of all tables in a mySQL database
• Compresses each generated SQL-export file
• Deletes any backups older than a week - mine are sufficiently small to do this

It's a small, straight-forward script that does the job and is exactly why I love UNIX/Linux.

Manually or from crontab:

mySQL-backup.sh DB-NAME-PARAM > mySQL-backup.log

mySQL-backup.sh

1
2
3
4
5
6
7
8
9
10

#!/bin/bash
SUFFIX=$(date +%y%m%d)
# Get parameter
DBNAME=$1
# Run mysqldump command
mysqldump --opt -uUSERNAME -pPASSWORD -h HOSTNAME $DBNAME > $DBNAME.$SUFFIX.sql
# Zip all sql files individually
find *.sql -exec gzip {} \;
# Delete *.sql.gz files older than a week
find *.sql.gz -mtime +7 -exec rm {} \;

Notes:

• I've only tested this in BASH
• I didn't use TAR to package and compress the files. Personally, I usually use tar when I want to retain certain important file-permissions and structures - didn't really feel it was needed here
• In this case, there would be a separate crontab entry for each database - you could choose to back-up all your databases on a server in one go