SQL backups

shawjames

Data loss is the #1 fear for our business. I would like to implement a backup system to protect our data. Currently, we are hosted on BlueHost, and according to them they "may or may not perform backups on our data, but we should not count on it."

We have had data loss in the past, but luckily it has been less then 24 hours of loss which took us less then 20 hours to re-create. Twice from hardware failure at the host, and once by the host's tech support accidentally deleting one of our tables.

I would like to perform a daily backup, and transfer the data to another host or to a local drive.

Our database currently has 38 tables, 1.7M records and 307MB in size.

Does anyone have a recommendation on how I can set this up, and maybe where I can start?

Ashley_Sheridan

If you have full root access to your hosting (i.e. a dedicated server, virtual server, etc) then you can just create backup of the directories and files within /var/lib/mysql (may var from distro to distro) assuming you're using a Linux web server. It's best if you can copy the files to a remote location (as it sounds like the support you've got at Bluehost would find it difficult organising a booze up in a brewery!)

Alternatively, move to a host that gives you backups as part of the package. I'm assuming this isn't an option though, or you'd likely have already done it considering the awful service it sounds like you've had thus far.

shawjames

Blue host is acceptable, I'd say least worst of what I've tried. They do backups, but any chance they get they will tell me not to rely on them... likely so they cannot be blamed when things go wrong.

perhaps I can just run a cron job to backup my sql database every day. Then I can have a remote system FTP out the backup file.

I do have full shell access. Do you know how to do a simple sql dump from a php cron job?

I found a simple script to perform the backup:

http://www.underwaterdesign.com/2006/06/php_mysql_backup_script_for_cr.php

See any reason why this would not work?

Ashley_Sheridan

I wouldn't go with a sql dump but rather copy the database files in that directory I mentioned. If the databases are very active though, you might want to temporarily lock them while the files are copying.