Ramin Hossaini

Simple MySQL backup-script

Mar
23

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

One Response to Simple MySQL backup-script

  1. Pingback: Ramin Hossaini (blog) » Performing Wordpress upgrades (Linux)

Leave a Reply

Your email address will not be published. Required fields are marked *