Friday, January 21, 2011

What possible events could cause a MySQL database to revert to a previous state?

A client of mine recently had a strange event with their MySQL database. Several days ago, one database suddenly "went back in time". All the data was in the state it was in several months ago. Even most of the .MYD and .MYI files had timestamps from November.

Fortunately, the server is not in production yet, but we need to understand how it happened so it doesn't happen again.

I'm not a MySQL guru, but I couldn't think of a scenario that could cause the database to rewind like that short of restoring from a backup. What could have happened here? Where should I look for clues?

(Server is FreeBSD 6.4)

  • Nothing showing up in the logs for the time period where the reversion was believed to have happened?

    No one ran an operation that deleted records after a certain date?

    Are you looking at raw data from the database or from an application that may be filtering the output so you don't see things after a certain date?

    Any data restore from backup run, anyone playing with filesystem snapshots?

    Any scripts running to make backups that could have hiccuped?

    Who else has access to that directory with rights to copy/modify programs? You said it wasn't a production database, so are developers playing with the server, with access to version control that could have done something to the file?

    Filesystem errors showing up? Was it just the database files that were affected, no other system or user data? Logfiles?

    Bart Silverstrim : This was just a quick list of things that would go through my mind, but I realize you said the datestamps are also backdated, so I'd suspect that either someone restored the database directory, copied files back to he directory to "fix" a problem, or someone did something with filesystem snapshots to revert the system to a previous state, but those are my suspicions. Someone else more well versed in db's may have another idea, but this sounds more like someone did something accidentally or is covering something up and has admin access to the machine.
    Bart Silverstrim : At any rate as far as I know databases don't just "roll back" complete with datestamps in any database transaction or from the database engine, and it would have had to happen in a way that it doesn't cause issues with the database engine running in memory. I'm no DB admin but I would think the SQL engine would be unhappy having the active database files yoinked from under it as it was running, and so the logs should have the fallout if the database doesn't crash (and it should have had some filesystem locks since it would be open, I'd think...)
    Bart Silverstrim : Oh, and I'd also start automating a few scripts to pull up records on who was logging into the machine for the past few days to see who was in it when, and audit that with when it approximately happened to narrow down who may have done it, even accidentally. You can also grep for commands using sudo if your admins aren't using root by default. If not the case you might need to change policy for a better audit trail, or at least have a list of who has access to alter database files and such on the system.
  • # cat /etc/fstab
    # mount
    

    Maybe mysql database, along with, for example /var, was copied to separated disk and then mounted on the top of existent /var(/db(/mysql))?

0 comments:

Post a Comment