MySQL Database Mirroring: When Automatic Backups Fail

Posted by santosj on September 16, 2006

Back Story

Yes, my blog has been down for the past week or so. So what. That isn’t quite the issue, but it did give me quite a bit of reflection about past projects, besides the general confusion of why the database started to act up. This isn’t a rant about MySQL, because the blame is completely on me. Stuff happens and I really should have been more prepared.

———————-

Does anyone know why in a database you can sort of half-assed connect to, gather a few SELECT statements, but can’t dump (using mysqldump) or do any intensive SQL work? Also giving some user has too many connections error, when there are only one or two (accurate) connections.

Database Mirroring

It occurred to me in my darkest hour that if only I had a backup database that there wouldn’t have been any down time. Of course, if I had a hard backup on my computer there would only have been a few hours to a day or so of downtime. However, no downtime would have been just fine for other projects.

However, while I was contemplating this “genious” idea, a few flaws kept nagging me.

  • Redundancy is best done at the physical layer and this would be at the application layer.
  • It would be insane to keep up-to-date copies and it would be pointless to have something that would already be outdated
  • Keeping two or more backup copies is a good thing, generally. Sigh, if only I had a backup.

For research purposes, I’m going to try to implement this on another project and see how well it works (or doesn’t work). It would be fun to try different methods of wrongly implementing it.

Well, there is one reason to have this and it would be mainly for “shared” users, who are able to spare an extra database or two, who don’t have access to the hardware for clustering and hard drive mirroring. However, the problem I did have was a strange one and I could have used this method.

Self Restoration

It would be even better if perhaps, the database access layer could tell that the database was no good and then try to use a separate one. After it found a better database that it had permissions for, it could use the mysqldump file to restore itself and then retry.

Most of the projects I’ve worked with, it would take several minutes to reload the database tables, so it would be a short wait. It would make sense to display a message, like the one WordPress gave when the database could no longer be accessed, until it could restore itself.

I very much like the idea of displaying database and updating messages to visitors and site webmasters. It gives a better notice of what is going on, then just displaying a PHP error.

Conclusion

Really, this is just a case of denial and avoiding the main point of keeping secondary backups on your hard drive.

I’ll tell you one thing, I spent many hours ordering my family picture gallery and I’ll be damned if I’m going to spend that time again. As well as I was in the process or moving some of the posts to a wiki, where they would be better suited. As much as I hate writting stuff again, I did spend enough time on my own trying to get back the data.

Possibly Related Posts:


General

1 Comments.

  1. Steve Kurzeja September 16, 2006 at 4:51 pm

    I remember about 4 years ago I had an issue with mysqldump segfaulting, but could still partially use the database.

    It was caused by some incompatible libraries on a custom built linux distro. Have you upgraded any libraries recently?

    If you are using MyISAM just back the raw files now, and try them on a fresh MySQL server install.

    Also, MySQL replication is what you want for redundancy :)

    Then you can build in fail-over into the application layer.