Mysql - Repair table lost 75% of data :( - Knowledgebase

Portal Home > Knowledgebase > Articles Database > Mysql - Repair table lost 75% of data :(

Mysql - Repair table lost 75% of data :(

Posted by webstyler, 07-13-2009, 02:29 AM
Hi our customer have a table crashed we try to repair table but after this table have 102 record .. originally have 432 record.. so lost 75% of data We have try to repair from phpmyadmin and from ssh with myisamcheck any suggest ? thanks
Posted by mnaumann, 07-13-2009, 02:45 AM
Check for file system inconsistencies and for defective sectors. If you use RAID, LVM or other intermediary block device layers, make sure these are working as expected, too. Also inspect S.M.A.R.T. reports. Once this is done, roll back a recent backup. For the future, make sure your system checks the database for inconsistencies regularly and optimizes its indexes, and that you receive reports and actually notice them when such issues occur.
Posted by webstyler, 07-13-2009, 02:46 AM
Hi we are working on file of recent backup
Posted by mnaumann, 07-13-2009, 02:50 AM
Then you'll have to roll back an earlier backup. You can possibly merge the data from older backups with that of recent backups to decrease the overall amount of lost data (but this is a very time consuming process, and based on what the clients need, this can be worse than restoring the complete data of a given (older) date). Also, if you have MySQL binary logs enabled, this can help to recover the lost information. A common cause of such massive corruption, aside from block device issues, can be memory issues. So you may want to inspect in this direction, too.
Posted by webstyler, 07-13-2009, 03:27 AM
uhm.. seems that customertable.frm and .MYI is bad we must rebuild table from customertable.MYD
Posted by webstyler, 07-13-2009, 03:38 AM
myisamchk -c contenuti Checking MyISAM file: contenuti Data records: 444 Deleted blocks: 4 myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size - check record delete-chain myisamchk: error: Record at pos: 671252 is not remove-marked myisamchk: error: record delete-link-chain corrupted - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links myisamchk: error: Wrong bytesec: 105-116-97 at linkstart: 470824 MyISAM-table 'contenuti' is corrupted --- BUT if run myisamchk -r contenuti the table lost 342 record Any idea ? Tx
Posted by mnaumann, 07-13-2009, 03:51 AM
The table is corrupted, as such it seems logical that unrecoverable entires are removed when it is repaired. I don't see any contradicting statements in the output you posted. Are you suggesting differently?
Posted by ianeeshps, 07-13-2009, 05:50 AM
go o mysql use databasename repair table tablename. Regards, Aneesh
Posted by webstyler, 07-13-2009, 05:56 AM
with repair the table lost 342 record
Posted by linux-engineer, 07-13-2009, 08:06 AM
cd /var/lib/mysql/ myisamchk -r *.MYI these steps will help you repair table.
Posted by webstyler, 07-13-2009, 08:10 AM
same as sql "repair table" .. we lost 342 record on 444
Posted by nomankhn, 07-13-2009, 09:43 AM
Hi, is it possible that you tell me whats your database size? Thanks, Noman
Posted by nomankhn, 07-13-2009, 10:13 AM
For details, You can contact me at -> noman@linuxonsupport.com Thanks, Noman
Posted by aneesadmin, 07-13-2009, 10:51 AM
please check the size of /var and /tmp partition. It seems your /tmp partition is filled out.
Posted by aneesadmin, 07-13-2009, 10:53 AM
you can check the partition size by using the cmd "df -h".
Posted by Mat Sumpter, 07-13-2009, 11:02 AM
Have you tried using `myisamchk -o tablename`? This will run a safe recover on the table which might recover some of the data. This needs to be run on the freshly corrupt table before running other recovery processes, since the repair will just remove the junk data.
Posted by StevenG, 08-25-2009, 07:54 AM
Make sure the mysql server is shutdown or you flush and lock the tables prior to using myisamchk