Servers fscked during upgrade

Servers are off site somewhere in texas i think, in a data centre, not in vbs bedroom ;)

VB have you been using timevault?
https://launchpad.net/timevault/+download
https://wiki.ubuntu.com/TimeVault/

you could replicate each machine to another and if you needed it can replicate on an hourly basis, run an incremental back up to a nas at home out of hours for a daily backup?

*are you moving from virtual boxes/one machine to three physical boxes?

Says the man who uses an Etch a Sketch as a back up option*

• Younger forum members may need to do a search to find out what a Etch a Sketch is.
  

I am in VBS's bedroom ;)

you could replicate each machine to another and if you needed it can replicate on an hourly basis, run an incremental back up to a nas at home out of hours for a daily backup?

*are you moving from virtual boxes/one machine to three physical boxes?

AWS costs about twice what we pay, I checked.

And yes, I'm backing up whole images. Because it's all virtual, the backups are actually cheap for the hosting company to do, so they're cheap for me.

rsync.net is dirt cheap too, but that's a rather fab service that I've used before. So I'll do the data backup to that.

My concern is how to backup a 1.5GB database without degrading availability of the site. Even at 4am there's a few hundred people visiting the site.

And transfer is free, but I want a more efficient backup than moving 1.5GB daily. What's the most efficient way of backing up mysql and shoving the data across the web? Ideally it would be a delta... does rsync work at block level for the physical database tables? These are some of things that I need to work out.

I thought about replication too. But replication isn't backup, and then you need another server altogether.

excuses excuses, replicate to another server on the fly then backup the spare server

Are you paying for the extra server(s)?

I pay for 3 servers. I pay a little extra to have the images of those shadow copied and backed up on the each day, week and month.

If I were to replicate, I would have to spin up another server. Servers aren't quite so cheap as to make that an option.

I could do replication, but I'm already running the database quite hot during the day (in fact I was thinking of putting Sphinx in place to cope with the searches). I want cheaper front-ends rather than more expensive ones (because of replication).

It's about balancing the service, the costs, the risk. I had let the risk get high to keep the service and costs important, but clearly if I roll back 5 days I hadn't quite got the risk assessed correctly.

What's the additional cost with just setting up a rolling daily backup using what you have now?

What's the additional cost with just setting up a rolling daily backup using what you have now?

Its cheaper in Poland.

Do those few hundred people at 4am matter as much as 400 of my posts saying UTFS? I think not! ;)

Insincere smiley.

And that large post of mine about helmet lighting.. fucking waste of time telling you to not derail your own threads eh? :)

Ohhmmmm....., ohhmmmm....., ohhmmmm....., ohhmmmm......

Tis karma. ;)

.

I Blame Beth Ditto

Tis karma. ;)

hmmmmmmmmm

.

oof 1.5Gb a night!
what we have here is all the basic images of each server on each seperate server and the same images off site in our co location just in case. We run a seperate incremental back up for data on these servers so the daily backups are 20 - 30 Mb for example as they only backup what has changed.
The first backup is the killer as you do a complete back up of all data and then only changes are backed up as there is no need to backup all that data again if nothing has changed, then you archive your daily/weekly/monthly as you see fit

so in a disatser recovery situation you restore the server to the basic ready to go live state then restore your data, yes it would take longer to do this but you would save shifting those complete server images around every night!

Says the man who uses an Etch a Sketch as a back up option*

• Younger forum members may need to do a search to find out what a Etch a Sketch is.
  
  

meh I print out hard copies of everything and have it faxed over to the other office, we have two massive filling cabinets and if anything goes wrong we have to type it back in..may as well do it right!

full monthly, incremental daily, easy as

How do you incrementally update a mysql database dump?

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

I do know what mysqldump and mysqlhotcopy are.

But, if a day only has 2,000 posts, and 1,000 private messages, 100 attachments and 30 new users... then why do I need to dump and send 1.5GB of data to the disk and then over the wire each day.

It's about the fact that sending 1.5GB when only 300KB has changed appears to be very dumb.

mysqldump takes a whole database and plops it into one file. But even taking just a single table would still create 1 file for the posts table of over 700MB. Still dumb.

Compression doesn't help as a single difference in the file (and every database dump has the date printed), compression would make the change far more ambiguous and change the compression table which would require the whole file gets backed up. Were I to actually have a block level backup utility that transferred only the parts of a large file that had changed the whole file would appear to be changed. So plain text beats compressed file if block level backup is achieved.

Replication is stupid too. DROP DATABASE replicates just as well as INSERT. Replication is not backup, plus it is an overhead when high availability is not needed, and would cost more for an extra server. All negatives, no positives. The only time replication looks interesting is if I can have it replicate and the slave does the cold backup, but that's still an extra server just for backups and that's still more expensive than rsync.net which gives off-site backup.

mysqlhotcopy isn't considered "disaster recovery", and mysqldump gets us back to monolithic backup files that lock the database during backup.

What I need is a scalable way to perform a backup with low impact but high integrity, and that ideally would also support differential block level backup.

What you're all proposing is perfectly fine if you run a wordpress blog, but doesn't scale when your database starts going multi-GB. MySQL know this is a big flaw, version 6.0 of MySQL finally goes some way to solve it ( http://dev.mysql.com/tech-resources/articles/mysql-60-backup.html ). But we're on version 5.1, and even version 5.4 isn't out yet and version 6.0 is a pipe dream at the moment.

theres 6 Database guys in my office, wait and ill grab one of those wee code monkey bastards and rattle their cage!

MY SQL allows you to replicate via the logs no?
binary logs created can be backed before flushing OR as they are created and a second instance on the back up can consolidate the logs.
why don't you cluster BTW?

ah ignore the cluster question.. cost

Cost is a large factor. We have a pretty nice thing for the price we pay. We're Rackspace hosted on some very nice machines. The problem comes down to their backup service being a little limited for the virtual machine offering (those three backup slots and no option to pay for more and have true daily backup).

There's also the problem with how it's grown slowly and I've had to react to conditions. Effectively all 3 boxes are single points of failure. I couldn't have made it worse if I tried. The new design would only have 2 points of failure out of 4 boxes, and the points of failure should be easy to restore reasonably quickly. Plus this design doesn't actually cost a penny more than what we pay (not a penny less either).

So it's about backing up data remotely in a way that can be restored quickly, without affecting the site and without breaking the bank (almost broke, must get onto SJS).

There are some technical options, but they cost. I'm trying to find the "cheap and not-so-dirty" way to get the good stuff without paying for it, perhaps by doing more work (because my time is apparently a free and unlimited resource in comparison to cold hard dollars).

apparently windows 7 does everything you need..according to the 327 adverts I saw this weekend and woke up to it on the radio this morning..at 7am...oh.. i see what they did there now! ..better not be on at 7pm when I get home!

Doesn't really help you, but don't use a piece of shit db like mysql. Personally I always try to use postgres.

Doesn't really help you, but don't use a piece of shit db like mysql. Personally I always try to use postgres.

Horses for courses
http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html

If you agree with the above comparative analysis, MySQL seems like the better choice for this application.

Doesn't really help you, but don't use a piece of shit db like mysql. Personally I always try to use postgres.

vBulletin has been written with MySQL in mind, it's basically optimised for this engine. I've put in the request loads over the last decade for vBulletin to put in a database abstraction layer so that I can use something else. I'd probably use Oracle under a non-profit licence if that were possible.