Sprayfly

With very few of the BASH scripts out on the internet fitting my specific requirements, I decided to write my own shell BASH script to make email backups of MySQL databases.

Here is what I wanted the script to do:

• First check whether or not the day is Sunday (or another day of the week)
• If the day is not Sunday, end the script
• If the day is Sunday, export and compress all tables and contents of a specified MySQL database to a temporary file.
• Email this file as an attachment along with an automatically generated subject and message body to a chosen email address. Provide the ability to use two different UNIX mailing tools - to provide for different server setups.
• Delete all the temporary files generated

Instead of backing up the entire database (name, etc.) I prefer instead to backup all the database’s tables and contents. This means that when you want to restore data, you can simply create a new database in PHPMyAdmin and then import the dump file into this. The new database can even have a different name to the old one. Essentially, its just the same as backing up the database but this solution is more flexible.

Configuration

You will need SSH access to your server to be able to do the following.
I schedule the script as a CRON job on my host’s webserver. This runs multiple times every day. However, a backup is only made on Sundays although this day of the week can easily be changed. You need to upload the script to a directory below public_html, rename it and give it execute permissions:

move jonosql2email.txt jonosql2email.sh
chmod +x jonosql2email.sh

You need to edit the script with your preferred text editor. The only values that need changing are:

fullbackup="Sun" # The day of the backup (Mon Tue Wed etc)

email="email@address.com" # The Email Address you want to send the backup to

dbhost="localhost" # the database server (normally localhost)
dbuser="db-username" # the database user name
dbpass="password" # the database password
dbname="db-name" # the name of the database

All you need to do now is run the script (either manually or via CRON) and you should get an email to the specified address with the backup file attached:

In an SSH Shell enter the following:
If you are in the directory within which lies the script-
./jonosql2email.sh

If you want to give an absolute path (this depends on your server setup!)-
/home/sites/example.com/jonosql2email.sh

Download the Script

jonosql2email

Hope this is useful info! Please give me some feedback on the script if you use it.