Probably because command line backup is so easy:

mysqldump -h [host] --user=[mysql_user] --password=[mysql_pass] [db_table] > [backup_file.sql]

And restore is as easy:

mysql> source [backupfile.sql]

These two commands are easily entered into most scripts.

I understand what I need to replace in the command line you created. Do I need to place this command into a file? If so, what should I label the file and what extension? Also, should this be placed in my public_html directory or in the root or home directory?

Whew ... lotta questions for a first post! :)

The mysqldump is a command line program, so you'll need to have a command line login to use it. There's probably some way to access it using something like PHP, or in Perl by using a "system" command - assuming your web host allows it... It may be worth asking your hosting company what they recommend.

As a non-technical person this makes me pretty nervous too. I used phpMyAdmin at first, but the max database size is ridiculously low.

I use Drupal 5, and there are a couple of backup and restore modules. I'm currently using the module "Backup and Migrate" though at the moment my primary contractors are dealing with it, so it hasn't been idiot-tested yet. (I.e., by me)

My hosting company also runs nightly backups, though again I don't have experience trying to restore from one.

Because I'd like to understand a bit more, and be able to experiment with the CMS itself, I've decided to pay for a couple hours' consulting to walk me through how to run and synch local, dev, and production servers. This will allow me to play with new modules without affecting what my users see -- and should get me up to speed on how I'd restore the whole site (the CMS as well as the content database) from my local machine if something bad happened to the production server.

That will probably be this weekend or next (the guy is a rare commodity - both a good developer and a good teacher - so I have to work around his schedule).

Not sure my situation will really apply to yours, but I'll aim to post my notes.

Is it better to use any specific scripts to do the job?
Or is phpmyadmin the best way to backup, restore?
I personally think a CMS is incomplete without the option to backup, restore database within its control panel.

You should definitely go with an automated script. This is not because making a backup with either PhpMyAdmin or a control panel is not possible, but because it is human driven. You may forget to do it, you may choose the wrong formatting options in PhpMyAdmin making the backup effectively unusable and it is time you can use for other things.

If you make daily full backups (recommended) and each backup takes 15 minutes (which may be much more when compressing and downloading large databases over a home connection) you will spend more than 90 hours a year just sitting there, waiting for the backup to complete. If you charge $20 per hour for your own activity, that is $1800 wasted time.

My server performs a backup each night of both the SQL databases and all user changable content, scripts etc. The information is zipped and automatically FTP-ed to an FTP backup server of the hosting company. I use an automatic round robin scheme based on the weekday number so I have always seven backups to choose from if something goes terribly wrong. I also now and then download a backup to either my home computer or another remote server for long time storage.

The backupscript is run from crontab and has the following format. The result of the action, including a current listing of the files in the FTP backup space is emailed to me every night.

<<<START OF SCRIPT>>>

#!/bin/bash

# Turn on extended pattern matching features to Exclude files and directories

shopt -s extglob

# Set some variables and backup options. Note that all backup files
# will be named X-filename.tgz, where X is the number of the day
# of the week (0..6) It is easy by changing the BACKDAY variable
# to change this in the day of the month (1..31) or even a complete
# date (2008-01-12), just by changing the options to the /bin/date
# command in the following line.

BACKDAY=`/bin/date +%w`
BACKDIR=/tmp/backup-dir.$$
BASEOPT="--create --ignore-failed-read --totals --gzip"

echo ""
echo "Dumping MySQL databases to regular file"
/usr/local/mysql-4.1.22/bin/mysqldump --all-databases --quote-names --opt > /var/www/sqldumps/all_databases.sql

# We want to include the complete path( without trailing /) in
# the tar archives. We therefore have to make sure that we are
# located in the root directory before starting the tar calls.

/bin/mkdir $BACKDIR
cd /

# The tar archives are created. Gzip compression is used. We
# could use bzip compression but the results are only slightly
# better and test showed that the backup process may take five
# to ten times more time. During backup the server has a higher
# load than normal which may cause delays in HTTP requests. We
# therefore like to keep the high load period as short as possible.
#
# Note hat I compress the file with tar, and afterwards call gzip to
# test if the resulting file is correct. If anything goes wrong in
# this test, gzip will print some errors that I can read in my
# email te next morning.

echo ""
echo "Creating backup of /var/www"
BACKFILE=$BACKDIR/$BACKDAY-var-www.tgz
/bin/tar $BASEOPT --file $BACKFILE var/www
/bin/gzip --test --verbose $BACKFILE

(Here some repeating stuff for other directories deleted)

echo ""
echo "Creating backup of /usr/src excluding linux base"
BACKFILE=$BACKDIR/$BACKDAY-usr-src.tgz
/bin/tar $BASEOPT --file $BACKFILE usr/src/!(linux-2.6*)
/bin/gzip --test --verbose $BACKFILE

# Now do the FTP action. The FTP does a dir and quota listing
# which should be visible in the resulting email log. This makes
# it easy to test if the backup succeeded.

cd $BACKDIR
/usr/bin/ftp -v -n -i backupserver < /usr/local/etc/ftpcommands.ftp
cd /

# We don't need our temporary backup directory anymore. We
# delete it to preserve diskspace.

/bin/rm -rf $BACKDIR

<<<END OF SCRIPT>>>

The FTP command uses an input script ftpcommands.ftp. This has the following contents. Please note that the backup server is on a closed network with the server, so using plain FTP with a password mentioned in the script is no big deal in this case. If the transfer would have been remote, SFTP and properly setup keys or rsync over SSH would have been the way to go.

<<<ftpcommands.ftp>>>
user username password
bin
mput *.tgz
dir
site quota
quit
<<<ftpcommands.ftp EOF>>>