Backup Cron

[Mark Brookes]

Hello, Technical guys ....

I have worked out how to do manual backup & restores of the database using phpMyAdmin <phew - hard stuff>

What I would really like now is a php file which would operate the backup process for me, and which I could schedule to do regular backups. Ideally this php would also download the resulting sql file to my local computer.
Somehow I would need to be able to tell the php file what backup settings I wanted (similar to the settings I use in phpMyAdmin(?)

Anyone any ideas? / able to help?

[ Jump to message #16 for a summary/case study ]

Regards
Mark

[John Turner]

Hi Mark,

Sorry for delay.
Here the solution. There are 2 files attached.
'../cron/backup.php'
'../updates/backup_cron.sql'

Upload all files to your hosting

Execute '../updates/backup_cron.sql' via Admin Panel » Manage Database » Import

Setup Cron tabs for '../cron/backup.php' do the same operation like with check.php

That's all. B)

[Mark Brookes]

Hey john,

Great Stuff ....

Feature Requests :)

* If I set "Make backup each [..] day(s)" = 0
Please can backup.php run 'NOW' when I execute it from the Browser address bar. ie no time delay since last backup.


* I Have found line 99 $backupfile .= 'db-'.date("Y-m-d").'.sql' ; Please can you tell me how to add the Time (hours & Mins) to this filename. (preferably GMT)


* Please would you explain a bit about "Archive Data (Zlib PHP requires)"? how do I know if my host has these archive propgrams/settings?


* Please would you explain a bit about "Use Complete Insert for tables"? When I experimented with this disabled I lost my category display data (no problem I had a previous backup stored :))


* Please could you add a setting in Cron-configuration for ... "How many Backups do you want to archive, before the oldest gets deleted"


* And ... is it possible for the program to FTP download to a location on my local computer? I guess I would need to enter the local folder into a new cron-configuration field.


Thanks
Mark

ps .. proceeding with tests :)

[John Turner]

Hi Mark,


* If I set "Make backup each [..] day(s)" = 0
Please can backup.php run 'NOW' when I execute it from the Browser address bar. ie no time delay since last backup.
Yes, you can, but don't forget to delete cron tab for backup.php


$backupfile .= 'db-'.date("Y-m-d").'.sql' ;

try to change

$backupfile .= 'db-'.date("Y-m-d_H-i").'.sql' ;



* Please would you explain a bit about "Archive Data (Zlib PHP requires)"? how do I know if my host has these archive propgrams/settings?

You can check this via server info.
But if you enable this option and script hasn't find zlib installed it isn't zip your sql dump.

* Please would you explain a bit about "Use Complete Insert for tables"? When I experimented with this disabled I lost my category display data (no problem I had a previous backup stored )

I will remove this option in next versions of backup-cron

* Please could you add a setting in Cron-configuration for ... "How many Backups do you want to archive, before the oldest gets deleted"

This implementation is difficult.

* And ... is it possible for the program to FTP download to a location on my local computer? I guess I would need to enter the local folder into a new cron-configuration field.


You need to install FTP-server on your local computer after that cron will be able to upload backups on it.
At present you need just upload backups from your directory (../backup)


Hope it helps.

[John Turner]

Here attached CRON backup V2 :cool-yo:

[Mark Brookes]

Hi Mark,
* cron_backup_last_time
Please can backup.php run 'NOW' when I execute it from the Browser address bar. ie no time delay since last backup.
Yes, you can, but don't forget to delete cron tab for backup.php


Hi John,

I have set the value to zero, but backup.php will not create the backup file unless I also use phpMyAdmin to delete the contents of Table=Config field = cron_backup_last_time

why do I need to delete this data via phpMyAdmin? I would prefer to only need to set the field "Make backup each [..] day(s)" = 0

regards
Mark

[Mark Brookes]

try to change
Code:
$backupfile .= 'db-'.date("Y-m-d_H-i").'.sql' ;



:1st:
I like try to change
Code:
$backupfile .= 'db-'.date("Y-m-d_H:i_T").'.sql' ;


can you suggest the symbol for 'seconds'?

Regards
Mark

[Mark Brookes]

Quote:
* Please would you explain a bit about "Use Complete Insert for tables"? When I experimented with this disabled I lost my category display data (no problem I had a previous backup stored )

I will remove this option in next versions of backup-cron

all right then, I don't know what it was trying to do so I can't say if it would have been a great idea or not :/

I did wonder if it was trying to overcome the problem of trrying to restore to an existing database with existing data - i.e. trying to add data to the tables withour changing or replacing the existing table structures?



Regards
Mark

[Mark Brookes]

Quote:
* Please would you explain a bit about "Archive Data (Zlib PHP requires)"? how do I know if my host has these archive propgrams/settings?

You can check this via server info.
But if you enable this option and script hasn't find zlib installed it isn't zip your sql dump.
:1st:

It creates a file type = gz, which I dont know about, but my WinZip program handles it with no trouble at all.

Thanks...

[Mark Brookes]

Quote:
* Please could you add a setting in Cron-configuration for ... "How many Backups do you want to archive, before the oldest gets deleted"

This implementation is difficult.

But worthwhile .... ?,
or,
Too difficult ...?


regards
<ark

[Mark Brookes]

Quote:
* And ... is it possible for the program to FTP download to a location on my local computer? I guess I would need to enter the local folder into a new cron-configuration field.


You need to install FTP-server on your local computer after that cron will be able to upload backups on it.
At present you need just upload backups from your directory (../backup)
By 'FTP Server' do you mean my SMARTFTP program?
if so, how would cron/backup.php use it to download the backup file onto my local computer?


(I do actually have an alternative - way to schedule FTP downloads which I could use for myself, but it seemed more elegant for backup.php to do it ?)


Regards
Mark

PS. Thanks for your development work on the backup issue.

[John Turner]

I have set the value to zero, but backup.php will not create the backup file unless I also use phpMyAdmin to delete the contents of Table=Config field = cron_backup_last_time

try to set -1

can you suggest the symbol for 'seconds'?

$backupfile .= 'db-'.date("Y-m-d_H:i:s_T").'.sql' ;


Too difficult your last question.

hope it helps.

[Mark Brookes]

try to set -1
WONDERFUL, and so elegant :) ... works perfectly




$backupfile .= 'db-'.date("Y-m-d_H:i:s_T").'.sql' ;

Wonderful too!



Too difficult your last question.

OK, B) (please remember the idea in case one day it becomes 'easier')

THANK YOU John for this great and useful addition to eSyndiCat.

I will proceed to testing it as a cron job (but I can't foresee any problems given the testing we have done via the browser)

hope it helps. Yes it does ... backing up the database was the one, serious, weakness/omission from my current Daily web-site backup process.

Regards
Mark

[John Turner]

You are welcome Mark.
Always at your services.

Also please check this my post http://www.esyndicat.com/forum/post57235-37.html

[Mark Brookes]

Hi John,

1.

I forgot to mention that the backup.php attached to message 5 gave an error on line 253.

By Deleting "}" on line 223 I appear to solve the problem.



2. backup.php Permissions
Please can you guide me about the appropriate permissions for this file. My FTP program defaulted to 644 and all my tests work with this. BUT does this mean anyone on the internet could run it? Do I need to change it to 744 or something else?

Thanks
Mark

[Mark Brookes]

OK then, a summary/casestudy of one possible backup strategy:-

[this mod developed on/for ver 2.0.01]


1. Install the new Backup.php programs
(see message 5 above)

* Download cron_backup v2.zip
* Un-zip files to local computer
* FTP upload
*** backup.php to {dir}/cron/backup.php
*** backup_cron.sql to {dir}/updates/backup_cron.sql
* Open Admin-Panel > configurations > Manage database
*** select Import
*** From 'please choose a file to import' select 'backup_cron'
*** select GO
(This has created several new fields in the table _config)


2. Configure
* Open Admin-Panel > configurations > Cron Job Configuration
*** View 'Backup Cron Settings'
*** MY Suggested Settings
-------- Backup cron functionality ---- enabled
-------- Archive Data (Zlib PHP requires) ---- enabled
-------- Make backup each [..] day(s) ---- -1
-------- Include tables structure ---- enabled
-------- Include DROP TABLE ---- enabled
-------- Include DATA ---- enabled

The default result is that a file named db-yyyy=mm-dd.sql (e.g. db-2007-01-30.sql) will be stored in the online folder {dir}/backups/

OOPS - CRASH - Problem on line 253.
Deleting "}" on line 223 seems to solve



3. Testing
By setting "Make backup each [..] day(s)" = -1 above (on my host at least) the backup.php program will run every time it is executed - ie there will be no forced-delay between executions.

Run from Browser:
* in your interner browser address bar execute the program
eg:
http://www.xxxxx.com/cron/backup.php

* Use your FTP program to view the {dir}/backup/ folder. You should see your new backup file.

* If you have set "Make backup each [..] day(s)" = -1" then each time you execute the program from the browser you should note (in your FTP program) that the Date/Time field shows the most recent execution. I.E that the file has been replaced by the most recent backup process.

* Now set "Make backup each [..] day(s)" = 0" or 1 or 2 etc and then each time you execute the program from the browser you should note (in your FTP program) that the Date/Time field does not change I.E that the file has NOT been replaced by the most recent backup process - because the Delay-since-last-backup has not been exceeded.

[Hmm. thinking about it I dont know if 0 means "never" or if it means "1"? ... still I'm not likely to use "0" in practice]




4 Customisation

* You can experiment with various enable/disable options


* Un-Dated File Names
~~~~~~~~~~~~~~~~~~~~~
The default file name is a dated file name - The disadvantage of this is that every time the backup.php program is executed a new file will be stored online. Eventually this may occupy an unwelcome amount of space.

If you change the file name so that it is the same every time the backup.php is executed there will only ever be one backup file - i.e. the latest backup.

To do this:-
*** Open backup.php in an editor
*** Find line 104
i.e.... $backupfile .= 'db-'.date("Y-m-d").'.sql' ;
*** modify to something like ... $backupfile .= 'MyEsc-db-'.'.sql' ;



* Time&Dated File Names
~~~~~~~~~~~~~~~~~~~~~~~
If you want more Date & Time details in your filename, try
$backupfile .= 'My-eSC-db-'.date("Y-m-d__H:i:s_T").'.sql' ;



5. Set up as Cron Job
Follow the principles set out in the eSC Knowledgebase article "cron jobs explained" http://www.esyndicat.com/support/desk/knowledgebase/view/3.html

e.g. /usr/local/bin/php -f /xxxx/xxxxx/xxx/wedding-resources/cron/backup.php


My choice is to do a daily backup, so I want

* cron to run daily,
minutes ... 0
hours ... 0=12AM
months ... every month
days ... every day
weekdays ... every weekday

* and for eSC cron>configuration delay-between-backups to be no greater than 1 day
Make backup each [..] day(s) = 1

I like these settings because once every day at midnight CRON will execute the backup.php program. The first thing it will do is to check if the delay since last backup is exceeded.
SO ... setting Make backup each [..] day(s) = 1 means that I get a backup each day
SO ... setting Make backup each [..] day(s) = 7 means that I get a backup each week
SO ... setting Make backup each [..] day(s) = 31 means that I get a backup each month
AND therefore I am able to play with the frequency of backups without having to tinker with the cron jobs again.


6. Restoring a backup
This is a whole extra issue with its options and pitfalls.
I have only tried restoring via phpMyAdmin.

If you want to see how, you may be interested in my casestudy "About Backup & Restore" at http://www.finespeeches.com/_other_/eSC_intro.html




Regards
Mark

[John Turner]

Hi Mark,

By Deleting "}" on line 223 I appear to solve the problem.
Ooops you are right.

[Mark Brookes]

Hi Mark,


Ooops you are right.

:) no problem

any advice on proper permissions for backup.php?

[John Turner]

Hi Mark,

Permission should be set to 644.
(the same of check.php)

[woeanl]

Hmm i guess i'm out of the newbie-zone here...

So downing cron_backup.zip to myComputer and do:

* Download cron_backup v2.zip
* Un-zip files to local computer
* FTP upload
*** backup.php to {dir}/cron/backup.php
*** backup_cron.sql to {dir}/updates/backup_cron.sql
* Open Admin-Panel > configurations > Manage database
*** select Import
*** From 'please choose a file to import' select 'backup_cron'
*** select GO
(This has created several new fields in the table _config)


* Configure
* Open Admin-Panel > configurations > Cron Job Configuration
*** View 'Backup Cron Settings'
*** MY Suggested Settings
-------- Backup cron functionality ---- enabled
-------- Archive Data (Zlib PHP requires) ---- enabled
-------- Make backup each [..] day(s) ---- -1
-------- Include tables structure ---- enabled
-------- Include DROP TABLE ---- enabled
-------- Include DATA ---- enabled

Then?
Still have to manually change code with PRO 2.0 / 2.1 (2.2)?

(if everything works there are new options but without cron_backup.zip installed you don't have those options?)

[John Turner]

Hi woeanl,

Which version of eSyndiCat do you use?
If V2.1, backup-cron already included in the version.

So, after you have tuned up your backup-cron you should setup your cron - tabs via your CPanel.

Hope it helps.
If you'll have question please feel free to contact me.

[woeanl]

So, after you have tuned up your backup-cron you should setup your cron...
Hello John,
What exactly do you mean with "tuned up" and in what order i have to do this? I mean first admin area settings then cPanel?

[John Turner]

Hi woeanl,

Sorry for delay.
You should enable next option via Admin Panel » Configuration » Cronjob Configuration

Backup cron functionality enabled
Archive Data (Zlib PHP requires) enabled
Make backup each [..] day(s) 30
Include tables structure enabled
Include DROP TABLE enabled
Include DATA enabled

Thats all.

[newbiz]

hello
when i run .....cron/backup.php
nothing add in backup directory ?

and in cronjob we must to set command in the {} or not ?
because i receive this mail :
/bin/sh: {/usr/local/bin/php}: No such file or directory

thank you