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.
FTPHOSTNAME=”ip and port”
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
#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