I have written a linux shell script to automatically backup a website: also, a php script can help to backup the database in case you do not have access to the database machine and cannot use the mysqldump command.

First of all, what will you need: the script uses wget and ncftp; you can download them with your favorite package manager on linux, e.g. on a debian machine:

# apt-get install wget
# apt-get install ncftp

The mysql backup script, instead, is written in PHP and it uses a nice PHP class written by Adam Globus-Hoenich which allows you to export mysql tables. Download the PHP class here. The PHP part should be put on your website, anywhere you like, like in the root directory; create on your site a directory where the backup will be created, let’s call it mysql_backup. Remember to protect this folder so that users cannot read it.

Here’s the PHP script:

$server = "mysqlserveraddress";
$username = "dbusername";
$password = "dbpassword";
$db = "dbname";

$cnx = mysql_connect($server, $username, $password) or die(mysql_error());
mysql_select_db($db, $cnx) or die(mysql_error());
$tables = mysql_list_tables($db) or die(mysql_error());

//Create a list of tables to be exported
$table_list = array();
while($t = mysql_fetch_array($tables)) {
	array_push($table_list, $t[0]);
}

//Instantiate the SQL_Export class
require("SQL_Export.php");
$e = new SQL_Export($server, $username, $password, $db, $table_list);
//Run the export
$dump = $e->export();

//Clean up the joint
mysql_close($e->cnx);
mysql_close($cnx);

$date = date( "Y-m-d" );
$fp = fopen( "mysql_backup/database.sql", "w" );
fwrite( $fp, "# DATABASE BACKUP " . $date . "\n\n" );
fwrite( $fp, $dump );
fclose( $fp );

The script uses the SqlExport class in order to write the dump of the table on a file, adding a comment with the current date to it. Let’s see the shell script now:

#!/bin/sh

#
# Created by Davide Cassenti 
# You can use, modify and distribute this script freely if you keep this comment lines intact.
#

CURDATE=`date +%Y-%m-%d`

# Create directories
mkdir /home/user/backup/www.yoursite.com/backup/
cd /home/user/backup/www.yoursite.com/

# Database dump
wget -q -O - http://www.yoursite.com/backupdb.php

# Enter the backup directory
cd backup

# Begin downloading files with ftp
ncftpget -R -u ftpusername -p ftppassword www.yoursite.com `pwd` /

# Return to the main directory
cd ..

# Tar and gzip the backup with the current date
tar czf $CURDATE.tgz backup

To use the script, simply modify the directories as you need, the name of the site and the ftp’s username and password. Save it, suppose in your home directory with a name such as site-backup.sh and add writing permission to the file. It would be useful to put the script in the crontab; for example, to call the script every night at midnight, modify the /etc/crontab adding the line:

0 0 * * * user /home/user/site-backup.sh

The script is pretty simple: first it creates the needed directories, if needed, then it will call the php script using wget; once done, it will begin to download files with ncftpget to the backup directory. At last, it tar and gzip it. The backup directory is not deleted, so that next time you run the script only the modified files will be downloaded. If you have any comment, let me know!