cron job for sql backup

[ed_lynn]

calling all php masters!!!

i need a script that can do a backup of my sql database, date it, and put it in a folder i have set up. your help will be greatly appreciated. :guess these are my thank yous!!!

ed

[harmonic]

This would be a very large script if you wanted the table structure and all. I will try to see if I can make something that uses cpanel to copy the daily backup that is provided for you.

[lucas]

you may be able to do the same thing i did here http://forums.insiderhosting.com/showthread.php?s=&threadid=165 except with directory for the mysql db backup.

[ed_lynn]

lucas,

i currently have a cron job set up to do a backup of the db but it re writes the file each time it does the backup. and since i am to lazy to go in everyday and change the filename i thought i could just throw together a script that either does what i stated above or use something similar to the one you posted and just have it copy the file and rename it so that it doesnt get written over.

make sense?

[harmonic]

If you know where the files are stored I could easily whip something up.

Or, if you show me the job you are using currently, I could see if I could make it not overwrite old backups.

[lucas]

yeah, i see your point. i *know* there has to be a way to put a date in the filename within the cron job, but i never could find anything about it. that way you could backup up using cron everyday and not worry about a script.

btw, you must have some pretty important data to be backing it up yourself each day.

[ed_lynn]

not so much that is important as it is just a pain to have to restore what i do have (forums and blogs). and i have very little time between school and work to be worried about db crashes and what not(even thought ih's servers never fail :) ). basically just a safety net for me, and for the individuals I host the forums and blogs for.

at any rate the backups are located in

/home/edlynn/forum_backups

/home/edlynn/mt_backups

these will just be test dir until i can get things running smoothly, then i will break it down per account, er whatever else clever i can come up with....

thanks again all!!!

[harmonic]

Originally posted by harmonic
try something like this:

<?
$destination = "/home/username"; // no slash at end
// stop editing

$name = date("d-m-Y").".bak";
copy("/home/edlynn/forum_backups", "$destination/$name");
?>

now just execute that, it should copy it... and it will name the logs like

01-01-2002.bak - 1st of january, 2002
01-02-2002.bak - 1st of february, 2002
etc...

[ed_lynn]

harmonic,

thanks i will give it a try as soon as i get home from work, but i have no doubts your code will work flawlessly!!!!

ed

[macshack]

Hi,

I use a perl script that finds all the databases for my account and then uses the mysqldump system utility to dump each database to a file in my temp directory. The directory is then tar'd and compressed as a file named dbs.tar.gz.

The perl script is set to run each night and the resultant file stored in my user space which is then saved/backup. So with each save I have a complete mysql database save of each DB that is within 24 hours.

I would be glad to post it here if your interested. With a few changes in the final file output area, one could have a rotating N number of saves.

Michael

[pete3005]

Before running any script as a cron job you should do some tests, upload Sys Info and keep refreshing as the script runs to ensure you are not negatively affecting everyone else on the server.

Someone ran something heavy as a cron this morning anyway, I know Steven will be looking into this today, luckily the spike lasted only 3 or 4 minutes.

I wish people would show more consideration with Perl Scripts.

Like I say, it might not be you, but someone is hogging resources at midnight for a few minutes.

Pete

[macshack]

pete,

I do.

michael

[pete3005]

Actually you do, you aren't even on my server apparently so I apologise for 'pointing the finger'.... don't ya just hate adding 2 + 2 and getting 5... sorry about that :(.

Pete :)

[macshack]

Hi pete,

It's ok, I've jumped before myself. :)

Michael

[ed_lynn]

well I hope it wasn't me causing the problem, if so then i apologize in advance. I have switched to pete's script from the other thread and it works like a dream. thanks pete.

Harmonic for some reason when you script executed it would only create a file with a o byte size in the directory i had it pointed to. I would like to use yours as well as pete so that i can keep backups with dates...any thoughts...

[pete3005]

I'm glad it worked for you, I will be adding better options like the ability to not overwrite the previous one and I will add the date to the file name and possibly zip the attachment for those with larger dbs and 56k modems.

I will also try to give the option to only backup db structure and predefined tables, sometimes you may have a huge db but only a few tables change, such as transactions, reviews or whatever. That will allow for more selective backups.

I'm just so busy right now that I don't have the time, but I hope to complete it by the end of next week assuming I get a 'client free' day or so.

When its done I'll upload it for people to use and/or modify.

Pete :)