Backing up your MySQL server on a cron schedule

Regular backups are always a good idea, and that’s why I wrote this quick shell script.

It’s sole purpose is to connect to your MySQL server, get a list of databases, then generate a mysql dump file for each one. All on a cron schedule, of course.

#!/bin/sh

# 2010/7/30
# Spencer Steffen
# Citrus Media Group
# http://citrusme.com/
# http://github.com/citrus/mysql-backups

SCRIPT_NAME="MySQL Backups"
VERSION="0.0.3"

BACKUPS=/path/to/your/backups

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

HOST="localhost"
USER="root"
PASS="password"

NOW=$(date +"%d%H")


if [ ! -d $BACKUPS/$NOW/ ]; then
  echo "Creating $BACKUPS/$NOW"
  mkdir -p $BACKUPS/$NOW/
fi


DBS="$($MYSQL -h$HOST -u$USER -p$PASS -Bse 'show databases')"

for db in $DBS; do
  
  if [ "$db" != "information_schema" ]; then
  
    time=$(date +"%H-%M-%S")
    dump="$db-$time.sql"
        
    $MYSQLDUMP -h$HOST -u$USER -p$PASS $db > $BACKUPS/$NOW/$dump
    
    echo "$BACKUPS/$NOW/$dump saved!"
    
  fi
  
done

You can add it to your crontab like this:

# m h  dom mon dow   command

@hourly sh /home/user/backup.sh >> /home/user/log/backup.log

It’s on github too if you’d like to fork or clone it.

git clone git://github.com/citrus/mysql-backups.git

Cheers!

rss feed

Tagged as: Software cron shell script MySQL

Viewed 235 times