I have a large table which has crashed however when I try to run a repair on the server I have problems with not enough disk space. I would like to dump this table to my local machine for a repair but mysqldump throws error because of crashed table. Is it possible to dump this table for repair locally?
3 Answers
Is this MyISAM? InnoDB?
If it's MyISAM you could try myisamcheck --safe-recover
as the docs say:
"This recovery method also uses much less disk space than --recover. Normally, you should repair first using --recover, and then with --safe-recover only if --recover fails. "
Here is the reference.
This is an old question but for the benefit of others coming here looking for the answer:
We see such question quite a lot and most people miss a really obvious solution. If you don't have enough drive space to do a repair it may be possible to temporarily create the need space by moving other files off the system or even compressing directories that won't be needed straight away. e.g. All those man pages take up a lot of room and compress really well or you might clean out the /tmp directory, archived logs, etc. In a pinch you can also dump and drop all other databases from the system (if that's practical) and restore them latter.
Clearing space may be preferable to moving corrupted database files, which may even get further screwed in the process. Sometimes it's best to think inside the box.
I solved this issue by copying down database to local machine with more drive space and running --recover
.