Shell script: Automatic backup of mySql database

This script will create a backup file including the current date so you can have multiple copies of the backups of the same database under one directory.
Create a file called mysqlbkup.sh

# vi /root/mysqlback.sh
and paste the following code in the file as it is.

##############THE SCRIPT##############
#!/bin/bash
# Specify the temporary backup directory
BKUPDIR="/tmp"
# Database Name
dbname="dbname_here"
# store the current date
date=`date '+%Y-%m-%d'`
# Specify Ftp details
ftpserver="FtpServerIP"
ftpuser="username"
ftppass="password"
# Dump the mysql database with the current date and compress it.
#Save the mysql password in a file and specify the path below
/usr/bin/mysqldump -uroot -p`cat /path/to/passfile` $dbname | gzip > $BKUPDIR/$date.$dbname.sql.gz
# Change directory to the backup directory
cd $BKUPDIR
# Upload the backup
ftp -n $ftpserver <user $ftpuser $ftppass
binary
prompt
mput *.sql.gz
quit
!EOF!
# Remove the local backup file
/bin/rm -f /$BKUPDIR/$date.$dbname.sql.gz
##############END OF THE SCRIPT##############

Save the file and schedule a cronjob to execute the file on daily basis, say during night hours at 1.00AM. Edit the cron file

# crontab -e
and set the following cronjob

0 1 * * * /bin/sh /root/mysqlback.sh
save the file and restart the crond service

# service crond service
The script will work on a Linux/Plesk server as well. You just have to replace the mysqldump line in the script with the following

/usr/bin/mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $dbname | gzip > $BKUPDIR/$date.$dbname.sql.gz
Make sure you assign the db_name, ftpserver/user/pass values properly at the start of the script.