I was just wondering what methods admins here use to backup their website?

I normally used phpmyadmin, but for larger databases it does not help as much.

What methods do you use to copy your database, and save it to your server and/or PC machine at home/office?

Hello,

I think in case of linux server with cpanel the cpanel backup option would suffice as it would take the backup of the domain as well as the corresponding mysql databases. If you want mysql backup option it would be better if you make your own backup scipt for mysql alone and take backup either remote or locally(provided that shell access is available)

I prefer the second method as it is more fast, saves a lot of the system resources and time.

cheers

if you have SSH access for your hosting account , you can use the mysqldump command.

I use this for some sites. Creates daily backups, rotates weekly, can email or FTP an off-site copy. Enjoy!

#!/bin/sh

# This script will backup one or more mySQL databases
# and then optionally email them and/or FTP them

# This script will create a different backup file for each database by day of the week
# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
# This is a trick so that you never have more than 7 days worth of backups on your FTP server.
# as the weeks rotate, the files from the same day of the prev week are overwritten.
# name it backup.sh (or whatever), and set up a cron (daily):
# /bin/sh /path/to/script/backup.sh > /dev/null
############################################################
#===> site-specific variables - customize for your site

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="database_name"

# Directory where you want the backup files to be placed
backupdir=/home/user/backup

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password use root mysql info if many dbs are being backed up
userpassword=" --user=mysql_username --password=*****"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="My Backup"
mailto="your_alias@example.com"

#===> site-specific variables for FTP
ftpbackup="y"
ftpserver="ip.address.goes.here"
ftpuser="ftp_username"
ftppasswd="*****"
# If you are keeping the backups in a subdir to your FTP root
ftpdir="/"

#===> END site-specific variables - customize for your site
############################################################

# Get the Day of the Week (0-6)
# This allows to save one backup for each day of the week
# Just alter the date command if you want to use a timestamp
DOW=`date +%w`

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${DOW}-${database}.sql.gz
$gzip ${backupdir}/${DOW}-${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
done
fi

# FTP it to the off-site server
echo "FTP file to $ftpserver FTP server"
if [ $ftpbackup = "y" ]
then
for database in $databases
do
echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
ftp -n $ftpserver <<EOF
user $ftpuser $ftppasswd
bin
prompt
cd $ftpdir
lcd ${backupdir}
put ${DOW}-${database}.sql.gz
quit
EOF
done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

Yes, Use mysqldump

If you can't run that personally you always can relay on your web hosting provider and ask for the help

Normally I'd do a manual mysqldump over SSH. But bear's script looks interesting, maybe I'll give it a try, thanks for sharing!

yes, mysqldump through ssh

i use a software

in linux

it would be better to write a script for managing mysql backup

suppose you have a database 'shopcart' and you want to backup this to /user1/db_backup

for that create a file db_backup.sh and add the following content in to it

dt=$(date +%T-%d_%m_%Y)
backup_path="/user1/db_backup";
backup_name="$backup_path/shopcart_$dt";
mysqldump shopcart>$backup_name.sql

save the file and run

#sh db_backup.sh
it will create a db backup of the databae shopcart in the directory /usr1/db_backup

the format of the backup file is something like 'shopcart_12:53:04-20_10_2009.sql'


if you need automated daily backup you can do it using cron


hope it helps you
Rasin