[mysql] 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?

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.

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

There is no default command to do that, but you may create a procedure to do the job. It will iterate through rows of information_schema and call REPAIR TABLE 'tablename'; for every row. CHECK TABLE is not yet supported for prepared statements. Here's the example (replace MYDATABASE with your database name):

CREATE DEFINER = 'root'@'localhost'
PROCEDURE MYDATABASE.repair_all()
BEGIN
  DECLARE endloop INT DEFAULT 0;
  DECLARE tableName char(100);
  DECLARE rCursor CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE();
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endloop=1;

  OPEN rCursor;
  FETCH rCursor INTO tableName;

  WHILE endloop = 0 DO
    SET @sql = CONCAT("REPAIR TABLE `", tableName, "`");
    PREPARE statement FROM @sql;
    EXECUTE statement;

    FETCH rCursor INTO tableName;
  END WHILE;

  CLOSE rCursor;
END

If corrupted tables remain after

mysqlcheck -A --auto-repair

try

mysqlcheck -A --auto-repair --use-frm

I like this for a simple check from the shell:

mysql -p<password> -D<database> -B -e "SHOW TABLES LIKE 'User%'" \
| awk 'NR != 1 {print "CHECK TABLE "$1";"}' \
| mysql -p<password> -D<database>

You may need user name and password:

mysqlcheck -A --auto-repair -uroot -p

You will be prompted for password.

mysqlcheck -A --auto-repair -uroot -p{{password here}}

If you want to put in cron, BUT your password will be visible in plain text!

from command line you can use:

mysqlcheck -A --auto-repair

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

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

for plesk hosts, one of these should do: (both do the same)

mysqlrepair -uadmin -p$(cat /etc/psa/.psa.shadow) -A
# or
mysqlcheck -uadmin -p$(cat /etc/psa/.psa.shadow) --repair -A