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.
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
One thought on “Shell script to backup all mysql databases and upload via FTP”
We can also use command “tar -cvf” to compress DB backup which will reduce size of the backup file.