A 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@example.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 sharutils
I have set up this cron job to run once a week. So far it works beautifully!
25 thoughts on “Using a cron job to backup your MySQL Database”
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.
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 firstname.lastname@example.org
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?
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.
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 firstname.lastname@example.org
Credit goes to kitsi80 on ubuntu forums:
That does it. Awesome. Thanks for your help, and for this page!
I have moved to Mac mail, and now the database backup is in the message body, and not actually attached as a zip like it used to be in Outlook.
Any thoughts on something other than uuencode (which I believe is the issue from my research) that will work with this method?
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.
Thanks for the code. but the size of attached file is 0 kb. what will be the problem? pls suggest me
Most likely your server does not have uuencode installed. Please see the post above for help on installing uuencode.
Thanks for the info. let me check with my provider. is there any other alternate options? pls let me know
No you need uuencode if you want to receive MySQL backups via email.
Thanks for the reply. i have informed my provider and waiting for the process to be completed. Thanks for sharing the knowledge.
installed uuencode in server but still attached backup is 0kb file. what will be the problem? pls advice
Try these measures:
– Check the DB password and username to make sure it is correct.
– Ask your hosting provider for the absolute path to mysqldump and use that instead. For example mysqldump is installed in /usr/bin/mysqldump. Use that in the cron job instead of just mysqldump
– Try to pipe the output of mysqldump directly to mail. For example:
/usr/bin/mysqldump -e –user=username –password=password dbname | mail email@example.com
If none of these measures work I suggest you give up 🙂
I’m wondering, what code can I use to replace mail and uuencode
The hosting provider does not allow to use those 2 commands