Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to run a CHECK TABLE on all my tables from the MySQL command-line.

I can do it for a single table:

mysql> check table Restaurant;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| foodbox.Restaurant | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.02 sec)

Is there an equivalent command that would check all tables in the current database?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You could simplify your life with a shell script. Here is one I created for Linux, it is based on the mysqlcheck application

This script will check all tables in all databases except mysql and information_schema

vi check_all_databases.sh

#!/bin/bash
USER=root
Port=3306
PASSWD=password

DBS="$(/usr/bin/mysql --connect_timeout 10 -u $USER -P $Port -p$PASSWD -Bse 'SHOW DATABASES')"
for db in $DBS
        do
                if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
                then
                        /usr/bin/mysqlcheck -u $USER -p$PASSWD -c $db
                fi
        done

Save the file

\wq

Then execute it

sh check_all_databases.sh

dbt2.customer                                      OK
dbt2.district                                      OK
dbt2.history                                       OK
dbt2.item                                          OK
dbt2.new_order                                     OK
dbt2.order_line                                    OK
dbt2.orders                                        OK

For Windows you would use a batch script or vbs to do the same thing.

The other option would be just to run the mysqlcheck application for the one database you are interested in checking.

/path/to/mysqlcheck -u username -pPASSWORD -c database_name

mysqlcheck will be in the directory you installed your mysql binary files in.

In Linux, you can locate it with the which command

which mysqlcheck
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.