Wednesday, June 24, 2009

Scheduled Encrypt, Back-up mySQL database and send to Secure FTP

I needed a way to backup our mySQL database, encrypt it and send it via Secure FTP to a server that was not our webserver (to have it in a different location).

Turns out that this isn't as straight-forward as it may seem if you want to use a cron job to schedule it on a regular basis.  The reason being, SSH provides ways to make things secure, and thus, hard to automate.

So you end up giving up some of the security to automate it.  I thought that this would be okay, as if an attacker got onto my webserver, my security is compromised anyway, and seeing the Secure FTP settings in the script would be a moot point.  I basically just didn't want to be passing the data in an non-secure pipe.

So here it is, a hybrid bash shell, with some expect scripting built in:
#! /bin/bash # MySQL server name SERVER= # server directory to backup to BACKDIR= # date format that is appended to filename DATE=`date +'%m-%d-%Y'` #----------------------MySQL Settings--------------------# # MySQL server location HOST=localhost # MySQL username USER= # MySQL password PASS= # List all of the MySQL databases that you want to backup in here, # each separated by a space DBS="" # set to 'y' if you want to backup all your databases. this will override # the database selection above. DUMPALL=n #----------------------FTP Settings--------------------# # set "FTP=y" if you want to enable FTP backups FTP=y # FTP server settings; should be self-explanatory FTPHOST="" FTPUSER="" FTPPASS="" # directory to backup to. if it doesn't exist, file will be uploaded to # first logged-in directory FTPDIR="" #-------------------Deletion Settings-------------------# # delete old files? DELETE=y # how many days of backups do you want to keep? DAYS=7 #----------------------End of Settings------------------# # check of the backup directory exists # if not, create it if [ -e $BACKDIR ] then echo "Backups directory already exists" else mkdir $BACKDIR fi if [ $DUMPALL = "y" ] then echo "Creating list of all your databases..." mysql -h $HOST --user=$USER --password=$PASS -e "show databases;" > dbs_on_$SERVER.txt # redefine list of databases to be backed up DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' dbs_on_$SERVER.txt` fi echo "Backing up MySQL databases..." for database in $DBS do mysqldump -h $HOST --user=$USER --password=$PASS $database > \ $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql # Encrypt DB Dump using GPG gpg --symmetric --batch --passphrase-fd 0 < --output $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql.gpg $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql.gpg done if [ $FTP = "y" ] then echo "Initiating Secure FTP connection..." cd $BACKDIR ATTACH=`for file in *$DATE.sql.gpg.gz; do echo -n -e "put ${file}\n"; done` # Uses sftp expect - <spawn sftp $FTPUSER@$FTPHOST # expect "Are you sure you want to continue connecting (yes/no)?" # send "yes\r" expect "$FTPUSER@$FTPHOST's password:" send "$FTPPASS\r" expect "sftp>" send "cd backups\r" expect "sftp>" send "$ATTACH\r" expect "sftp>" send "quit\r" EOF echo -e "Secure FTP transfer complete! \n" fi if [ $DELETE = "y" ] then find $BACKDIR -name "*.sql.gpg.gz" -mtime $DAYS -exec rm {} \; find $BACKDIR -name "*.sql" -mtime $DAYS -exec rm {} \; if [ $DAYS = "1" ] then echo "Yesterday's backup has been deleted." else echo "The backup from $DAYS days ago has been deleted." fi fi echo Database backup complete!# expect "Are you sure you want to continue connecting (yes/no)?"# send "yes\r"expect "$FTPUSER@$FTPHOST's password:"send "$FTPPASS\r"expect "sftp>"send "cd backups\r"expect "sftp>"send "$ATTACH\r"expect "sftp>"send "quit\r"EOFecho -e "Secure FTP transfer complete! \n"fi if [ $DELETE = "y" ]thenfind $BACKDIR -name "*.sql.gpg.gz" -mtime $DAYS -exec rm {} \;find $BACKDIR -name "*.sql" -mtime $DAYS -exec rm {} \; if [ $DAYS = "1" ]thenecho "Yesterday's backup has been deleted."elseecho "The backup from $DAYS days ago has been deleted."fifi echo Database backup complete!

No comments: