Shell script to backup all mysql databases and upload via FTP

You have learnt how to backup MySQL databases using the mysql dump in my previous post. You may want to setup a cron job which will back up MySQL databases and upload them on a remote server using FTP. The following shell script will help you to achieve this.

#!/bin/bash

#Variables
FILENAME=/loc/to/file/$(date +%Y%m%d).sql
FILENAMEREMOTE=/loc/to/file/$(date +%Y%m%d).sql
FTPHOSTNAME=”ip and port”
FTPUSERNAME=””
FTPPASSWORD=””
SQLUSERNAME=””
SQLPASSWORD=””
MESSAGE=”Databases Backup Successful”

#SQL BACK UP
mysqldump -u $SQLUSERNAME –password=$SQLPASSWORD –all-databases > $FILENAME

#Upload file to FTP now
ftp -inv $FTPHOSTNAME << EOF
user $FTPUSERNAME $FTPPASSWORD
put $FILENAME $FILENAMEREMOTE
ls
bye
EOF

#Delete SQL file after backup.
rm -Rf $FILENAME

Save the above script and assign it a name which will be easy to identify. I will name it as mysqlbackup.sh. Assign it appropriate permission.

chmod +x mysqlbackup.sh

You can save the script in any location of your choice. I will store it in /scripts directory.

Now set the cron job to execute the script at your preferred schedule.

I use this command to run it every morning at 5 am.

0 4 * * * /scripts/mysqlbackup.sh