[Guide] Automated backups (*nix)

[lillecarl]

Hey, i just managed to put together a working (and slim) MySQL ftp backup script so ill share it here

you need to have this on the computer where the backups is going to run from:

apt-get install mysql-client ncftp

#!/bin/bash

### MySQL Server Login Info ###

MUSER="mangos"

MPASS="mangos"

MHOST="localhost"

MYSQL="$(which mysql)"

MYSQLDUMP="$(which mysqldump)"

BAK="/home/mangos/backup"

GZIP="$(which gzip)"

### FTP SERVER Login info ###

FTPU="ftpuser"

FTPP="ftppass"

FTPS="fptsrvr"

NOW=$(date +"%d-%m-%Y")

[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

echo MySQL Database dumping initialized

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

for db in $DBS

do

if [ "$db" != "information_schema" ]; then

echo Started dumping $db

FILE=$BAK/$NOW-$(date +"%T").$db.gz

$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE

echo Finished dumping $db

fi

done

echo Finished dumping, will now begin ftp transfer to $FTPS

ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF

mkdir $NOW

cd $NOW

lcd $BAK

mput *

quit

EOF

echo MySQL database backups finished

EDIT: I'm using this crontab:

0 */6 * * * screen -AmdS mysql_backup /home/mangos/mysqlbackup

You can edit "/6" to the interval you want as example "/2" would make backups each 2nd hour while i do each 6th hour

for more information read: http://en.wikipedia.org/wiki/Cron

NOTE: There will always be 1 local copy of the databases, (latest) all other are only accessible on the FTP server and the database inside the gzip archives is not stored with .sql file extention, they are just plain text, but contains all SQL information (mostly a point to windows users)

NOTE2: The backups are stored in the ftp users "home" directory, so make a new user with only write access and select his home directory (for security reasons since the password is stored in plain text)

Feel free to send me a PM if you need help with the setup of this system

- LilleCarl<3