2 2

Mysql Backup Script 2

Posted by Chris Barnes Thu, 06 Sep 2007 21:06:00 GMT

Need a way to backup all your mysql databases in seperate files without eating up your hard drive, then this is the script you need.

Its a bash shell script that will export all your databases with mysqldump into seperate files named like 'database-date.sql'. It will also delete your old backups so you don't fill up your hard drive.

#!/bin/bash

DBHOST='localhost'
DBUSER='root'
DBPASSWD='password'
LOCALDIR=/path/to/your/backup/directory/

# Using the date function's --date="last week" to delete backups that are 7 days old 
OLDSUFFIX=`date --date="last week" +%Y%m%d`
SUFFIX=`date +%Y%m%d`
DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`
su_cmd=""
for DATABASE in $DBS; do
  if [ $DATABASE != "Database" ]; then
    OLDFILENAME=$OLDSUFFIX-$DATABASE.sql
    FILENAME=$SUFFIX-$DATABASE.sql
    su_cmd="${su_cmd} rm -f $LOCALDIR$OLDFILENAME;"
    mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE > $LOCALDIR$FILENAME
  fi
done
su -c "${su_cmd}"
echo "done";

Trackbacks

Use the following link to trackback from your own site:
http://blog.randomutterings.com/articles/trackback/21

  1. Lexapro vs prozac. From Prozac.
    Prozac mood disorder. Prozac side effects. Prozac. Prozac withdrawal.

Comments - Leave a response

  1. Avatar
    jason 10 months later:
    forgive my ignorance. How/Where do i implement this into my site to make it work.
  2. Avatar
    Chris Barnes 10 months later:
    All you need to do is save this script somewhere on your server and execute it and if you want to automate it you can set it up as a cron job.

    Don't forget to change the DBUSER, DBPASSWD, and LOCALDIR to something appropriate.

    If you need more detailed help, post a comment with any specific questions you have and I'll be happy to assist.