A simple script to backup MySQL databases with FTP.

Re-reqs – in this case I used ncftp as the client:

[html]
apt-get install ncftp
[/html]

Create and open a new Backup Script:

[html]

vim /path/to/backupscript.sh

[/html]

Insert the following code:

[html]

#!/bin/bash
# your MySQL server’s name
SERVER=my.server.com

# directory to backup to
BACKDIR=~/backups

# date format that is appended to filename
DATE=`date +’%m-%d-%Y’`

#———————-MySQL Settings——————–#

# your MySQL server’s location (IP address is best)
HOST=localhost

# MySQL username
USER=user

# MySQL password
PASS=password

# List all of the MySQL databases that you want to backup in here,
# each separated by a space
DBS=”dbname”

# set to ‘y’ if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=n

#———————-Mail Settings——————–#

# set to ‘y’ if you’d like to be emailed the backup (requires mutt)
MAIL=y

# email addresses to send backups to, separated by a space
EMAILS=”hello@who.com”

SUBJECT=”MySQL backup on $SERVER ($DATE)”

#———————-FTP Settings——————–#

# set “FTP=y” if you want to enable FTP backups
FTP=y

# FTP server settings; should be self-explanatory
FTPHOST=”10.10.10.10″
FTPUSER=”ftpuser”
FTPPASS=”ftppassword”

# 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=1

#———————-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
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
done

# if you have the mail program ‘mutt’ installed on
# your server, this script will have mutt attach the backup
# and send it to the email addresses in $EMAILS

if [ $MAIL = “y” ]
then
BODY=”Your backup is ready! Find more useful scripts and info at http://www.scriptuse.com”
ATTACH=`for file in $BACKDIR/*$DATE.sql.gz; do echo -n “-a ${file} “; done`

echo “$BODY” | mutt -s “$SUBJECT” $ATTACH $EMAILS

echo -e “Your backup has been emailed to you! n”
fi

if [ $FTP = “y” ]
then
echo “Initiating FTP connection…”
cd $BACKDIR
ATTACH=`for file in *$DATE.sql.gz; do echo -n -e “put ${file}n”; done`

ftp -nv <

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@matthewc424.sg-host.com.

Leave a Comment




Are you human? * Time limit is exhausted. Please reload the CAPTCHA.