3 ms·

Now I'm questioning my ability to do mysql backups ... What are you finding difficult? How did it lose you x00,000? (just curious)

patio11 13y ago
Long story short: I thought I had two types of backups, MySQL dumps and snapshots of the VPS the MySQL server ran on. It turned out that those were both stored on the same hardware. (D'oh.) Which then had problems. (D'oh.) And my only accessible backups were a day old. I was either forced with "Lose a day of data" or "Reconstruct current state of SQL database from a machine will now not boot." Long story short: After some don't-try-this-at-home-kids craziness I was able to bring up a second copy of the production environment from the bare metal, initialized it with an empty database, and then copied out the contents of MySQL from the barely functioning hard drive on a machine booted into rescue mode. How did it almost lose me $X00,000? Well, clearly losing the database is Very Bad and losing the last day of data is Quite Bad, right? $X00,000 = somewhere between Very Bad and Quite Bad times the value of being the sole shareholder in Appointment Reminder.
- bigiain 13y ago
  "I thought I had two types of backups, MySQL dumps and snapshots of the VPS the MySQL server ran on. It turned out that those were both stored on the same hardware." Can I advise anyone reading that who can't immediately say "I'm 100% sure that's not a problem for me." to go and check ASAP. Just a few weeks ago, a bunch of my clients on a not-to-be-named hosting provider all got notice of brief scheduled downtime while new server hardware was to be installed in the racks. Long story short: They chose to use the downtime to power down and move both Dell SAN boxes across the aisle ("to make the wiring neater")- neither of them successfully powered back up (with ~72hr of fingerpointing before Dell admitted no data was going to be recovered). Turns out hosting company had been storing all VPS snapshots _and_ daily filesystem/database backups on those two SANs. ~150 websites ended up being restored from weekly backups (which, of course, were 6 days old - or almost 8 days by the time the "waiting round on Dell to recover the SAN data" plug was pulled). It's hard to tell if we were lied to, mis-lead, or just allowed to make untrue assumptions, but we were certainly not expecting both the dailies and the VPS snapshots to all vanish with the same hardware failure... (It's unclear so far whether just one of the two SANs failing would have been quite so spectacularly bad... Fortunately these were almost all sub $30/month hosting accounts, so nobody was _too_ precious about their downtime or weeks worth of missing updates)
  - rmc 13y ago
    I'll second the call to double check that your database backups are OK. You do not want to discover that your backups are broken when you go to recover (this happened to me once).

patio11 13y ago

Long story short: I thought I had two types of backups, MySQL dumps and snapshots of the VPS the MySQL server ran on. It turned out that those were both stored on the same hardware. (D'oh.) Which then had problems. (D'oh.) And my only accessible backups were a day old. I was either forced with "Lose a day of data" or "Reconstruct current state of SQL database from a machine will now not boot." Long story short: After some don't-try-this-at-home-kids craziness I was able to bring up a second copy of the production environment from the bare metal, initialized it with an empty database, and then copied out the contents of MySQL from the barely functioning hard drive on a machine booted into rescue mode. How did it almost lose me $X00,000? Well, clearly losing the database is Very Bad and losing the last day of data is Quite Bad, right? $X00,000 = somewhere between Very Bad and Quite Bad times the value of being the sole shareholder in Appointment Reminder.

bigiain 13y ago
"I thought I had two types of backups, MySQL dumps and snapshots of the VPS the MySQL server ran on. It turned out that those were both stored on the same hardware." Can I advise anyone reading that who can't immediately say "I'm 100% sure that's not a problem for me." to go and check ASAP. Just a few weeks ago, a bunch of my clients on a not-to-be-named hosting provider all got notice of brief scheduled downtime while new server hardware was to be installed in the racks. Long story short: They chose to use the downtime to power down and move both Dell SAN boxes across the aisle ("to make the wiring neater")- neither of them successfully powered back up (with ~72hr of fingerpointing before Dell admitted no data was going to be recovered). Turns out hosting company had been storing all VPS snapshots _and_ daily filesystem/database backups on those two SANs. ~150 websites ended up being restored from weekly backups (which, of course, were 6 days old - or almost 8 days by the time the "waiting round on Dell to recover the SAN data" plug was pulled). It's hard to tell if we were lied to, mis-lead, or just allowed to make untrue assumptions, but we were certainly not expecting both the dailies and the VPS snapshots to all vanish with the same hardware failure... (It's unclear so far whether just one of the two SANs failing would have been quite so spectacularly bad... Fortunately these were almost all sub $30/month hosting accounts, so nobody was _too_ precious about their downtime or weeks worth of missing updates)
- rmc 13y ago
  I'll second the call to double check that your database backups are OK. You do not want to discover that your backups are broken when you go to recover (this happened to me once).