[Soliloquy]

Can anyone recommend another method or script for backing up a very large database? I tried using Ryan's shell script tutorial, and though it worked at first, I think it times out while trying to make the backup due to the size. The site owner wants to be able to log on via the web every so often and download the latest backup; the account has shell access but she can't handle learning all that. The account does not have phpMyAdmin, and she doesn't want to use that either anyway. (FTP is pretty daunting to her as well.)

I found a PHP script that would make a regular backup and email it, but the size of the zipped database is too big for that. Does anyone know of any simple-to-use PHP scripts that will make a regular backup and store it on the server, and allow someone to log in every now and to download the latest version? And would anyone know how to solve the likely timing out problem? (If I execute the shell script manually it does create the full file, but I can't be doing that every day.)

Originally Posted by Soliloquy

I installed this script on a rather large medical forum a couple of months ago, and it seemed to be working ok at the time. I happened to check on the directory this morning and found all three zip files were only 20 bytes large, and the .sql files they contained were blank. The script has been running on time and creating zip files, just not saving any actual data. Could this be because of the size of the database? The backups I have saved to disk are around 25meg, I'm sure it would be larger now.

Any ideas?

[Get Shorty]

I use this and haven't had any problems with it.

http://www.adminfusion.com/forums/t2127/

[JonathanSA]

Depending on the size of your SQL Backup, you may want to look into off-site backup solutions.

Off-Site backups generally use RSYNC and you will be able to incremental backups instead of having to do full backups each time around (i.e. backup only the changes instead of everything).

[Ryan]

Or you can also make your own off-site backups for free by doing this

[JonathanSA]

Of course, Ryan, however, RYSNC is much easier to work with. You set one cron job and forget about it for the most part . Nothing to install, nothing to really configure (except a single cron job that is).

Nothing against using third party software to manage backups, however, RSYNC is much more reliable and that program will not be of much use unless it is running (i.e. hard drive crashes, PC crashes etc will prevent such).

Do correct me if I am wrong though, I am speaking from a very quick look at it .

Off-Site Backup Solution + RSYNC is one of the better options, especially if your data is mission critical. If you're only backing up a few images here and there, then you probably do not need this type of setup.

[Ryan]

Mission critical, yes, it is probably better to find a paid solution, but how many forums are mission critical?

I am 110% comfortable having on-site and local PC (my PC) backups of AdminFusion, and I would imagine that most forum owners are the same way. A lot of forum owners don't even make any backups as is...on-site or off-site...So to make on-site backups and then mirror them to your own PC would be a huge step for a lot of people.

[JonathanSA]

I'm not saying that backups made to your computer will not or could not be used as a reliable source, however, hard drives crash and I'd feel more comfortable having my data on a RAID5 setup so that the data is replicated over multiple drives.

The chances of a single hard drive failing is much higher than 5 hard drives failing at once .

If you can afford web hosting at $10-$20 per month, then another $5 to $10 a month for secured backups should not be an issue. If you're paying $30/year for web hosting, the data probably isn't overly important in the first place.

[Soliloquy]

Well, after poking around more on the web looking for similar experiences, the general consensus seems to be that doing it through the shell is the way to go. The site owner agreed to learn how to do so after I told her I would write up a tutorial with the exact commands, so she could just copy and paste.

[Ryan]

Originally Posted by JonathanSA

The chances of a single hard drive failing is much higher than 5 hard drives failing at once .

Yes, but right now I have AF being backup up on 3 separate hard drives using a combination of shell backups + syncback. The odds of 3 separate hard drives all failing at the same time are extremely rare, and stepping it up to 5 hard drives is really not going to improve reliability all that much, definitely not $5 - $10/month worth of increased reliability.

Paying for a backup solution is definitely safer and more reliable. I'm not arguing that...however, I think that for my purposes (and for many other forum owners' purposes), using a combination of shell backups + syncback is a very effective solution. It's free and it works...the little bit of added benefit I get from paying for a third party to do this for me is not worth the cost.

[JonathanSA]

For some, safer and more reliable is the only option, for others, it's not. If the hard drives are setup on a RAID array, then yes, you're probably better off than most.

If they are not on a RAID array, even if it's only mirroring to another drive, then the chances of failure are much greater as is erasure of data in the event the server's security is breached.

Again, it really depends on the type of community or website you're running and if you are pulling profit from it or just doing it for fun.

I've always been very picky about my data, whether it's a profitable venture or not. Data is data and since I do 99% of the programming work myself, I do not consider backups on a PC to be as reliable.

It takes far too long to upload from a PC to a Server, even with a T1 dedicated line, far much more time than I'd be willing to allow my website to be down due to me simply making backups to my PC.

As above, RAID is a much more reliable setup. This is not to say you have to utilize a third party, however, if I am already paying $100-$600/month for a server, $10.00 more isn't going to be an overkill for my budget.