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!

25 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

  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.

    • 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

Leave a Reply

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