The following shell script will dump the mysql database and will save the .sql file on a remote location using Ftp. 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/mysqlbkup.sh

and paste the following code in the file as it is.


    ##############START OF 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 <<!EOF!
    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/mysqlbkup.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.

Note: Leave a comment if you have any suggestions, questions OR have received any error message using this script.}