Cron Job for Backup DB

Hangin' Around Joined: Aug 11, 2005 Posts: 26

Just a general question really im wondering if anyone knows a cron job to bacl up my database so i dont have to keep doing it manually?

Thanks in advance

Tom

Posted: Tue Aug 23, 2005 11:16 pm

Code:

<?php 





set_time_limit( 1200 );


$host="localhost"; // database host address


$dbuser=""; // database user name


$dbpswd=""; // database password


$mysqldb=""; // name of database


$day = (date("y-m-d"));


$path1 = "/home/YOURUSER/dumps"; // path to your backups storage MUST be CHMOD 777


$filename1 = "YOURDB_".$day.".sql";


$filename2 = "YOURDB_".$day.".sql.gz";


$old_path1 = getcwd();


chdir($path1);


if( file_exists($filename1) ) { unlink($filename1); }


if( file_exists($filename2) ) { unlink($filename2); }


system( "/usr/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$dbpswd --host=$host $mysqldb > $filename1");


system( "tar -czf $filename2 $filename1");


unlink($filename1);


chdir($old_path1);


echo"SQL Backup Completed";





?>

Save this script as something like backup_sql.php and upload it into a protected directory somewhere in your web accessable directory structure.

The cron call will be something like:
GET [ Only registered users can see links on this board! Get registered or login! ] > /dev/null

Posted: Tue Aug 23, 2005 11:56 pm

Thanks mate ill give it ago, may i ask what exactly does this back up as when i do a backup on the website it backs up 40 odd MB and when running this quickly it backs up 7mb???

Thanks

Posted: Wed Aug 24, 2005 12:17 am

That script backs up only your database.

This script:

Code:

<?php 





set_time_limit( 1200 );


$day = (date("y-m-d"));


$path1 = "/home/YOURUSER"; // path to your backups storage


$path2 = "public_html/"; // path to your web root


$filename1 = "YOURSITE_".$day.".tar.gz";


$old_path1 = getcwd();


chdir($path1);


if( file_exists("dumps/".$filename1) ) { unlink("dumps/".$filename1); }


system( "tar -czf dumps/$filename1 $path2");


chdir($old_path1);


echo"Site Backup Completed";





?>

backups up your site assuming your public directory is public_html. Some servers have it as public, some as www, and many others Smile

Posted: Wed Aug 24, 2005 1:44 am

No thats all i want is my DB backed up, sorry when i unzipped the tar file it was 38 mb big so that is about the same as my normal backup.

Thanks for your help mate now time to get that cron working lol!

Posted: Wed Aug 24, 2005 3:24 am

okay i run the cron job -

GET [ Only registered users can see links on this board! Get registered or login! ] > /dev/null

and i get the error

<HTML>
<HEAD><TITLE>An Error Occurred</TITLE></HEAD>
<BODY>
<H1>An Error Occurred</H1>
500 Internal Server Error
</BODY>
</HTML>

Regular Joined: Jun 15, 2005 Posts: 78 Location: AR

what is the cron for the site backup. Would it be the same concept as the db backup?

Posted: Wed Aug 24, 2005 11:56 am

OK! I just set this up, and it was Really Easy to do.

- First off make a new folder in the root of your site...
- Next go into cpanel, or whatever control panel you have, and secure the directory.
*** In cPanel it would be "Password Protect Directories".
- Set this up with a UserName & Password.
- Create the file "backup_sql.php" with the code that Bob provided above.
- Edit the info to what it needs to be to fit your site.
- Drop backup_sql.php in the secured folder via [ Only registered users can see links on this board! Get registered or login! ]
- Go back into cPanel, and go to "Cron jobs".
- Choose "Standard".
- Now in here you will see a bunch of settings that will allow you to select how often
you want this cron job to backup your DB. I would do a daily backup or maybe a
weekly backup depending upon the size of your DB and the ammount of webspace
that you have. In order to do this see where it says "Day(s)"? Select how
frequent you want this to run 1 = every day, and 7 = every week.
- The username and password on the cron tab is the same username and password that
you used to setup the secure directory.

You will have to clean the folders up. Delete the old backups after you have
downloaded them to your PC. This way your site doesn't become loaded with
stuff that you don't need.

This is really nice for me. Cause I have started an archive of my site. With
daily backups of my DB and weekly backups of the files on my site.

Now that you know how to do the SQL backup do the same thing for the site backup.

Posted: Wed Aug 24, 2005 11:57 am

Bob do you mind if i post this on my site as a full pledge tutorial?

Posted: Wed Aug 24, 2005 12:43 pm

You have my full and complete permission

Posted: Wed Aug 24, 2005 12:46 pm

Thank you.

Posted: Wed Aug 24, 2005 12:48 pm

Thanks guys, this is some extremely useful information.

Posted: Wed Aug 24, 2005 1:09 pm

Xiode wrote:

what is the cron for the site backup. Would it be the same concept as the db backup?

Yes, same concept.

Posted: Wed Aug 24, 2005 3:25 pm

You can find the full tutorials for this at.
[ Only registered users can see links on this board! Get registered or login! ]

Posted: Wed Aug 24, 2005 3:31 pm

Good job.

Great tutorial!

Don't you just love Mighty_Y's tutorial module? It is so much better than the KB mod.

Posted: Wed Aug 24, 2005 3:34 pm

Making the DB backup one now.

Posted: Wed Aug 24, 2005 9:46 pm

Quote:

Before I say anything. You must understand that this site, anyone associated with it, Nukescripts.net, or anyone associated with that site are responsible for any lost data, damages, or anything else. You take complete responsiblity when using this. Use at your own risk.

Not to be picky but I think you forgot the word NOT in are responsible from both tutorials. Other then that one word they look really good Smile

Posted: Wed Aug 24, 2005 10:05 pm

LMAO.... WOW. I can't believe i missed that.I probably read over it 3 times.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Posted: Thu Aug 25, 2005 4:06 am

OOPS, lol

Good Tut though mate, nice!

Posted: Thu Aug 25, 2005 6:36 am

For those who don't seem to be able to use "GET" is there an alternative on a Linux machine?

Posted: Thu Aug 25, 2005 10:01 pm

Save the following code in /home/YOURNAME/sqlbackup.sh

Code:

#! /bin/sh


FILE1=sqlbackup-`date +%Y%m%d%H%M`.sql


DIR=/home/YOURNAME/


mysqldump -f -hlocalhost -u<loginname> -p<password> <dbname> --add-drop-table | gzip -9c >${DIR}${FILE1}.gz

Then run thru SSH or Cron

Code:

sh /home/YOURNAME/sqlbackup.sh

Posted: Thu Aug 25, 2005 10:03 pm

djmaze,

You are a scholar and a gentleman! Thank you!

montego

Posted: Mon Aug 29, 2005 5:55 am

your welcome

Posted: Wed Aug 31, 2005 11:53 pm

If you have access to cron but not telnet or ssh and cron doesnt like the GET function you could also try

Code:

php --quiet /home/yourpath/public_html/yourdirectory/filenamephp