his is a simple backup solution for people who run their own web server and MySQL database server on a dedicated or VPS server. Most dedicated hosting provider provides backup service using NAS or FTP servers. These service providers will hook you to their redundant centralized storage array over private VLAN. Since, I manage couple of boxes, here is my own automated solution. If you just want a shell script, go here (you just need to provided appropriate input and it will generate FTP backup script for you on fly, you can also grab my php script generator code).

Making Incremental Backups With tar

You can make tape backups. However, sometime tape is not an option. GNU tar allows you to make incremental backups with -g option. In this example, tar command will make incremental backup of /var/www/html, /home, and /etc directories, run:
# tar -g /var/log/tar-incremental.log -zcvf /backup/today.tar.gz /var/www/html /home /etc

Where,

• -g: Create/list/extract new GNU-format incremental backup and store information to /var/log/tar-incremental.log file.

Making MySQL Databases Backup

mysqldump is a client program for dumping or backing up mysql databases, tables and data. For example, the following command displays the list of databases:
$ mysql -u root -h localhost -p -Bse 'show databases'

Output:

Enter password:
brutelog
cake
faqs
mysql
phpads
snews
test
tmp
van
wp

Next, you can backup each database with the mysqldump command:
$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz

Creating A Simple Backup System For Your Installation

The main advantage of using FTP or NAS backup is a protection from data loss. You can use various protocols to backup data:

1. FTP
2. SSH
3. RSYNC
4. Other Commercial solutions

However, I am going to write about FTP backup solution here. The idea is as follows:

• Make a full backup every Sunday night i.e. backup everything every Sunday
• Next backup only those files that has been modified since the full backup (incremental backup).
• This is a seven-day backup cycle.

Our Sample Setup

   Your-server     ===>       ftp/nas server
IP:202.54.1.10   ===>       208.111.2.5

Let us assume that your ftp login details are as follows:

• FTP server IP: 208.111.2.5
• FTP Username: nixcraft
• FTP Password: somepassword
• FTP Directory: /home/nixcraft (or /)

You store all data as follows:
=> /home/nixcraft/full/mm-dd-yy/files – Full backup
=> /home/nixcraft/incremental/mm-dd-yy/files – Incremental backup

Automating Backup With tar

Now, you know how to backup files and mysql databases using the tar and mysqldump commands. It is time to write a shell script that will automate entire procedure:

1. First, our script will collect all data from both MySQL database server and file system into a temporary directory called /backup using a tar command.
2. Next, script will login to your ftp server and create a directory structure as discussed above.
3. Script will dump all files from /backup to the ftp server.
4. Script will remove temporary backup from /backup directory.
5. Script will send you an email notification if ftp backups failed due to any reason.

You must have the following commands installed (use yum or apt-get package manager to install ftp client called ncftp):

• ncftp ftp client
• mysqldump command
• GNU tar command

Here is the sample script:

#!/bin/sh
# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### System Setup ###
DIRS="/home /etc /var/www"
BACKUP=/tmp/backup.$$
NOW=$(date +"%d-%m-%Y")
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +"%a")
FULLBACKUP="Sun"
### MySQL Setup ###
MUSER="admin"
MPASS="mysqladminpassword"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP server Setup ###
FTPD="/home/vivek/incremental"
FTPU="vivek"
FTPP="ftppassword"
FTPS="208.111.11.2"
NCFTP="$(which ncftpput)"
### Other stuff ###
EMAILID="admin@theos.in"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" == "$FULLBACKUP" ]; then
  FTPD="/home/vivek/full"
  FILE="fs-full-$NOW.tar.gz"
  tar -zcvf $BACKUP/$FILE $DIRS
else
  i=$(date +"%Hh%Mm%Ss")
  FILE="fs-i-$NOW-$i.tar.gz"
  tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
mkdir $FTPD
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF
### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
 rm -f $BACKUP/*
else
 T=/tmp/backup.fail
 echo "Date: $(date)">$T
 echo "Hostname: $(hostname)" >>$T
 echo "Backup failed" >>$T
 mail  -s "BACKUP FAILED" "$EMAILID" <$T
 rm -f $T
fi

How Do I Setup a Cron Job To Backup Data Automatically?

Just add cron job as per your requirements:
13 0 * * * /home/admin/bin/ftpbackup.sh >/dev/null 2>&1

Generate FTP backup script

Since I setup many Linux boxes, here is my own FTP backup script generator. You just need to provided appropriate input and it will generate FTP backup script for you on fly.

You can use mysqldump command to backup database. The mysqldump client is a backup program. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server. The dump contains SQL statements to create the table or populate it, or both.

Once database is dumped, you need to upload the same to ftp server. Use lftp client to upload all files.

Install lftp

lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:

sudo apt-get install lftp

Shell script to backup MySQL database server

Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server. You need to setup correct username and password before using the script:

 #!/bin/bash### MySQL Server Login Info ###MUSER="root"MPASS="MYSQL-ROOT-PASSWORD"MHOST="localhost"MYSQL="$(which mysql)"MYSQLDUMP="$(which mysqldump)"BAK="/backup/mysql"GZIP="$(which gzip)"### FTP SERVER Login info ###FTPU="FTP-SERVER-USER-NAME"FTPP="FTP-SERVER-PASSWORD"FTPS="FTP-SERVER-IP-ADDRESS"NOW=$(date +"%d-%m-%Y") ### See comments below ###### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###[ ! -d "$BAK" ] && mkdir -p "$BAK" DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"for db in $DBSdo FILE=$BAK/$db.$NOW-$(date +"%T").gz $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILEdone lftp -u $FTPU,$FTPP -e "mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

Save script as /home/your-name/mysql.backup.sh file. Setup executable permission:
$ chmod +x /home/your-name/mysql.backup.sh
To backup MySQL, enter:
/home/your-name/mysql.backup.sh
OR
sudo /home/your-name/mysql.backup.sh

Run MySQL backup script as cron job

To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
$ sudo crontab -e
Append following cron job:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
Save and close the file. Please note that above script should work with other Linux distros or UNIX like oses.

<?php
// Create the mysql backup file
// edit this section
$dbhost = "yourhost"; // usually localhost
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdb";
$sendto = "Webmaster <webmaster@yourdomain.com>";
$sendfrom = "Automated Backup <backup@yourdomain.com>";
$sendsubject = "Daily Mysql Backup";
$bodyofemail = "Here is the daily backup.";
// don't need to edit below this section

$backupfile = $dbname . date("Y-m-d") . '.sql';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");

// Mail the file

    include('Mail.php');
    include('Mail/mime.php');

	$message = new Mail_mime();
	$text = "$bodyofemail";
	$message->setTXTBody($text);
	$message->AddAttachment($backupfile);
    	$body = $message->get();
        $extraheaders = array("From"=>"$sendfrom", "Subject"=>"$sendsubject");
        $headers = $message->headers($extraheaders);
    $mail = Mail::factory("mail");
    $mail->send("$sendto", $headers, $body);

// Delete the file from your server
unlink($backupfile);
?>