Automatic MySQL Backups

After setting up several sites with a linux based shared hosting provider I felt the need to add a little more reliability and regularity to the backup process that was in place. The hosting provider simply ran a full disk backup once a week. Knowing that my data could potentially be lost after 6 days and 23 hours of work did not give me what you might call a warm fuzzy feeling. Because of that I set out to design a more frequent backup procedure that I could setup once and be done with. Here’s what I did…

First, I created a simple script called dbbackup to backup and compress my database:

1. #!/usr/bin/bash
2. mysqldump -uusername -ppassword yourdbname > yourdbname.sql
3. tar -zcvf sqldata.tgz *.sql
4.  
5. perl /home/emaxx/sqlbk/dbmail.cgi
6. Download this code: /code/dbbackup.txt

If you have multiple databases you can repeat line 2 as many times as necessary. Line 3 will simply gzip any .sql files in the directory. You can find full documentation on the mysqldump command on the MySQL.com wesbite.

Next I created another script called dbmail.cgi to email my gzipped mysqldump results to an address of my choice. In order for this to work, you must have MIME::Lite installed and available to you. Just ask your hosting provider to enable it if it isn’t already. Now on to the email script:

1. #!/usr/bin/perl -w
2. use MIME::Lite;
3.  
4. $msg = MIME::Lite->new(
5. From => 'some@address.com',
6. To => 'you@address.com',
7. Subject => 'your db backup',
8. Type => 'text/plain',
9. Data => "Attached you will find your database backups for today.");
10. $msg->attach(Type=>'application/x-tar',
11. Path =>"sqlbackup.tgz",
12. Filename =>"sqlbackup.tgz");
13.  
14. $msg->send;
15. Download this code: /code/dbmail.txt

All you’ll have to do is replace the From and To addresses in lines 6 and 7 and you’re set. If you’d like you could always send to multiple addresses or customize the text in your message.

Now that these two scripts are complete and in place, verify that they work by running the dbbackup script from the shell. If you don’t have shell access to your account, either get it, or skip this step and read on.

Finally, I setup a cron job to run the dbbackup script on a schedule. I schedule mine to run nightly at somwehere between the hours 3am and 5am EST when I expect there to be minimal (if any) traffic on my sites. Here’s the setup for one of my cron jobs that runs nightly at 3am:

1. 0 3 * * * /full/path/to/dbbackup

That should about do it. You’ll be greeted each morning with an email containing your latest database that evokes feelings both warm and fuzzy.

Comments

Add a comment

You may use textile in your comment. Gravatars are enabled. Your email will not be displayed and will remain private. I reserve the right to edit or delete comments.

Name E-mail Website Remember

Message