3

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?

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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