DC_Sara — Wed, 14 Dec 2005 23:49:26 +0000

I back up my Vbulletin every night through PHP admin via Hsphere.

James — Mon, 12 Dec 2005 23:58:25 +0000

Here is a semi-automated backup method via cron jobs.

1: Save the following text as a file ending in ".pl"
2: Upload it in text FTP transfer mode (not binary) to your cgi-bin directory
3: Create a cronjob to execute this script every timecycle you want.

CODE wrote:
#!/usr/bin/perl
use Net::FTP;

# ************************************************** ********
# This is a simple script to place in your CPanel cron jobs that will FTP your
# backups of your MySQL databases to any FTP server you wish. By doing this,
# you will have automated backups of your databases sent to this FTP server
# on a routine basis. Then, you will have peace of mind knowing that HASWEB's
# servers were to go offline, you'd have recent backups.
# ************************************************** ********

# ***********
# Installation: *
# ***********

# You will have to rename the file sql_ftp_backup.pl and make sure it has
# executable permission set (mode 755 will work) which can be done with your FTP client

# 1. Download and save this script to your local computer.
# 2. Open the file in a text editor like Notepad with Word Wrap turned off.
# 3. Edit the variables with the <> symbols around them.
#
# SEE PROGRAMMER'S NOTE SECTION BELOW!!!!
#
# (Make sure you leave OFF your username from the database names.
# For a complete list of db names, check out your backup section on CPanel)
#
# 4. Save the document and then rename it to sql_ftp_backup.pl
# 5. Upload the file to your home directory (The main root of your FTP dirs)
# 6. Make sure that it has been renamed to sql_ftp_backup.pl
# 7. Make sure that the CHMOD setting is executable (mode 755)

# ********
# Usage: *
# ********
# 1. Go to your cPanel and click on "Cron Jobs".
# 2. Click on the standard button.
# 3. Enter a valid email address in the top entry. This is where any error output
# from the script will be sent in case the script fails.
# 5. In the entry box "Command to run" enter: /home/simplele/sql_backup.pl
# 6. The default configuration is to have the script run every day at 3am
# If you want to change this, simply alter the config you see on your screen
#
# For instance, if you want it to run every Sunday, just click on Sunday in
# the weekday selection box.
#
# 7. Test the script out by setting it to run once every minute. After you
# are confident that it will work correctly, set it to once a day/once a week.
#
# 8. Click "Save Crontab"
#
# Enjoy!

# October 2003 - David Sierkowski (scripts@phonism.net)
# EDIT FOR FTP: November 26, 2003 - Justin Tubbs (justin@simpleletter.com)

# ************************************************** ********
# (from the BSD license. in short, I'm not accountable for anything this script does to you.)
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
# DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
# SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
# OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
# OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
# ************************************************** ********
use MIME::Lite;

# ************************************************** ********
# PROGRAMMER'S NOTE SECTION:
# In this section, any variables with <> around them need to be filled.
# Remove the "<>" marks and the caps text inside and replace with your parameters.
# ************************************************** ********

# make sure this is your cPanel username - CASE SENSITIVE
$user = "";
# enter your cPanel password here - CASE SENSITIVE
$password = "";

# enter the list of databases you want to have sent to you
# leave off your username. use the database names listed on your backup page
# in cPanel
#
# USE THIS FORMAT - CASE SENSITIVE:
# @dbs = qw(addressbook calendar mailingList) etc... (space separated)
#
@dbs = qw(addressbook calendar mailingList);

# this should be /home/yourusername/tmp or /home2/yourusername/tmp
# go with /home/ and if the script fails, switch it to /home2/
$tmp_dir = "/home//tmp";
# mysqldump options to make a cPanel restore compatible dump
#
# NOTE: if you want to have a backup compatible with cPanel's restore
# mechanism, leave the $dump_opts alone. Only change this if you know what
# you are doing.
$dump_opts = "-c --add-drop-table";

# name the file you want to contain backup log information
$msg_file = $tmp_dir . "/msg.txt";

# set a variable with today's date in it
$date = `date +'%m-%d-%Y'`;

open(MSG,">$msg_file");
print MSG "Here are the MySQL Dumps, generated by SQL_Backup: \n";
print MSG "\nThe following databases are enclosed: \n\n";

# Loop through the array of db names and send each backup script to FTP server
foreach $db (@dbs)
{
system("mysqldump $dump_opts --user=$user --password=$password $user\_$db > $tmp_dir/$db.sql");
print MSG "\t $db\n";
push(@atts,"$tmp_dir/$db.sql");

$ftp = Net::FTP->new("", Debug => 0) or die "Cannot connect to : $@";
$ftp->login("","") or die "Cannot login ", $ftp->message;
$ftp->cwd("") or die "Cannot change working directory to ", $ftp->message;
$ftp->put("$tmp_dir/$db.sql") or die "get failed ", $ftp->message;
$ftp->quit;
}
print MSG "\n";
close(MSG);

mairving — Fri, 02 Dec 2005 16:19:08 +0000

lamad wrote: agreed with mairving. nothing compares to automated frequent backups.

backing up is actually quite simple. have two machines, both connected to the internet and at physically seperate locations if your concerned about the building burning down, etc.

have a cron job that runs nightly.

rysnc your web and sql root directories to the backup server.

ftp is too insecure, sftp secure but still slow. rsync has many advantages. the rsync website has various backup examples. i recomend a 7 day rotating backup.

I wouldn't rsync your db directory. I think that is what you are talking about. The reason is that you can get a corrupt database if you rsync it while you are writing to it. It would be better to dump the db, then sync that up. Rsync is a great program but make sure that it is secured before you use it.

lamad — Fri, 02 Dec 2005 16:00:38 +0000

agreed with mairving. nothing compares to automated frequent backups.

backing up is actually quite simple. have two machines, both connected to the internet and at physically seperate locations if your concerned about the building burning down, etc.

have a cron job that runs nightly.

rysnc your web and sql root directories to the backup server.

ftp is too insecure, sftp secure but still slow. rsync has many advantages. the rsync website has various backup examples. i recomend a 7 day rotating backup.

mairving — Tue, 29 Nov 2005 03:19:44 +0000

Aven101 wrote: You should also consider backing up your database every once in a while, in case anything bad happens

Because it happened to me, and I lost everything

Every once and awhile ain't going to cut it. If you care about back it up daily using a cron job. Set it, test it regularly and you will feel much better if your data suddenly disappears.

Aven101 — Tue, 29 Nov 2005 02:27:56 +0000

You should also consider backing up your database every once in a while, in case anything bad happens

Because it happened to me, and I lost everything

bleep — Thu, 06 Oct 2005 15:09:44 +0000

thanks everyone

mairving — Thu, 06 Oct 2005 13:43:28 +0000

Here is one that I use that backups the database and ftp's the backup to another server (note: you don't have to tell it the db name, since it will backup all that you have permission to):

#!/bin/bash

#####################################
### MySQL Configuration Variables ###
#####################################

# MySQL Hostname
DBHOST='$hostname'

# MySQL Username
DBUSER='$mysql_username'

# MySQL Password
DBPASSWD='$mysql_password'

#####################################
### FTP Configuration Variables #####
#####################################

# FTP Hostname
FTPHOST='$remote_ftpsite'

# FTP Username
FTPUSER='$remote_username' 

# FTP Password
FTPPASSWD='$remote_password'

# Local Directory for Dump Files
LOCALDIR='$local_directory'

# Remote Directory for Offsite Backup
REMOTEDIR='$remote_directory'

# Prefix for offsite .tar file backup 
TARPREFIX='$tar_prefix'

#####################################
### Edit Below If Necessary ######### 
#####################################

cd $LOCALDIR
SUFFIX=`eval date +%m%d%Y-%k%M`

DBS=`/usr/local/bin/mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`
for DATABASE in $DBS
do
	if [ $DATABASE != "Database" ]; then
		FILENAME=$SUFFIX-$DATABASE.gz
		/usr/local/bin/mysqldump --opt -Q -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME
	fi
done

chmod 400 $LOCALDIR*.gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz

ftp -n $FTPHOST <<END_SCRIPT
quote USER $FTPUSER
quote PASS $FTPPASSWD
cd $REMOTEDIR
put $TARPREFIX-$SUFFIX.tar
quit
END_SCRIPT

rm -f $TARPREFIX-$SUFFIX.tar

exit 0

Here is another that will backup your db and send it to your gmail account. I haven't tested it though.

<?php
// Mysql Database Info:
$database = "db_name";   // name of the database.
$username = "db_uname";  // username with access to database.
$password = "db_pass";  // password for username.

// E-mail Information:
$mailto = "";   // email address to send the database to.
$fromaddr = ""; // Email addres this should be shown as sent from
$subject = "$database backup for ".date("F d, y");  // subject of email.
$message = "Here is the $database database backup";  // message body.

// Options:
$compress_file = '1'; // 1 = Compress the sql file with tar.gz  0 = Make it a plain text sql file.
$send_email = '1';  // 1 = send backup copy via e-mail.  0 = just backup data, don't e-mail.
$delete_local = '1';  // 1 = delete local copy when done.  0 = leave local copy when done.

// Database Backup Filename & Location
$backupas = 'backup-'.$database.'-'.date("Y_m_d_H_i");  // filename to backup the database as.
$backupto = '/home/username/public_html/upload';  // absolute path to folder containing database - 
no trailing slash. - must be a writeable location



// END of variables.
// Do Not Edit Beyond This Point


//Global Variables
$mail_boundary = '';


passthru ("mysqldump -u$username -p$password $database >$backupto/$backupas.sql", $error);
if($error) { echo "Problem: $error with backup"; exit;}

if ($compress_file) {
passthru ("tar -cpzf $backupto/$backupas.tar.gz $backupto/$backupas.sql",$error);
if($error) { echo "Problem: $error with compress"; exit;}
unlink("$backupto/$backupas.sql");
$backupas = "$backupas.tar.gz";
} else {
$backupas = "$backupas.sql";
}



if ($send_email) {
$mail_boundary = md5(uniqid(time()));
$mail_head = "From: $fromaddr\r\n";
$mail_head .= "X-Mailer: PHP/" . phpversion();
$mail_head .= "MIME-Version: 1.0\r\n";
$mail_head .= "Content-type: multipart/mixed;boundary=\"$mail_boundary\"";
$mail_head .= "\r\n\r\n";
$mail_head .= "This is a multi-part message in MIME format.";
$mail_head .= "\r\n\r\n";

$db_file = "$backupto/$backupas";
$fp = fopen($db_file, "r");
$file = fread($fp, filesize($db_file));
$file = chunk_split(base64_encode($file));

$mail_body = "--$mail_boundary\n";
$mail_body .= "Content-type: text/plain; charset=us-ascii\r\n";
$mail_body .= "Content-transfer-encoding: 8bit\r\n\r\n";
$mail_body .= " $message\r\n";
$mail_body .= "--$mail_boundary\r\n";
    
$filename = basename($db_file);

$mail_body .= "Content-type: application/octet-stream; name=$filename\r\n";
$mail_body .= "Content-transfer-encoding:base64\r\n\r\n";
$mail_body .= $file. "\r\n\r\n";
$mail_body .= "--$mail_boundary--";

mail($mailto, $subject, $mail_body, $mail_head);
}

if ($delete_local) {
unlink("$backupto/$backupas");
}


?>

timjpriebe — Thu, 06 Oct 2005 12:14:38 +0000

If you have CPanel or something similar, there is likely a built in option that says "Back up database"

mairving — Thu, 06 Oct 2005 02:49:01 +0000

Plenty of ways to do it. Here is one way.