# How to check database

# Check a Specific Table in a Database

If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.

The following example checks employee table in thegeekstuff database.

`# mysqlcheck -c thegeekstuff employee -u root -p`  
`Enter password:`  
`thegeekstuff.employee    OK`

# Check All Tables in a Database

To check all the tables in a particular database, don’t specify the table name. Just specify the database name.

The following example checks all the tables in the alfresco database.

```
# mysqlcheck -c alfresco  -u root -p
Enter password:
alfresco.JBPM_ACTION                               OK
alfresco.JBPM_BYTEARRAY                            OK
alfresco.JBPM_BYTEBLOCK                            OK
alfresco.JBPM_COMMENT                              OK
alfresco.JBPM_DECISIONCONDITIONS                   OK
alfresco.JBPM_DELEGATION                           OK
alfresco.JBPM_EVENT                                OK
..
```

# Check All Tables and All Databases

To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.

```
# mysqlcheck -c  -u root -p --all-databases
Enter password:
thegeekstuff.employee                              OK
alfresco.JBPM_ACTION                               OK
alfresco.JBPM_BYTEARRAY                            OK
alfresco.JBPM_BYTEBLOCK                            OK
..
..
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
..
```

If you want to check all tables of few databases, specify the database names using “–databases”.

The following example checks all the tables in thegeekstuff and alfresco database.

`# mysqlcheck -c  -u root -p --databases thegeekstuff alfresco`  
`Enter password:`  
`thegeekstuff.employee                              OK`  
`alfresco.JBPM_ACTION                               OK`  
`alfresco.JBPM_BYTEARRAY                            OK`  
`alfresco.JBPM_BYTEBLOCK                            OK`  
`..`

# Analyze Tables using Mysqlcheck

The following analyzes employee table that is located in thegeekstuff database.

`# mysqlcheck -a thegeekstuff employee -u root -p`  
`Enter password:`  
`thegeekstuff.employee   Table is already up to date`

Internally mysqlcheck command uses “ANALYZE TABLE” command. While mysqlcheck is executing the analyze command the table is locked and available for other process only in the read mode.

# Optimize Tables using Mysqlcheck

The following optimizes employee table that is located in thegeekstuff database.

`# mysqlcheck -o thegeekstuff employee -u root -p`  
`Enter password:`  
`thegeekstuff.employee         OK`

Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

# Repair Tables using Mysqlcheck

The following repairs employee table that is located in thegeekstuff database.

`# mysqlcheck -r thegeekstuff employee -u root -p`  
`Enter password:`  
`thegeekstuff.employee        OK`

Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.

# Combine Check, Optimize, and Repair Tables

Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.

The following checks, optimizes and repairs all the corrupted table in thegeekstuff database.

`# mysqlcheck -u root -p --auto-repair -c -o thegeekstuff`

You an also check, optimize and repair all the tables across all your databases using the following command.

`# mysqlcheck -u root -p --auto-repair -c -o --all-databases`

If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.

```
# mysqlcheck --debug-info -u root -p --auto-repair -c -o thegeekstuff employee
Enter password:
thegeekstuff.employee  Table is already up to date

User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 344, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12, Involuntary context switches 9
```

# Additional Useful Mysqlcheck Options

The following are some of the key options that you can use along with mysqlcheck.

```
-A, –all-databases Consider all the databases
-a, –analyze Analyze tables
-1, –all-in-1 Use one query per database with tables listed in a comma separated way
–auto-repair Repair the table automatically it if is corrupted
-c, –check Check table errors
-C, –check-only-changed Check tables that are changed since last check
-g, –check-upgrade Check for version dependent changes in the tables
-B, –databases Check more than one databases
-F, –fast Check tables that are not closed properly
–fix-db-names Fix DB names
–fix-table-names Fix table names
-f, –force Continue even when there is an error
-e, –extended Perform extended check on a table. This will take a long time to execute.
-m, –medium-check Faster than extended check option, but does most checks
-o, –optimize Optimize tables
-q, –quick Faster than medium check option
-r, –repair Fix the table corruption
```