Repair all tables in one go

How to check all the tables in the database in one go?

Rather than typing the query check table ''tablename''; for all the tables one by one.

Is there any simple command like check all or anything like that?

from command line you can use:

mysqlcheck -A --auto-repair

http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html

The command is this:

mysqlcheck -u root -p --auto-repair --check --all-databases

You must supply the password when asked,

or you can run this one but it's not recommended because the password is written in clear text:

mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --all-databases

Use following query to print REPAIR SQL statments for all tables inside a database:

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

After that copy all the queries and execute it on mydatabase.

Note: replace mydatabase with desired DB name

No need to type in the password, just use any one of these commands (self explanatory):

mysqlcheck --all-databases -a #analyze
mysqlcheck --all-databases -r #repair
mysqlcheck --all-databases -o #optimize

The following command worked for me using the command prompt (As an Administrator) in Windows:

mysqlcheck -u root -p -A --auto-repair

Run mysqlcheck with the root user, prompt for a password, check all databases, and auto-repair any corrupted tables.

Related