Cron Job for Backup DB

topmug · 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

BobMarion · 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 to the forums!

> /dev/null

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

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

BobMarion · 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

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

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!

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

okay i run the cron job -

GET

Only registered users can see links on this board!
Get registered or login to the forums!

> /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>

Xiode · 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?

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

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 to the forums!

- 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.

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

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

BobMarion · Posted: Wed Aug 24, 2005 12:43 pm

You have my full and complete permission

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

Thank you.

CurtisH · Posted: Wed Aug 24, 2005 12:48 pm

Thanks guys, this is some extremely useful information.

BobMarion · 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.

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

You can find the full tutorials for this at.

Only registered users can see links on this board!
Get registered or login to the forums!

CurtisH · 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.

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

Making the DB backup one now.

BobMarion · 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

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

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

Raven · Posted: Thu Aug 25, 2005 3:35 am

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

OOPS, lol

Good Tut though mate, nice!

montego · 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?

djmaze · 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

montego · Posted: Thu Aug 25, 2005 10:03 pm

djmaze,

You are a scholar and a gentleman! Thank you!

montego

djmaze · Posted: Mon Aug 29, 2005 5:55 am

your welcome

Guardian2003 · 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