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. Now I know I can use the serendipity backup plugin to do this but it only backups up the database to a directory on the server. IMO this does not serve much purpose since 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 -ce --user=username --password=password dbname | gzip | uuencode dbbackup_e.gz | mail email@address.com

Ok basically what this does is dump the database using mysql. This dump is a simple text file with a series of sql commands that can used to restore the database using phpmyadmin. The options --ce are used to create a dump with extended inserts which results in a smaller dump. It is piped to gzip for compression and then uuencoded for transmission via email. Finally it is mailed to me. I have set this up to run once a week. So far it works beautifully!

٠١ شعبان ١٤٢٧

25 Aug 2006

Comments (6)

Comments

Jackie

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.

Saturday, April 21. 2007 -

٠٤ ربيع الآخر ١٤٢٨

Ernesto

Your explanation is clear and works well, thank you for your script and I wait continue forward with your blog.

Thank you again.

Wednesday, May 30. 2007 -

١٤ جمادى الأول ١٤٢٨

Rupak

Does it holds good with large size database.

Friday, October 3. 2008 -

٠٣ شوّال ١٤٢٩

Abdussamad Abdurrazzaq

3.1

Yes it will work with any size database

Friday, October 3. 2008 -

٠٣ شوّال ١٤٢٩

Kenny

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

Monday, October 13. 2008 -

١٣ شوّال ١٤٢٩

Abdussamad Abdurrazzaq

4.1

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.

Monday, October 13. 2008 -

١٣ شوّال ١٤٢٩

Add Comment

Name

Homepage

In reply to

Comment

Standard emoticons like :-) and ;-) are converted to images.

E-Mail addresses will not be displayed and will only be used for E-Mail notifications

To prevent automated bots from comment spamming, please enter the letters or numbers you see in the image below in the appropriate input box.
CAPTCHA

Please type what you see in the above image:

Remember Information?
Subscribe to this entry

Using a cron job to backup your MySQL Database

Abdussamad.com

Using a cron job to backup your MySQL Database