SQL backups

[jimnyc]

Eric seems to have the most knowledge, but if anyone else has an opinion, please chime in!

I'm backing up the board database locally. If I back it up on the server it just takes up additional space. The board is still small, but the database is already 36meg. It takes me about 5 minutes to do the backup, but obviously it is going to get bigger and longer.

What do the bigger boards out there do that have thousands of members and hundreds of thousands of posts?

There is the master database, which is broken up into smaller databases (posts, words, searchindex, members, PM's...) I guess they can be backed up incrementally, but then it becomes a pain in the ass tracking what has and hasn't been backed up on which dates.

Eric?

Anyone?

 

[Jackass]

Uuhhh...stop backing up so much?? Seems pretty simple to me!

 

[janeeng]

hahahah, i'll go with what the Jackass says!!

 

[jimnyc]

2 peas in a pod! :finger:

 

[janeeng]

Well, don't go that far, PLEASE!

 

[Jackass]

Jim you ever insult me like that again and I will kick your ass!!

 

[eric]

Jim the bigger boards generally do not use shared hosting, the use either dedicated or co-location hosting, which gives them unlimited access to not just the shell but the entire server, including local backup systems.

First I would not do incremental backups. If one of the increments gets corrupted it is almost impossible to restore the data. The problem is not having access to the shell. With access you could run utilities to compress the files. These utilities are built right into the unix/linux OSs. Keep in mind that most databases are based on the B-Tree algorithm that leave a tremendous amount of currently unused slack. I have been able to compress a 100Meg file down to 10Med. When a B-Tree node goes thru a split, almost a full cluster is reserved between the spit ones. This is done for the sake of performance not storage. This creates a perfect environment for zipping, lots of repetative fill characters. Ok let me not go off too far on a tangent.

So what do we do?

First I would ask my provider for shell access. Many providers will grant this request.

Second, tell me is it the download speed that is the problem? With decent FTP software running on the server and a cable connection you should achieve download speeds of 600-900 KBytes/sec. Make sure your provider does not throttle down speeds at certain times of the day to minimize bandwidth reduction. If they do, try doing the backups in the middle of the night (2-4 am).

Also find out if you provider is making daily reliable backups every night, as they all should. Now you may not want to count on them, which I do not blame you for.

Other than that there really is not much more you can do, you need to pull the whole ISAM file at one time. I think compression, faster downloads, or local backups are really the only answers at this time, but I just got home, give me a little while to give it some thought.

Eric

 

[janeeng]

Jim you ever insult me like that again and I will kick your ass!!

Is that a promise, because if so, Jim told me that your a piece of shit! hahahahaha!!! can I take bets on who will win?

 

[jimnyc]

Thanks for the reply, Eric, that makes a lot of sense. I guess if someone has that kind of traffic coming to their site that they can afford to do inhouse hosting.

Shell access has been ruled out. They say they haven't allowed it in over a year and have no plans to reinstate that access in the near future due to security concerns.

I'm getting around 800-1Mb download speeds from my host, so thats not much of an issue, yet! I use MySql front currently for administration on the Sql end, I need to find a program that will allow me to automate backups and verify.

They do nightly backups but make it clear in the TOS that the customer is 100% responsible for their own backups and that they do it as a courtesy. They must be either eliminating portions of the database or compressing it as you outlined. I do a standard backup and it is currently 36meg. The backups they provide are available online (always a 7 day rotating schedule) and are currently about 25meg.

They are a decent provider in most aspects though. In fact, they just gave every customer a freebie yesterday! The space on the server WAS 650meg and they raised that to 1gig and the monthly transfer was 45gig and daily was 2.5gig and they raised them to 60 and 3 respectively. All this for $7.77 a month and I guess I really can't complain about shell access. They also offer unlimited FTP accounts, 250 email accts with webmail, 1 free Sql database... a lot more to list but those are the sellers.

 

[Jackass]

Originally posted by janeeng
Is that a promise, because if so, Jim told me that your a piece of shit! hahahahaha!!! can I take bets on who will win?

You can if you one..but theres only one sure bet here!!

 

[Jackass]

Originally posted by janeeng
Is that a promise, because if so, Jim told me that your a piece of shit! hahahahaha!!! can I take bets on who will win?

Besides..thats not an insult...thats Jim's everyday greeting!