Repair all tables in one go

Question

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?

Headmistress

Answer 1

from command line you can use:

mysqlcheck -A --auto-repair

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

Allseed

Answer 2

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

Fervid

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases Error: mysqlcheck doesn't support multiple contradicting commands – Jugular

If you get the contradicting commands error, take out the --optimize option. – Toffeenosed

i guess you have to use one and only one of these options: auto-repair, check or optimize. I used auto-repair only and worked – Hetman

I tried what you said but I get: mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect and I know I am using the correct password. – Maturate

Answer 3

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

Facet

Answer 4

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

Warrantee

Answer 5

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.

Shrum

Answer 6

If corrupted tables remain after

mysqlcheck -A --auto-repair

try

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

Generally

what does the -use-frm do? – Henrietta

--use-frm For repair operations on MyISAM tables, get the table structure from the data dictionary so that the table can be repaired even if the .MYI header is corrupted. (cf. dev.mysql.com/doc/refman/8.0/en/… ) – Generally

Answer 7

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

Leduc

Answer 8

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>

Tjon

you can use mysql -ss to make column names omitted from output - this would allow to remove NR != 1 from your code – Akers

Answer 9

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

Jolly

Answer 10

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!

Jaeger