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

[PoorDoggie]

I have this at the moment:

* * * * * 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]

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]

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.

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

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]

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.

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]

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]

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]

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]

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

Joe