Skip to content

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

1 thought on “Shell script to backup all mysql databases and upload via FTP”

  1. Hello Nitesh,

    We can also use command “tar -cvf” to compress DB backup which will reduce size of the backup file.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

x  Powerful Protection for WordPress, from Shield Security
This Site Is Protected By
Shield Security