Using a cron job to backup your MySQL Database
August 25th, 2006A cron job is a command that will run at a scheduled time. My website host allows me to set up cron jobs through the CPanel interface. Using a cron job I can automate MySQL database backups. Now a backup should always be placed on a different system to the one being backed up. I think a simple approach is to email myself the backup. So I use a cron job to backup my database. Its a series of piped commands.
mysqldump -e --user=username --password=password dbname | gzip | uuencode dbbackup_e.gz | mail email@address.com
Ok basically what this does is dump the database using mysqldump. This dump is a simple text file with a series of SQL commands that can used to restore the database using phpmyadmin. The option -e is used to create a dump with extended inserts which results in a smaller file. It is piped to gzip for compression and then uuencoded for transmission via email.
If the above command doesn’t work for you then you will need to install uuencode. If you have root access you can do so with the following command on CentOS or ask your hosting provider to do it for you:
yum install sharutilsI have set up this cron job to run once a week. So far it works beautifully!
Thank you! This is the first time I have found how to do a cron job which will email me the results. Always seemed really silly to have it back-up on the same server. If that crashes you loose your back-up too! Now it emails me. Thanks again.
Your explanation is clear and works well, thank you for your script and I wait continue forward with your blog.
Thank you again.
Does it holds good with large size database.
Yes it will work with any size database
Hey, this is the first set of info that has gotten me even remotley close to my target of backing up the database and emailing it to myself, the only problem is, it sends a file that is only 25KB, and has no data inside the GZip file
my Cron Deamon says
mysqldump : not found
when i export the same database through phpMyAdmin the file is close to 13 megs. Am i doing something incorrect? for the user do i use the MySql Database user? or my Cpanel login user? i;ve tried both, but somehting just oesnt seem to work.
thank you
please feel free to Email me if you can help me at at
slayer at idolcreations dot com
Try using /usr/bin/mysqldump instead of just mysqldump. Very strange that mysqldump is reported to not exist. You could also ask your webhost for assistance with this.
This works great, thanks. Since I save backups for several weeks at a time (just in case), I wonder if there’s a way to add the date of the backup to the file…so when I save it to a folder, I can just save it…instead of having to rename it.
Any thoughts on how to do that?
Yes try this command instead:
mysqldump -ce –user=username –password=password dbname | gzip | uuencode dbbackup_e-$(date +%Y-%m-%d).gz | mail email@address.com
Just add $(date +%Y-%m-%d) where you want the date to appear in the filename.
I used that very command, of course replacing the database, username, password and email address, and it never gets sent.
I can’t help but wonder if there’s something with my host…
Is there any other alternative that I can try?
Thanks
rob
Back when this post was first written uuencode used to be included by default on most linux distributions. Nowadays it isn’t. Ask your hosting provider to install uuencode. Its usually found in the sharutils package.
I thought I would update – I got this working:
mysqldump -ce –user=username –password=password dbname | gzip | uuencode filename_e-$(date +\%Y-\%m-\%d).gz | mail email_address
But now I have one more question. How can I add a subject to the email that is sent?
Add the -s switch to the mail command:
mail -s “subject” email@example.com
The function works…but it doesn’t the moment I try to add the date part.
uuencode must be installed, since it does email me my jobs. But like I said, as soon as I try to include the date with your code (and others I’ve found on the ‘net), it just doesnt’ work.
Thanks
rob
Rob to make it work in a cron job you have to escape the percent signs with a backslash character. The revised command:
mysqldump -ce –user=username –password=password dbname | gzip | uuencode dbbackup_e-$(date +\%Y-\%m-\%d).gz | mail email@address.com
Credit goes to kitsi80 on ubuntu forums:
http://ubuntuforums.org/showpost.php?p=4329469&postcount=6
That does it. Awesome. Thanks for your help, and for this page!
rob
Hi. Great post, I just found it and it works for me perfectly with one hitch. I’m backing up a MySql database named Library. The file I get emailed is named Email Attachment. It doesn’t rename to the file name I put in after uuencode, I think I’m missing a step but cannot figure out what it is. Any help would be greatly appreciated. Thanks.