Server:Backups

Daily backups of the following databases are made and copied to WebDrive shared platform under account radon

1. ticktock.sql
2. ticktock_demo.sql
3. wiki.sql

Cron job set to run daily at 5:09am

09 5 * * * /home/www/sqlbackup.sh > /dev/null 2>&1

Cron job on Webdrive set to run daily at 5:10am to remove backups older than 7 days
/home/radon/ticktock.backups/remove_backups.sh

#!/bin/sh
find /home/radon/ticktock.backups/*sql.gz -mtime +7 -exec rm {} \;

sqlbackup.sh

#!/bin/sh
HOST='ftp.radon.co.nz'
USER='ticktock'
PASSWD='<secret>'
current_time=$(date "+%Y%m%d_%H%M%S")
FILE1="ticktock_$current_time.sql.gz"
FILE2="ticktock_demo_$current_time.sql.gz"
FILE3="wiki_$current_time.sql.gz"

# Create MySQL Backups
mysqldump --routines --user=tt_backup --password=tt_backup --single-transaction --databases ticktock > ticktock.sql
mysqldump --routines --user=tt_backup --password=tt_backup --single-transaction --databases ticktock_demo > ticktock_demo.sql
mysqldump --routines --user=tt_backup --password=tt_backup --single-transaction --databases wiki > wiki.sql

#remove definer
perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`(\w|\.|%)*\`//g" ticktock.sql
perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`(\w|\.|%)*\`//g" ticktock_demo.sql

#zip files
gzip -9 -c ticktock.sql > $FILE1
gzip -9 -c ticktock_demo.sql > $FILE2
gzip -9 -c wiki.sql > $FILE3

#FTP backups to ftp.radon.co.nz"
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put $FILE1
put $FILE2
put $FILE3
quit
END_SCRIPT

#Remove local Backup files"
rm $FILE1
rm $FILE2
rm $FILE3
exit 0