Shell script to backup a Mysql database and save it on a remote server using Ftp

ENG MUHAMED

[ADMIN]
طاقم الإدارة
25 فبراير 2008
10,079
43
48
support-ar.net
بسم الله الرحمن الرحيم


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"

    # Specify Database Name
    dbname="dbname_here"

    # Save the current date
    date=`date '+%Y-%m-%d'`

    # Specify Ftp details of the remote server.
    ftpserver="FtpServerIP"
    ftpuser="your ftp user"
    ftppass="your ftp password"

    # Dump the mysql database with the current date and compress it.
    # Save the mysql password in a file and specify the password file
    # path in the below command.

    /usr/bin/mysqldump -uroot -p`cat /path/to/pass/file` $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##############

Make sure to specify your db_name, ftpserver, ftpuser and ftppass values in 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
Note: Leave a comment if you have any suggestions, questions OR have received any error message using this script.​