A MySQL backup script I wrote to backup all databases.

mysql_backup.sh

#!/bin/sh

# Remote FTP Configuration
USER="username"
PASSWD="password"
HOST="123.123.123.13"
# Remote FTP path to store the compressed backups.
RMDIR="/backups/mysql"

# Local configuration
DBUSER="databaseusername"
DBPASS="databasepassword"
DBHOST="127.0.0.1"
# Path to define where to store the raw backups
STORAGEPATH="/data/backups/mysql"

# Miscellaneous variables
DAY=$(date +%A)
HOSTNAME=`/bin/hostname`

# Check if the raw files exist.
DOES_EXIST=`sh -c "if [ -f $STORAGEPATH/*.sql ]; then echo 1; else echo 0; fi"`
if [ 1 -eq $DOES_EXIST ]; then
        echo "Found existing raw files, removing.."
        FILECNT=`ls -l $STORAGEPATH/*.sql | wc -l`
        rm -f $STORAGEPATH/*.sql
        rm -f $STORAGEPATH/*$HOSTNAME.tar.gz
        echo "INFO: $FILECNT Raw files removed."

else
        echo "No existing files to delete, continuing.."
fi
echo ""

# Start the main database dump.
echo "Creating the database dump."
echo "INFO: Table State (Locked)"
mysqldump -c -B -h$DBHOST -u$DBUSER -p$DBPASS --all-databases > $STORAGEPATH/db_backup_$DAY.sql
echo "Done. Table State now unlocked."
echo ""

# Compress the database data ready for FTP transfer.
echo "Compressing the raw .sql backup."
tar zcvf $STORAGEPATH/$DAY.$HOSTNAME.tar.gz $STORAGEPATH/db_backup_$DAY.sql
echo ""

# Remotely transfer the data to the FTP server.
echo "Uploading the backup to the remote host ($HOST)"
ncftpput -u "$USER" -p "$PASSWD" $HOST $RMDIR $STORAGEPATH/$DAY.$HOSTNAME.tar.gz;:
echo ""

echo "Process completed."

#EOF