I have mysql running on a platform that physically stores data on compact flash. We currently have issues with data corruption where people shut down the system by powering it down hard, the database gets corrupted.
I need to know if there are options in mysql to help flush the buffer in such a way to minimize data corruption for any major updates to the database.
For example, during an update to a table, the system may be powered down. Any ideas on how to minimize data corruption for this volatile environment is appreciated.
what storage engine do you use for your tables? InnoDB tables should in theory be able to handle this without problems, if I understand matters correctly. During an update, the data is first written to a log and then to the table space. If the unexpected (or, in your application, the expected) happens, it should be able to roll back half-written rows. For this to work, you should probably:
1. Convert all tables to InnoDB.
2. Not perform any updates on tables that you must not convert to InnoDB (such as the mysql.user table). Make sure that you do not convert MySQL's internal tables to InnoDB!
Hope this helps. If or if not, hope to hear from you!