0

After the server restarted due to a power cut, we lost all data saved in the database during the morning (client records created in the website). A few minutes before the crash, I was able to see such data on "MySQL Query Browser" during some routine checks. After the crash, all records created in the morning disappeared and the most recent ones that I could see were created at 7 p.m. the day before. It seems like all INSERT statements were performed in a cache that could not be flushed because of the power cut. Does MySQL use such caching mechanism ? If yes, is it crash proof and how can I configure it ?

Environment: mysql 5.X + ubuntu 10.4 + raid1 + Jira4.1.2

Hooligan
  • 195
  • 1
  • 2
  • 10
  • 1
    You shouldn't post a duplicate when a question is about to be moved from Stackoverflow to Serverfault: http://serverfault.com/questions/154111/mysql-data-loss-after-server-restart –  Jun 23 '10 at 17:57

2 Answers2

2

unless you are using insert delayed, inserts/updates/deletes are committed immediately. The key file may not have written itself completely. If you have a caching controller card, it may have cached it and told the OS that it had committed it. Have you run a check table/repair table against it? If so, you might find that just the index is corrupt and that most of the records are still there.

MyISAM is fast, but, lacks quite a few precautions that need to be taken if a power problem/server crash happens. InnoDB is another method that is a bit safer, but, is slower depending on the types of queries you are doing. It adds row-level locking rather than table locking, but, select count(*) requires a scan of the rows whereas MyISAM can answer that from an index.

You could set up replication to send the data to a mirror, but, if it is in the same data center, you face the same possibility of it having corrupt data.

karmawhore
  • 3,865
  • 17
  • 9
0

MySQL can be configured to cache data, but is usually not. It responds back to the client only when the data is on disk. Maybe you have got mysql setup in such a way that it tries to repair uncleanly closed tables automatically at startup and some of the records got deleted.

The query cache and sort cache only caches data that can be read from the tables (eg select ... from).

It could be that the OS is configured to cache writes (I think ubuntu ext3, defaults to 5 second commits...I don't know about ext4 that's in 10.4).

Jure1873
  • 3,692
  • 1
  • 21
  • 28