DIY Linux Web Server HACK - Automate your MySQL MySQL database backups | zedomax.com - Obsessively profiling DIYs, Hacks, Gadgets, Tech, Web2.0,and beyond.

Posted in Blog, Cool, DIY Zedomax, DoItYourself!, Educational, Hack, Linux, Ubuntu, Web, Wordpress by max on the February 10th, 2008

Okay, I’ve been running dedicated servers for couple years now and I used to mess around with Linux boxes back in college.

Here’s an essential automatic script and a cron job you will need in order to keep your backup automatic.

Yes, if you get a dedicated server, don’t get their stupid extra $15/month for backing up your database. (All they do is make scripts like this one and charge you for it)

You can follow this example and go spend that money on junk food.

So all you have to do is FTP your backup files.

This is way better than going into your phpMyAdmin and downloading from the web interface since the file is already ready for you to download.

Here’s how you do it:

1) Make a new file called backup.sh in your home directory. 2) Use the following syntax:

mysqldump -u[USERNAME] -p[PASSWORD] –opt [DATABASE NAME] > /root/backup/DATABASE1.sql

Here’s an example backup.sh file if you had 2 databases:

mysqldump -uUser1 -pPassword1 --opt Database1 > /root/backup/Database1.sql
mysqldump -uUser2 -pPassword2 --opt Database2 > /root/backup/Database2.sql
tar cvf /root/backup/sqlbackup.tar /root/backup/*

You can test your script by typing this:

sh backup.sh

(The mysqldump will overwrite the older file if you run this more than once)

3) If everything is working good, now you can put that script to be run once a day in your cron job.

For Fedora Core, you can edit this file /etc/cron.d/sa-update

Add the following line:

10 3 * * * root sh /root/backup/backup.sh

This will execute the script once a day at 3:10AM.

Now simply download one tar ball file whenever you feel like backing up your database.

You can probably change this a little bit and use it for many other things like backing up all your files too. (which I do)

If you have multiple servers like me, you can also automate your scripts to download backup databases to each server. That keeps things real redundant in case of server failure.

If you like this post then please subscribe to my full feed RSS. You can also subscribe by Email.

Bookmark It!

|Digg it |SiteHoppin |Wagg It |

TrackBack

Email this to a Friend

RSS feed

4 Comments »

Comment by Mike

2008-02-11 02:24:34

Very useful script. Thanks.

http://packratstudios.com

Reply to this comment

Comment by max

2008-02-11 07:15:11

glad it helped!

Reply to this comment

Comment by Brian Gaut Subscribed to comments via email

2008-02-11 05:22:04

To further automate the process of backing up, you should set up a LOCAL cron job that downloads the file to your computer.

Which is just as easy…. all ya do fetch the remote file:

wget ftp://Username:Password@www.yoursite.com/backup/sqlbackup.tar

Reply to this comment

Comment by max

2008-02-11 07:14:54

true, that’s if you are using Linux…wait a minute, why am i not using linux!?!

Reply to this comment