This auto mysql backup script will dumps all sql databases into a remote or local ftp server.

Create script called mysqlbackup.sh contains:

#!/bin/bash

# -------------------------------------------------------------------
#
# mysqlbackup ver 1.0a
#
# (c) Copyright 2003 Mark Steel. All rights reserved.
#
# This script may only be distributed unmodified.
#
# This script is intended to be used to backup all databases on a given
# server, and ftp them to the remote host of your choice.
#
# NOTE: THIS SCRIPT IS NOT GPL #
# -------------------------------------------------------------------
 
# MySQL Username & Password
mysqluser="CHANGE"
mysqlpass="CHANGE"

# Local path to store backups
localpath="/local/backup/path"

# FTP Information and remote directory
ftpserver="localhost"
ftpuserid="CHANGE"
ftppasswd="CHANGE"
ftpfolder="/remote/ftp/path"


# DO NOT MODIFY BELOW THIS LINE!
# -----------------------------------------------------------------------------
date=`date '+%Y%m%d-%H%M'`
for db in `mysqlshow -u$mysqluser -p$mysqlpass | \
tr -d " " | \
tr -d "|" | \
grep -v "+" | \
grep -v "Databases"`; do

mysqldump -u$mysqluser -p$mysqlapss --add-drop-table -a $db > $localpath/$date-$db.sql 2>&1
 
gzip -9 $localpath/$date-$db.sql > /dev/null 2>&1
done
 
ncftpput -u $ftpuserid -p$ftppasswd \
$ftpserver $ftpfolder $localpath/$date*gz > /dev/null 2>&1

 
if [ $? != 0 ]; then
echo && echo "Script failed"
exit
fi
  
# end

Now for the question about removing old ones, try this:

cleanbackups.sh :

#!/bin/sh
# -------------------------------------------------------------------------
#
# cleanbackups ver 1.0
#
# (c) Copyright 2003 Mark Steel. All rights reserved.
#
# This script may only be distributed unmodified.
#
# This script is intended to be used to backup all databases on a given
# server, and ftp them to the remote host of your choice.
#
# NOTE: THIS SCRIPT IS NOT GPL #
# -------------------------------------------------------------------------
# Local path to store backups - set the same as in mysqlbackup.sh
localpath="/local/backup/path"

# Number of days to keep backups
numdays="7"

# DO NOT MODIFY BELOW THIS LINE!
# -------------------------------------------------------------------------
/usr/bin/find $localpath -mtime +$numdays | /usr/bin/xargs /bin/rm -Rf 2>&1

Save both scripts to /usr/local/sbin and chmod it 700.

If you wanna run it daily via cron, just `ln -s /usr/local/sbin/mysqlbackup.sh /etc/cron.daily/mysqlbackup`

Done.