Using a cron job to backup your MySQL Database

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@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 sharutils

I have set up this cron job to run once a week. So far it works beautifully!

30 thoughts on “Using a cron job to backup your MySQL Database

  1. 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.

  2. 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.

  3. 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?

  4. 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

        • 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?

          Thanks

          rob

      • Also zu uns wird keiner koencm!Mimh hat es genervt das alle Heiligabend bei uns waren. Ich stand den ganzen Tag in der Küche und das war stress! Dazu immer ein extrem langer Tag!Im Letzten Jahr haben wir echt mal ganz alleine Weihnachten verbracht und die Omas ,Opas usw. erst nach Weihnachten besucht

  5. 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.

    • Hello there! I am brand new to following you, but already you're providing such inspiration for my own food blog and giving such delicious sound recipes I can't wait to try!Pumpkin in chili? Combining two amazing fall products in one? YES. I just hope Texas cools down enough to make it. Though by how delicious this sounds, it could still be in the hundreds and I would have to give this a chance.It also sounds pretty healthy! I can't wait to give it a shLontooki.g forward to reading more from you!

    • Most likely your server does not have uuencode installed. Please see the post above for help on installing uuencode.

          • Thanks for the reply. i have informed my provider and waiting for the process to be completed. Thanks for sharing the knowledge.

          • 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 add@example.com

            If none of these measures work I suggest you give up 🙂

  6. Hi,

    I’m wondering, what code can I use to replace mail and uuencode

    The hosting provider does not allow to use those 2 commands

    • Gerry,It looks like GTOMR found the site of Samcheok Museum site and it looks like they put the map on public view. “Could you please go and check the map when you have free time on a holiday or feeling like a short trip or hiking? I’m really interested in place names around Jukdo, especially. (by Yabutarou)”Is it far from where you liv?RGTOMe,Good Job !

Leave a Reply

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