find-corrupt-mysql-table.sh

#!/bin/bash

# find-corrupt-mysql-table.sh
# Check corrupt table @ MySQL

# InnoDB tables sometime does not show up as errors with mysqlcheck,
# yet when you try to SELECT, it will give message :
# ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

# This script will check every available table & find all with such problem

# one-liner version
###rm result.txt ; databases=( `mysql -e "show databases"` ) ; for d in "${databases[@]}" ; do tables=( `mysql -e "use $d ; show tables"` ) ; for t in "${tables[@]}"  ; do printf "\n DATABASE: $d ; TABLE: $t \n" >> result.txt ;  if [[ "$d" == "information_"* ]] ; then sleep 0 ; else if mysql -e "select * from $d.$t limit 1"  |& tee -a result.txt | grep -q "ERROR" ; then sleep 2 ; fi ; fi ; done ; done ;

##### START #####################

omitted=information_

# clean up
rm result.txt

databases=( `mysql -e "show databases"` )

for d in "${databases[@]}"
do

    tables=( `mysql -e "use $d ; show tables"` )

    for t in "${tables[@]}"
    do
        printf "\n DATABASE: $d ; TABLE: $t \n" >> result.txt

        if [[ "$d" == "$omitted"* ]] ; then

            # skip this database
            sleep 0
        else

            # get the output into result.txt, and also
            # to stdout, so we can grep / check it for error message
            if mysql -e "select * from $d.$t limit 1"  |& tee -a result.txt | grep -q "ERROR" ; then

                # if we encountered a problematic table
                # we need to stop for a while
                # to give time to MySQL to recover
                # if we continue anyway, we won't be able to connect to MySQL

                sleep 2
            fi

        fi
    done

done