cron job to send a database backup to gmail account

[php_rules]

I have this setup on one of my servers. It is a lifesaver.
First thing to do is to make a bash script.
In the bash script was the following code:

#!/bin/sh
######
# first make a mysqldump of all databases, and tar and zip it
######
mysqldump --add-drop-table -u user_name -p password --all-databases | tar -vxzf > backup.sql.tar.gz
######
# next email it
# The echo at the start of the last line adds a blank into the body of the 
# mail being set out.
######
echo | mutt -a backup.sql.tar.gz -s “daily backup of database” email@gmail.com
####
# finally, delete the backup from your server
####
rm backup.sql.gz

Secondly chmod +x the script to make it executable,
next, just add a daily cron job to call the script and that's it.

[php_rules]

Just to add that you can also put the following line into the script to have it backup your directory structure and mail it to your gmail too

####
# backup the directory structure and send it in a separate email
####
tar -vxzf backup_dir.tar.gz /home/mysite/public_html

#####
# send a second email with the data structure as attachment
#####
echo | mutt -a backup_dir.tar.gz -s “daily backup of directory data” address@gmail.com

If you want one single email then you need to use the following line for the sending email part

####
# send a single email with both attachments
####
echo | mutt -a backup_dir.tar.gz -a backup.sql.gz -s “daily backup of directory data and database” address@gmail.com

also, remember to add an

####
# delete the data backup
####
rm backup_dir.tar.gz

at the bottom to remove the temp data.

[carl owen]

Thank you for this information. It is certainly a very good procedure as you will be able to schedule the database backup according to you. The updates of the database will be sent on your email address as per the cron job settings. If in case, the server fails and your database is lost you can recover the database as you will have it on your email address but the cron job settings plays a very important role in this and you should do it carefully.

[php_rules]

doh!! I just re-read this post - I put

tar -vxzf

that is to untar =|
it should read

tar -cvzf

to create the tar files!!

[Frankie]

So this backs up to your gmail email account? How do you then access it, to get a copy back, to use as a back up?

I'm just curious as to what format this works as-does it send as a file attachment?

[Skipper]

This looks like a neat little script. Like Frankie, I'm curious as to whether the backup file will be sent as an attachment or as the body of the email. Either way, good work!

[carl owen]

Originally Posted by Frankie

So this backs up to your gmail email account? How do you then access it, to get a copy back, to use as a back up?

I'm just curious as to what format this works as-does it send as a file attachment?

Yes, the backup is sent to your Gmail account. The backup is sent as an attachment in tar.gz format which can be used if required.

Originally Posted by Skipper

I'm curious as to whether the backup file will be sent as an attachment or as the body of the email. Either way, good work!

The backup will be sent as an attachment in tar.gz format.

Hope I have answered all the questions

[Skipper]

Yep, that's the only question I had. I am wondering if anyone here can verify that this works (someone who has tried it themselves and had good results) before I set this up with my databases.

[carl owen]

Originally Posted by Skipper

Yep, that's the only question I had. I am wondering if anyone here can verify that this works (someone who has tried it themselves and had good results) before I set this up with my databases.

Well, we do have customers who have tried this and it has worked for them but I doubt if they will share their experience here ;)

You can try this and see if it works for you.

All the best!!