Argh help me! lol - phpbb backing up database via cron

PoorDoggie · 10-31-2006, 01:46 PM

I have this at the moment:

Code:

* * * * * mysqldump --add-drop-table -u ***** -p**** database_name > phpbb_backup-$(date +%m%d%Y).sql

which should do it every 1 minute right?

However, it dosen't seem to work. Where is the backup file, named phpbb_backup-*date*, going?

Also, is it possible to make another cron job to delete and phpbb_backup-*date*.sql files that are older than 2 weeks old?

Also, sorry, what does "--add-drop-table" mean? Will that copy EVERYTHING. So, if the whole db goes down, will the tables be copied (like "CREATE TABLE ** (blah...." and stuff? Also, will ALL the data be in there?)

Thanks a lot
Tom

Peter · 11-01-2006, 01:34 AM

if "--add-drop-table" is enabled it will add the drop table command into the database backup so if some of the tables exist when restoring it will delete the tables already there and upload the backed up tables. Usually if a table in the backup already exists and you try to do the backup without those commands the restoration will fail.

regarding where the file is have you tried doing a command like :-

locate phpbb_backup-

they are most likely in your root folder but not 100% sure.

also regarding how to delete the files after 2 weeks 1 thing you could do is put the files into a folder that php has complete access with (only have backups in it). Then iterate over the files with http://www.php.net/manual/ro/function.readdir.php ensuring that each entry is a proper file. Then with the file name strip out anything not to do with a date and then convert it to a unix timestamp. Then simply compare it to mktime()-1209600 which is the unix timestamp for 14 days ago from the current time.

To compare them simply have:-

if($file_time<$delete_timestamp)
{
unlink $filename
}

where $file_time is the timestamp from your file and $delete_timestamp is the timestamp for 14 days ago.

PoorDoggie · 11-01-2006, 01:10 PM

Quote:

Originally Posted by filth@flexiwebhost

if "--add-drop-table" is enabled it will add the drop table command into the database backup so if some of the tables exist when restoring it will delete the tables already there and upload the backed up tables. Usually if a table in the backup already exists and you try to do the backup without those commands the restoration will fail.

Aha... thanks a lot

That helps.

Quote:

regarding where the file is have you tried doing a command like :-

locate phpbb_backup-

they are most likely in your root folder but not 100% sure.

err.... what? lol

Quote:

also regarding how to delete the files after 2 weeks 1 thing you could do is put the files into a folder that php has complete access with (only have backups in it). Then iterate over the files with http://www.php.net/manual/ro/function.readdir.php ensuring that each entry is a proper file. Then with the file name strip out anything not to do with a date and then convert it to a unix timestamp. Then simply compare it to mktime()-1209600 which is the unix timestamp for 14 days ago from the current time.

To compare them simply have:-

if($file_time<$delete_timestamp)
{
unlink $filename
}

where $file_time is the timestamp from your file and $delete_timestamp is the timestamp for 14 days ago.

Ok, so I make a php file that looks for files to delete, but how do I automate that to run at the same time as the database backup.

When I know that everything works I am going to make it backup the db at midnight every second day.

Thanks
Tom

Peter · 11-01-2006, 01:49 PM

Quote:

Originally Posted by PoorDoggie

Ok, so I make a php file that looks for files to delete, but how do I automate that to run at the same time as the database backup.

When I know that everything works I am going to make it backup the db at midnight every second day.

Thanks
Tom

Run the script through a cron as well immediately after the backup cron.

Quote:

Originally Posted by PoorDoggie

Quote:

regarding where the file is have you tried doing a command like :-

locate phpbb_backup-

they are most likely in your root folder but not 100% sure.

err.... what? lol

if you have ssh access to the server you can use that command to find where files are.

PoorDoggie · 11-01-2006, 01:54 PM

Quote:

Originally Posted by filth@flexiwebhost

if you have ssh access to the server you can use that command to find where files are.

err... no

is there any way round it? like, putting it in a specific directory or something?

Thanks
Tom

Peter · 11-01-2006, 03:28 PM

ok the best way to sort it to do what you want is to create a folder on your hosting package preferably not web accessible (if it is restrict access using htaccess)

now in the cron command instead of just having the filename make sure you specify the full server path to the newly created folder

PoorDoggie · 11-03-2006, 01:29 AM

no that didn't work either.

What I did do though, was create a php script that emailed me, and ran that every minute, and that worked, so all I did was to just take the query, put into php, make a file and then have it email it to me.

I think email is going to be better anyway... although I can't delete old copies as easilly. I use thunderbird, so aren't all attachments downloaded to my computer when I get the email anyway? If my server goes down then I will still have the backups here

Tom

Joe · 11-05-2006, 07:09 AM

erm
i am not sure if the attachments are downloaded onto your computer.. =/

Joe

10-31-2006, 01:46 PM	#1 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	Argh help me! lol - phpbb backing up database via cron I have this at the moment: Code: * * * * * mysqldump --add-drop-table -u *** -p** database_name > phpbb_backup-$(date +%m%d%Y).sql which should do it every 1 minute right? However, it dosen't seem to work. Where is the backup file, named phpbb_backup-date, going? Also, is it possible to make another cron job to delete and phpbb_backup-date.sql files that are older than 2 weeks old? Also, sorry, what does "--add-drop-table" mean? Will that copy EVERYTHING. So, if the whole db goes down, will the tables be copied (like "CREATE TABLE ** (blah...." and stuff? Also, will ALL the data be in there?) Thanks a lot Tom __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

11-03-2006, 01:29 AM	#7 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	no that didn't work either. What I did do though, was create a php script that emailed me, and ran that every minute, and that worked, so all I did was to just take the query, put into php, make a file and then have it email it to me. I think email is going to be better anyway... although I can't delete old copies as easilly. I use thunderbird, so aren't all attachments downloaded to my computer when I get the email anyway? If my server goes down then I will still have the backups here Tom __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

11-05-2006, 07:09 AM	#8 (permalink)
Joe Senior Member Join Date: Oct 2005 Location: Kent ~ U.K. Posts: 3,246 88.85 NP$ (Donate)	erm i am not sure if the attachments are downloaded onto your computer.. =/ Joe __________________ Fused Hosting Simplicity and Superiority. █████████████████████████████████████████████ Affordable web hosting solutions; shared, reseller, shoutcast, WHMSonic, proxy, dedicated and more! My computer is broken; new components have been ordered.

11-01-2006, 01:34 AM	#2 (permalink)
Peter Senior Member Join Date: Nov 2003 Location: Scotland Posts: 4,900 0.60 NP$ (Donate)	if "--add-drop-table" is enabled it will add the drop table command into the database backup so if some of the tables exist when restoring it will delete the tables already there and upload the backed up tables. Usually if a table in the backup already exists and you try to do the backup without those commands the restoration will fail. regarding where the file is have you tried doing a command like :- locate phpbb_backup- they are most likely in your root folder but not 100% sure. also regarding how to delete the files after 2 weeks 1 thing you could do is put the files into a folder that php has complete access with (only have backups in it). Then iterate over the files with http://www.php.net/manual/ro/function.readdir.php ensuring that each entry is a proper file. Then with the file name strip out anything not to do with a date and then convert it to a unix timestamp. Then simply compare it to mktime()-1209600 which is the unix timestamp for 14 days ago from the current time. To compare them simply have:- if($file_time<$delete_timestamp) { unlink $filename } where $file_time is the timestamp from your file and $delete_timestamp is the timestamp for 14 days ago.

11-01-2006, 03:28 PM	#6 (permalink)
Peter Senior Member Join Date: Nov 2003 Location: Scotland Posts: 4,900 0.60 NP$ (Donate)	ok the best way to sort it to do what you want is to create a folder on your hosting package preferably not web accessible (if it is restrict access using htaccess) now in the cron command instead of just having the filename make sure you specify the full server path to the newly created folder

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)