I have a VPS powered by Centos 5.2.
There are some very important forum on it. I would like to backup it every day.
Now because HyperVM/LxAdmin – Backup Home, can not backup MySQL or not easy to get it. I have to write the shell script to do the backup job.
To use the FTP I need lftp tool.
The whole picture looks like below.
1) Dump all MySQL databases and compress them, save them in /home/usera/backup/mysql/
2) FTP them to Backup server of Dreamhost at /vps-backup/mysql/
3) Remove the backup files 4 days old.
You have to use your own account name and password.
usera is my sample user name. You can also use any other FTP server to save your offsite backups.
You make a file under /home/usera/mysql.backup.sh
Put the content below in it, save and make it executive.

### MySQL Server Login Info ###
MUSER="MySQL User name"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
FTPU="FTP User name"
NOW=$(date +"%d-%m-%Y")
[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
FILE=$BAK/$db.$NOW-$(date +"%T").gz
lftp -u $FTPU,$FTPP -e "mkdir /vps/backup/mysql/$NOW;cd /vps/backup/mysql/$NOW; mput /home/usera/backup/mysql/*; quit" $FTPS
find /home/usera/backup/mysql -ctime +4 -exec rm {} \;

The last step is to run it with cron schedule job. I’d like to make it work at 2AM every day.

David Yin

David is a blogger, geek, and web developer — founder of FreeInOutBoard.com. If you like his post, you can say thank you here

Leave a Reply

Your email address will not be published. Required fields are marked *