-1

0 down vote favorite We have damaged disks on the server and partially lost data. Backup of the database was not the most relevant, so it was decided to restore the database from the .myd, .myi, .frm files.

Since the tables were MyISAM, the restoration itself is simple. We stopped mysql, copied the files to the directory, started mysql.

As a result, the database went up, at first glance, everything is fine, but it turned out that some of the records in different tables (inside one database) are missing. CHECK TABLE thus does not show any problems. There are no strange glitches, no damaged or erroneous records, that is, it looks like it's all right.

Accordingly, in connection with this, two issues are ripe:

Is the database recovery process correct? In theory, there are no difficulties, copied the files and is ready. But, maybe, there are nuances?

In general, how could such a situation turn out? Judging by the fact that everything was restored without any errors, we can conclude that these database files were not damaged on a defective disk. But why and where then did the records disappear? Maybe still there is an opportunity to get them somehow? Just in my understanding, if the file was corrupted, nothing would be restored. And then it turns out that just a few hundred / thousand records were missing. (Total DB on 100k + records)

  • If your backup was not the most recent, where did those MYI/MYD files come from? – utdrmac May 30 '18 at 15:33
1

MyISAM is not a transactional engine. I'm not surprised with some missing records. Your first job should be to convert all those to InnoDB which is a transactional, ACID compliant, engine. MyISAM is out of date and hasn't been improved in 6+ years.

For MyISAM, yes, your recovery process was correct: simply copy the files. This process is NOT the same for InnoDB since it has more moving parts. You will need to alter the way you do backups/recovery once you move to InnoDB.

MyISAM can do a type of "crash recovery" based on the MYI file, so it is highly possible that you had rows in the MYD file but they did not make it to the MYI file (because MyISAM relies on filesystem buffering), thus the rows show as missing. You "might" be able to strings the MYD file and grep out some data if you really need it.

Your second job is to set up a slave MySQL so that in the event your main one crashes due to disk corruption, the slave has most up to date contents on different disks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.