Repair all tables in MySQL

8 September 2010

In mysql you can repair a table using:

repair table mytable;

How do you repair all the tables in a database? There’s no command for that, so you’ll need to repair each one individually.

That’s quite tedious, so here’s a bit of help in MySQL 5.

select concat('repair table ', table_name, ';') from information_schema.tables where table_schema='mydatabase';

Change mydatabase to the name of your database.

This gives you a load of repair table statements. They’re formatted in a table with vertical bars, so copy the whole lot, paste in to a text editor or word processor, do a find and replace: find “|” replace with nothing. Don’t worry about the spaces.

Copy and paste that lot in to your mysql command prompt. Job done.

Filed under: Uncategorized — Scott @ 1:08 pm