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.

If corrupted tables remain after

mysqlcheck -A --auto-repair

try

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

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

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>

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

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!