I'm trying to back up my database daily at 2:30am. is this the right format?

30 2 * * * mysqldump -u root -pPassword database > backup_$(date +%y%m%d).sql

please let me know.
Thanks guys!

I would suggest writing a bash script to back up the database, then using cron to run the script.

With a script, you can create a new directory to receive the backup, give the backup a specific name not duplicating any other file name, and lots of other neat stuff.

Once I got that working, it worked perfectly until I moved my website out to GoDaddy. I haven't tried scripting on GoDaddy.

You can read the details and see the script on my blog.

I've never tried to enter variables in cron, so I'm not competent to comment on that.

There are also some weird things about cron. Seems like you also have to watch it's environment like it's shell and some other stuff.

0 members found this post helpful.

Hello struct,

Have you every tried zmanda mysql backup...Its very easy Just have a look
here

No answer to the actual question, but I strongly suggest that you don't use the mysql root user for this. Reason is that you have the password in the clear in your cron (and/or script).

I suggest that you create a mysql user who basically has read-only access to the databases (tables etc); so no delete, update, insert etc permissions.

2 members found this post helpful.

I second the suggestion by Wim Sturkenboom. In addition, if you want to run the mysqldump command directly from the crontab, better to use absolute paths both for the command and for the output file (as previously mentioned, the crontab environment is very limited, e.g PATH is usually /bin:/usr/bin. Also cron jobs are executed from the user's home directory: to assure that every file is created in the place you want it to be, better specify the full path or at least use the $HOME environment variable which is available to cron jobs).

Finally, take in mind that the % sign has a special meaning in crontab (please see man 5 crontab for details). To get it literally you have to escape using backslash, as in +\%y\%m\%d.

1 members found this post helpful.

You guys are great.
frankbell, I followed the link to the shell script. Thank you for the link. I'll find out tomorrow morning if it worked.
Probably the variables I put in place in the crontab messed up the command so it wasn't running at 2:30am like I expected. I didn't know the limitations of the cron.

As for the user that I put in the crontab only had select access to the database. If anyone has root crontab access would have access to the shell script with user/pass... so I didnt' think too much of it. But I'll try to hide it better

vinaytp, I'll put the utility for the future systems. It looks very helpful and handy to have.

Thanks to everyone who gave me the insight and valuable knowledge on this subject.

I agree with you, the folks at LQ are great. It was years between when I registered here and when I started trying to answer, rather than ask, questions. But I am really glad to have found this place.

About the script:

It took me a week to make the script work for me, because, to tell the truth, it was the first time I messed with variables in scripting. I am not a programmer by any stretch . . . .

The box in question was dedicated as a webserver and had nothing on it other than files related to the website, plus a firewall and an anti-virus, so security was a concern, but not an obsession; all a bad guy would discover is the backstory to stuff he could already read and I'm such a small fry that I'm hardly a target for someone who is looking to take over the world.

I'll give you a for instance: I'm an Opera browser fanatic, and I never even bothered to install Opera on it.

The suggestion not to use the root mysql user to run the backup is an excellent one. Out of exhaustion after all the testing, plus the reasons above, I didn't use a more restricted MySQL user to run the backup, but, if I were doing it again, I would.

If its' solved, please mark the thread as 'solved' using the thread tools just above the first post.