Cron job backup of MySQL db??

nemonoman

Hi:

I'd like to set up regular backups of my MySQL db on my hosting co's server.

This would be a cron job, yes? Forgive me for my ignorance, as I am a unix-dope, but anxious to learn.

My host company says:

"We can setup cron jobs for you, or at your request we can install cron services into your sites filesystem for use via SSH."

I can SSH to my server, and follow simple commands from a Unix cheat sheet. I do simple MySQL stuff via SSH, dump and reload datafiles, check runaway processes etc. I can read and write and do simple sums, and feel confident I can handle a cron job data dump with a little advice.

How about it, all you x-ix guys? 'lil help?

Tx
Nemo

notionlogic

You say you've got SSH access so try entering "man cron" or "man crontab" at the command line for more information about cron and how to set it up on your own system

As for the actual DB backup I would suggest "mysqldump" - something like this should work:

/usr/local/bin/mysqldump -uusername -ppassword --opt dbname > /home/uname/public_html/sqldata/database.sql

Hope this helps

nemonoman

Hi thanks notion.

Just tried this, but...

'no manual entry for cron"
"no manual entry for crontab"

I think whatever cronjob I get set will have to be implemented by the tech staff. They have so far been very willing and efficient so no worries.

What should I be asking for, I guess might be another way to state my query? A mysqldump of current database every hour so? Rotating backups (means keep the backup for 2-3 days, then kill it?)

I could write these actions as a PHP program, and in fact meant to do exactly that, but I'd prefer to mechanize the process so I don't have to be the one to dial in to do it.

Also a full backup of a MySQL database via a PHP command can cause me to miss my 30-second timeout window.

Looking to get smart about this stuff.

Thanks for any guidance. Would like to hear what sort of backup systems persons have actually implemented in production environs.

nemonoman

still interested in hearing thoughts about my question...