MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge.

After massive CPU spikes, I created a script to OPTIMIZE all the tables, and ran it. And everything was fixed and working fine and not using nearly as much CPU. This was done through PHP. I also have a jailed shell that I can use to access the database.

The next thing I did was really stupid. I took this working database and decided to run a REPAIR operation on all the tables. For most tables the operation was successful and returned a success status, however it timed out and broke several of the tables. The remaining tables that weren't working show as 'in use' in PHPMyAdmin and when I try to perform any operation on those tables it says 'table is marked as crashed and last (automatic?) repair failed'.

A couple of the tables recovered fully by simply repeating the REPAIR operation. Over time by running REPAIR operations over and over I managed to recover most of those tables. However, there is one table which is still timing out always. I have access to run queries via PHP, PHPMyAdmin, or the jailed shell, all of which time out, as well as cPanel. The CHECK operation also times out. I am certain that the actual data is fine, and that the operations are simply being killed before they can finish by some higher-level restriction. (All of which I do not have access to change.) I'm pretty sure the data is probably even just fine and it's an in-use flag somewhere which was set because the operation didn't finish. Obviously the export function of PHPMyAdmin does not work in this scenario. What can I do to get this data off the server and attempt a proper recovery?
First, I need to ask "Does the OPTIMIZE actually help?"  I ask because it is very rare that it makes enough difference to bother with.  Please describe the activity on the table.  One case that might need the optimize is a huge amount of deletes and/or updates (with different lengths).  If the deletes are of "old" data, then we should discuss partitioning as a much better way.

What setting do you have for myisam_recover_options?  That would let the server deal with the REPAIR automatically.

You really should consider changing to InnoDB which will mostly eliminate the need for OPTIMIZE and REPAIR.  Also MyISAM is going away.

On another topic,...  Have you identified the query/queries that are involved in the CPU spike?  It could be that we can speed them up enough to avoid the problem.  Sometimes it is as simple as adding a suitable composite index.