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.
#!/bin/bash
### MySQL Server Login Info ###
MUSER="MySQL User name"
MPASS="MySQL Pass"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/home/usera/backup/mysql"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
FTPU="FTP User name"
FTPP="FTP Pass"
FTPS="backup.dreamhost.com"
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
do
FILE=$BAK/$db.$NOW-$(date +"%T").gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
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.
Leave a comment