1

Our server has a large MyISAM MySQL table that is no longer needed and is currently crashed. If we repair it then it greatly slows down our server consuming sources and it's almost 50GB so the process will take a very long time.

Can we safely delete the .myd and .myi files for the table manually? What would be the proper procedure for this to not affect the other tables. Would MySQL simply need to be restarted after we delete the files or are there more steps to this?

Thank you!

migrated from stackoverflow.com Feb 21 '15 at 4:32

This question came from our site for professional and enthusiast programmers.

2

From my experience with MYISAM (I am still trying to find documentation that supports it), it is safe to manually delete all three table files (.MYD, .MYI, .frm) and that's it - the table is dropped.

After doing that on a test table, MySQL could not find it anymore (SELECT * FROM test returned an error saying the table does not exist), and I was able to CREATE TABLE test again, so it seems that nothing remains from the original table.

I would recommend the following:

  1. Delete the three files.
  2. Try dropping table table again (this will probably return an error, hopefully 'table does not exist'.

Good luck.

1

I would suggest to backup the table or if you have a nightly backup use that and take a copy of the files on the file system first before doing anything just to be safe and then drop the table and if there are any residual files relating to that table on the file system then you can remove these also.

If you tried to repair the table via the REPAIR command I would suggest trying the commandline function myisamchk.

Try this:

myisamchk -r -q table_name

And if that doesn't work then try this:

myisamchk -r table_name

The -r flag will kick off the recover function and the -q will make it quicker by only looking at the index file and not the data file.

  • thanks for the feedback! We can't afford to have mysql shutdown during this process so I think we might need to do that in order to run myisamchk. We also don't care about the data in the table as we've already copied it over to another table. When attempting to truncate or drop the crashed MyISAM table - we are getting an error saying that the table is crashed. Is there a way to do this by manually deleting the .myd and .myi files? – yoloman Jan 30 '15 at 10:46
1
  1. Manually remove the .MYD and .MYI files;
  2. truncate table XXX;
  3. repair table XXX USE_FRM;

That's what I do last week to delete a large table which is useless for me.

In my case, the XXX table occupied all the storage that MySQL cannot even start, so the remove is done at offline time.

EDIT: also works for me with MySQL online.

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.