Backup db with Cron Job?

[A. Sanny]

Hello Heliohost team! Please I need some help here. I googled for auto backup my forum db and found a cron job script. I'm not that know-it-all about cron jobs nor Linux commands, but I do understand your restrictive policies for cron jobs and respect them. Therefore I want any advice on this:

 

Using (cPanel/Cron Jobs), set those fields to the following:

-----------

Minute: 0

Hour: 19

Day: *

Month: *

Weekday: 0

Command: mysqldump --opt -Q -u dbusername --password=dbpassword dbname | gzip > /pathto/backups/sunday.gz

----------

Replacing dbusername to your database user.

Replacing dbpassword to your database password.

Replacing dbname to your database name.

Alter /pathto/backups/ to your favourite path, far outside from public_html.

 

Repeat this job six times changing 'Weekday' from 0 to 1, 2... 6 and file name from 'sunday.sql' to 'monday.sql'... 'saturday.sql'. This will give you seven backup files for everyday.

Quick summary from: http://www.theadminzone.com/forums/showthread.php?t=17548

 

Will, I think this method does not cross any policies here; It's only seven different cron jobs that run each day of the week, but have no idea about eating resources. Note that I chose 19 for 'Hour' for what I think it's the time when few users would use the forum (My time zone is PST-10). Also I found someone (from that link) recommend using Gzip format to minimise backup folder size.

 

One more thing, how do I restore these backup if something went wrong? Is it from (cPanel/backup)?

 

So, what do you think? I haven't tried this nor I have tried cron jobs before. To backup I was doing the old unproductive way by exporting through phpMyAdmin every week. Sure I want my life much easier with automatic jobs, but unfortunately I lack experience.

 

UPDATE:

Just want to thank you for your help on my last topic. Stevie provides all what I like, most important his server-load; much lower than Johnny's! (3.55 against 29.47 ??? thank god I moved to him!)

[jje]

Have you read this thread?:

 

http://www.helionet.org/index/index.php?showtopic=9375

[A. Sanny]

Hello jje,

 

Actually I read some php-script topics for backups when I searched, but preferred to choose the simplest way. Getting emails everyday attached with backups sounds efficient, but consumes too much storage (by the end of the month, my inbox will be filled with 30 backups).

 

Anyway, if I want to put this script on schedule, what command to be written in cron job to execute it, let's say, everyday on 1900 PST? Sorry if I'm mixing things here but my knowledge in administrating websites is somehow limited, but I'm always ready to learn!

 

I like the way of making 7 different backup files weekly; each day has its own backup file. For example, on Sunday a cron job makes a new backup replacing the last Sunday backup. Sad that I can't make that

[Byron]

If you don't want that script to email you the backup and instead write it to a directory, replace the email part with this:

 

$day = date("l");
file_put_contents("$day.$fname", $result);

 

And have a cron job run it every day.

 

I can't remember the exact setting for every day, but the path will look something like this:

 

php -q /home1/username/public_html/cron_job.php

 

Here's the entire code that needs editing for your domain and username and password and dtabase info. It will over write each day every 7 days (Sunday, Monday, Tuesday, etc).

 

<?php
# Edit cPanel backup info 
# login is cPanel login username and password
$user_name = "byron";
$user_pass = "********";
$database = "byron_test";
$domain = "byron.heliohost.org";
$url = "http://$domain:2082/getsqlbackup/$database.sql.gz";
$agent = "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)";
$cook_file = "curl_login_cookie.txt";

# get file extension
$extract = pathinfo($url);
$fname = ($extract['basename']);

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, false);
curl_setopt($ch, CURLOPT_REFERER, "http://$domain");
curl_setopt($ch, CURLOPT_USERAGENT, $agent); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_UNRESTRICTED_AUTH, true);
curl_setopt($ch, CURLOPT_USERPWD, "$user_name:$user_pass");
curl_setopt($ch, CURLOPT_COOKIEFILE, $cook_file);
curl_setopt($ch, CURLOPT_COOKIEJAR, $cook_file);

$result = curl_exec($ch);
$extract = curl_getinfo($ch);
$httpcode = $extract['http_code'];
curl_close($ch);

if ($httpcode >= 200 && $httpcode < 303) {

# write new file
$day = date("l");
file_put_contents("$day.$fname", $result);

} else {
echo "cPanel Login Failed!";
}

# delete cookie file
if (file_exists($cook_file)) {
unlink($cook_file);
}
?>

 

 

 

Here's what the setting would look like for 1900 hours

 

00 19 * * *

php -q /home1/username/public_html/cron.php >/dev/null 2>&1

 

This part keeps cron from sending you an email each time it runs:

 

>/dev/null 2>&1

 

 

[A. Sanny]

Thanks for instant reply, byron.

 

I read this script trying to understand somehow its mechanism but I'm going to try it anyway with a test database. I have some questions in mind:

 

1. Do I have to save this script under public_html directory? Sorry but isn't this somewhat risky?

2. Where will be the backup file stored? Is it gzipped also?

3. How am I going to restore this backup? Via (cPanel/backup)?

 

Many thank you all for your efforts.

[Byron]

If you go here you'll see where I'm grabbing the gzipped backup file and where it gets restored also.

 

http://stevie.heliohost.org:2082/frontend/...ckup/index.html

 

You might want to set it up some where in your public_html directory at first just for testing so you can run the script without using a cron. Then once you've got it like you want it, move it to your home directory and call it from there with a cron job. The backup file will be a gzipped file stored in the same directory as the script is run in.

 

Also don't forget that the username and password are your cpanel login and not your database user and pass.

 

[A. Sanny]

byron, thank you very much! It worked with a test database successfully (both by manual triggering or using cron job). By the way, does Hotmail block incoming cron notification? I pulled out that last part from the command you wrote, a backup was made but no email was sent.

 

Thank you again!

[Krydos]

byron, thank you very much! It worked with a test database successfully (both by manual triggering or using cron job). By the way, does Hotmail block incoming cron notification? I pulled out that last part from the command you wrote, a backup was made but no email was sent.

Some people have problems receiving email from Heliohost to their Hotmail accounts. Personally, I created my account with a Hotmail account and still use it and I get all my emails, but enough people have reported problems that you can't even create accounts with @hotmail.com addresses anymore. Your problem probably lies with Hotmail being email nazis. Try sending it to another address and it probably will work.

[Byron]

I pulled out that last part from the command you wrote, a backup was made but no email was sent.

 

If the script isn't generating any kind of html output then cron doesn't send an email. Look for this in the script:

 

# write new file
$day = date("l");
file_put_contents("$day.$fname", $result);

 

and add this below it:

 

echo "Backup File Successful";

 

So then it looks like

 

# write new file
$day = date("l");
file_put_contents("$day.$fname", $result);
echo "Backup File Successful";

 

Then try it with Hotmail or another email service.

 

 

[A. Sanny]

Thank you Krydos and byron for passing by. byron's edit did the job and an email was sent to my Hotmail account, specifically the Junk folder. Thank you all Heliohost team again!

[jje]

Glad your problem got solved