Shulou(Shulou.com)05/31 Report--

This article introduces how to repair .frm file, .MYD file and .MYI file in MySQL database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

How to repair MySQL database .frm files, .MYD files and .MYI files

To check for errors in a table, simply run myisamchk (under MySQL's bin directory) and provide the location and table name of the file, or the index file name of the table:

% myisamchk/usr/local/mysql/var/dbName/tblName%myisamchk/usr/local/mysql/var/dbName/tblName.MYI

Both of the above commands perform a check on the specified table. To check all the tables in the database, you can use wildcards:

% myisamchk/usr/local/mysql/var/dbName*.MYI

If there are no options, myisamchk performs a normal check on the table file. If you have doubts about a table, but a normal check cannot find any errors, you can perform a more thorough check (but also more slowly!), which requires the-- extend-check option:

% myisamchk--extend-check/path/to/tblName

Checking for errors is non-destructive, which means you don't have to worry that performing checks on your data files will make existing problems worse. On the other hand, the repair option, although usually safe, cannot be undone to your data file. For this reason, we strongly recommend that you first make a backup when trying to repair a corrupted table file and make sure that your MySQL service is turned off before making this backup.

From the command prompt under windows2003, enter:

Note: this is to record the whole process of my operation at that time.

D:\ DocumentsandSettings\ Administrator > mysql C:\ > cdmysqlC:\ mysql > cddataC:\ mysql\ data > cdhw_enterpriceC:\ mysql\ data\ hw_enterprice > myisamchkfunction_products.frm

'myisamchk' is not an internal or external command, nor is it a runnable program or batch file.

C:\ mysql\ data\ hw_enterprice > cd\ C:\ > cdmysqlC:\ mysql > cdbin

Note: view help information for myisamchk

C:\ mysql\ bin > myisamchkmyisamchkVer2.6forWin95/Win98ati32ByMonty,foryourprofessionaluseThissoftwarecomeswithNOWARRANTY:seethePUBLICfordetails.Description,checkandrepairofISAMtables.UsedwithoutoptionsalltablesonthecommandwillbecheckedforerrorsUsage: Myisamchk [options] tables [.MYI] Globaloptions:-#,--debug=...Outputdebuglog.Oftenthisis'd:t:o,filename'-,--helpDisplaythishelpandexit.-O,--set-variablevar=optionChangethevalueofavariable.Pleasenotethatthisoptionisdeprecated Youcansetvariablesdirectlywith'--variable-name=value'.-t,--tmpdir=pathPathfortemporaryfiles-s,--silentOnlyprinterrors.Onecanusetwo-stomakemyisamchkverysilent-v,--verbosePrintmoreinformation.Thiscanbeusedwith--descriptionand--check.Usemany-vformoreverbosity!-V,--versionPrintversionandexit.-w,--waitWaitiftableislocked.Checkoptions(checkisthedefaultactionformyisamchk):-c,--checkChecktableforerrors-e,--extend-checkCheckthetableVERYthroughly.Onlyusethisinextremecasesasmyisamchkshouldnormallybeabletofindoutifthetableisokevenwithoutthisswitch-F,--fastCheckonlytablesthathaven'tbeenclosedproperly-C,--check-only-changedCheckonlytablesthathavechangedsincelastcheck-f,--forceRestartwith'-r'ifthereareanyerrorsinthetable.Stateswillbeupdatedaswith'--update-state'-i,--informationPrintstatisticsinformationabouttablethatischecked-m, -- medium-checkFasterthanextend-check,butonlyfinds99.99%ofallerrors.Shouldbegoodenoughformostcases-U--update-stateMarktablesascrashedifyoufindanyerrors-T,--read-onlyDon'tmarktableascheckedRepairoptions (Whenusing'-r'or'-o')-- medium-checkFasterthanextend-check,butonlyfinds99.99%ofallerrors.Shouldbegoodenoughformostcases-U--update-stateMarktablesascrashedifyoufindanyerrors-T,--read-onlyDon'tmarktableascheckedRepairoptions (Makeabackupofthe.MYDfileaseason filenameMakeabackupofthe.BYDfileeaseasiness filenametime.BAKFILE)-- eMaqie Mustang (whenrecreatingdatafilewhenit'sfull)-- eMaqie Mustang check check to recovereverypossiblerowfromthe datafileNormallythiswillalsofindalotoextensible garbagerows Donghutusethisoptionifyouarenottotallydesperate.After KFJ, there is no way to avoid TellMyISAMtoupdateonlysomespecifickeys.Whisabitmaskofwhichkeystouse.ThiscanbeusedfasterinsertsFlickr Maskofwhichkeystouse.ThiscanbeuswithfasterinsertsFUFEN recoverCanfixalmostanythingthingtuniquequekeysthatarenencoded tuniquequeque.Finthatarenquartertuniqueque.Finque.For the moment filewouldbeybeybig.For the parallelerecoversUesUesUamequizeeMamequizeeMUPASPUSUPULINE Slowerthan'-r'butcanhandleacoupleofcaseswhere'-r'reportsthatitcan'tfixthedatafile.--character-sets-dir=...Directorywherecharactersetsare--set-character-set=nameChangethecharactersetusedbytheindex-q,--quickFasterrepairbynotmodifyingthedatafile.Onecangiveasecond'-q'toforcemyisamchktomodifytheoriginaldatafileincaseofduplicatekeys-u,--unpackUnpackfilepackedwithmyisampack.Otheractions:-a,--analyzeAnalyzedistributionofkeys.WillmakesomejoinsinMySQLfaster.Youcancheckthecalculateddistributionbyusing'--description--verbosetable_name'.-d,--descriptionPrintssomeinformationabouttable.-A,--set-auto-increment [= value] Forceauto_incrementtostartatthisorhighervalueIfnovalueisgiven,thensetsthenextauto_incrementvaluetothehighestusedvaluefortheautokey+1.-S,--sort-indexSortindexblocks.Thisspeedsup'read-next'inapplications-R -- sort-records=#Sortrecordsaccordingtoanindex.ThismakesyourdatamuchmorelocalizedandmayspeedupthingsC:\ mysql\ bin > myisamchkc:\ mysql\ data\ hw_enterprice\ function_products.frmmyisamchk:error:'c:\ mysql\ data\ hw_enterprice\ function_products.frm'isnotaMyISAM-tableC:\ mysql\ bin > myisamchkc:\ mysql\ data\ hw_enterprice\ function_products.myiCheckingMyISAMfile:c:\ mysql\ data\ hw_enterprice\ function_products.myiDatarecords:85207Deletedblocks:39myisamchk:warning:Tableismarkedascrashedmyisamchk:warning:1clientsisusingorhasn'tclosedthetableproperly-checkfile-size-checkkeydelete-chain-checkrecorddelete-chainmyisamchk:error:recorddelete -link-chaincorrupted-checkindexreference-checkdatarecordreferencesindex:1-checkdatarecordreferencesindex:2-checkdatarecordreferencesindex:3-checkrecordlinksmyisamchk:error:Wrongbytesec:0-195-171atlinkstart:841908MyISAM-table'c:\ mysql\ data\ hw_enterprice\ function_products.myi'iscorruptedFixitusingswitch "- r" or "- o"

Continue the operation:

C:\ mysql\ bin > myisamchk--recover--quickc:\ mysql\ data\ hw_enterprice\ function_products.myi-checkkeydelete-chain-checkrecorddelete-chainmyisamchk:error:recorddelete-link-chaincorruptedmyisamchk:error:Quick-recoveraborted;Runrecoverywithoutswitch'q'UpdatingMyISAMfile:c:\ mysql\ data\ hw_enterprice\ function_products.myiMyISAM-table'c:\ mysql\ data\ hw_enterprice\ function_products.myi'isnotfixedbecauseoferrorsTryfixingitbyusingthe--safe-recover (- o) orthe--force (- f) option

I was prompted to use-- safe-recover (- o) orthe--force (- f) option for repair, so

How to repair MySQL database .frm files, .MYD files and .MYI files

After copying the repaired physical file to mysql\ data, access it through phpMyAdmin, and the OK is normal!

The data repair operation is successful, and the data has been restored normally, with a total of 85215 records, of which 85207 have been recovered.

To sum up this experience and find information, as follows:

When you try to fix a problem with a broken table, there are three types of repair. If you get an error message that a temporary file cannot be created, delete the file indicated by the message and try again-this is usually left over from the last repair operation.

The three repair methods are as follows:

% myisamchk--recover--quick/path/to/tblName%myisamchk--recover/path/to/tblName%myisamchk--safe-recover/path/to/tblName

The first is the fastest to fix the most common problems, while the last is the slowest to fix problems that cannot be fixed by other methods.

Check and repair MySQL data files

If the above method cannot repair a damaged table, you can also try the following two techniques before you give up:

If you suspect that there is an irreparable error in the table's index file (* .MYI), or even lose the file, you can use the data file (* .MYD) and the data format file (* .frm) to regenerate it. First make a copy of the data file (tblName.MYD). Restart your MySQL service and connect to it and delete the contents of the table using the following command: mysql > DELETEFROMtblName

When you delete the contents of the table, a new index file is created. Log out and shut down the service again, and then overwrite the new (empty) data file with the data file you just saved (tblName.MYD). Finally, use myisamchk to perform a standard fix (the second method above) to regenerate the index data based on the contents of the table's data and the table's format file.

If your table format file (tblName.frm) is missing or an irreparable error has occurred, but you know how to use the appropriate CREATETABLE statement to regenerate the table, you can regenerate a new .frm file and use it with your data file and index file (if there is a problem with the index file, use the above method to rebuild a new one). First make a copy of the data and index file, and then delete the original file (delete all records about the table in the data directory).

Start the MySQL service and create a new table using the original CREATETABLE file. The new .frm file should work, but you'd better perform a standard fix (the second method above).

On how to repair the MySQL database .frm file, .MYD file and .MYI file sharing here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.