With my new job comes a significant element of MySQL DBA work. It is also opportunity to help my fellow MySQL DBAs by sharing tools, tips and tricks so expect more frequest blog posts!
Today I had to recover a from a bunch of corrupt MyISAM tables in a “cache database”. Client code will throw an exception if these tables are accessed so it is not a good idea to let corrupt tables hang around for long. The fix was in theory easy – just recreate the tables and then rename the corrupt table, rename the new table to the old and then run myisamchk on the old table.
The problem is that “create table foo as bar” does not work if the table is marked as crashed. Some Google-fu found this.
Refer to the MyISAM bit – it really does work. I wrote a bash script to automate it which you can grab here.
However, there is an even quicker way. I discovered that a simple “rename table foo to foo_corrupt” will create a fresh, clean copy of table foo. Not sure if this is intentional but thanks MySQL!
Oh, and sadly these tables cannot be converted to InnoDB – we’re using “INSERT DELAYED” and for some other reasons.