I was just wondering how to figure out how often to do a mysqldump and at what time of day to do so?

once a day at midnight? twice a day??

Thanks :)

You can create a shell script and execute it at regular intervals using crond.

That should do the trick :)

Originally posted by jdulberg
I was just wondering how to figure out how often to do a mysqldump and at what time of day to do so?
once a day at midnight? twice a day??


That would ofcourse depend on what you need the mysqldumps for. You need to factor in:

* How much data can you afford to loose? (i.e. if you take backups more often, it will contain recent data, and you will thus loose less)

* For long how can you afford the database to be inaccessible? (the database is locked while doing backups)

* How often do you copy the dumps to tape or other form of backup

I will recommend _NOT_ using mysqldump reguarly. Instead use the mysqlhotcopy utility!

Currently, I have my cron script running mysqldump at 12am every day and sending the tar.gz file to my home machine and to a backup hard drive.

There isn't a ton of traffic on the database yet but its steadily
growing. Losing important data is definitely something that I want to stay away from.

jks, where can I find for information on mysqlhotcopy - what is the difference between that and mysqldump?

Thanks.

Here is some more information on mysqlhotcopy...

http://www.mysql.com/doc/m/y/mysqlhotcopy.html

I hope this helps..

If your databases are always being updated then a 12 hourly backup is good otherwise 24hours backup should do in most cases...

Have a great day :)

regards
amar

Originally posted by jdulberg
Currently, I have my cron script running mysqldump at 12am every day and sending the tar.gz file to my home machine and to a backup hard drive.

I do pretty much the same thing, except I send the file to a free webmail account. It makes a nice backup drive :-) I wrote a small PHP script (AMRSSBOSM) (http://www.hotscripts.com/Detailed/13109.html) that handles the backup, compresses it using gzip, and emails it to where I want. I cron'ed it, and I find that it doesn't matter too much when it's run, although as traffic picks up maybe it'll make a difference.

bobcares - correct me if I'm wrong, but mysqlhotcopy copies the database files directly. MySQLDump dumps the tables as sql statements. There's a huge difference between them. Several times I've gone to my dumps and pulled out rows I accidentally deleted (or modified) and added them back. You can't do that with a hot copy.

Originally posted by jnestor
bobcares - correct me if I'm wrong, but mysqlhotcopy copies the database files directly. MySQLDump dumps the tables as sql statements. There's a huge difference between them. Several times I've gone to my dumps and pulled out rows I accidentally deleted (or modified) and added them back. You can't do that with a hot copy.

Sure you can. Just rename the dumpfile, and copy it into the mysql directory. Then you can access it like any other table, and copy over the info you need.

The advantages of mysqlhotcopy clearly wins over mysqldump (if you have demands for high-availability).