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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If corrupted tables remain after   mysqlcheck -A --auto-repair   try   mysqlcheck -A --auto-repair --use-frm

User · Answer

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

User · Answer

I like this for a simple check from the shell   mysql -p lt password gt  -D lt database gt  -B -e  SHOW TABLES LIKE  User        awk  NR    1  print  CHECK TABLE   1          mysql -p lt password gt  -D lt database gt

User · Answer

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

User · Answer

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

User · Answer

from command line you can use   mysqlcheck -A --auto-repair   http   dev mysql com doc refman 5 0 en mysqlcheck html

[mysql] Repair all tables in one go

Examples related to mysql

Examples related to sql