Notes.

Here's a small backup script I use to do my daily MySQL backup dumps, and send them to a remote location/e-mail address. I know some people out there have been looking for this!

Used with (mt) MediaTemple, and should work perfectly with the (gs) GridService

#!/bin/sh

DATO=`date +%d%b-%H%M%S`

unset PATH
# USER VARIABLES
MYSQLUSER=your mysql user here
MYSQLPWD=password here
MYSQLHOST=localhost
MYSQLBACKUPDIR=/home/backup/mysql/mysql_backup$DATO

FILE=$MYSQLBACKUPDIR.tgz; # No need to modify this one
SUBJECT="Databasebackup for some server $DATO"
EMAIL="backup@yourdomain.com" # Where to send it..

# PATH VARIABLES - Needs to be set in order to be recognized by (mt)
MK=/bin/mkdir;
RM=/bin/rm;
GREP=/bin/grep;
MYSQL=/usr/bin/mysql;
MYSQLDUMP=/usr/bin/mysqldump;
XARGS=/usr/bin/xargs;
FIND=/usr/bin/find;
TAR=/bin/tar;
UUENCODE=/usr/bin/uuencode;
MAIL=/usr/bin/mail;


# CREATE MYSQL BACKUP

# Remove existing backup dir
# $RM -Rf $MYSQLBACKUPDIR

# Create new backup dir
$MK $MYSQLBACKUPDIR

#Dump new files
for i in $(echo 'SHOW DATABASES;' | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v '^Database$'); do
  $MYSQLDUMP                                                    \
  -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
  -Q -c -C --add-drop-table --add-locks --quick --skip-lock-tables   \
  $i > $MYSQLBACKUPDIR/$i.sql;
done;

# collect and compress the sqls
$TAR -cvzpf $MYSQLBACKUPDIR.tgz $MYSQLBACKUPDIR
$RM -r $MYSQLBACKUPDIR

# The send-email function
(echo "This e-mail message was generated by Kim Ellefsens backup script."; $UUENCODE $FILE $FILE) | $MAIL -s "$SUBJECT" $EMAIL

# Delete the old ones. Change +12 to whatever you need..
$FIND /home/backup/mysql/ -mtime +12|$XARGS rm

Help support my education.. or just buy me a coffee.