sufehmi

find-corrupt-mysql-table.sh

Nov 2nd, 2019
50
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/bash
  2.  
  3. # find-corrupt-mysql-table.sh
  4. # Check corrupt table @ MySQL
  5.  
  6. # InnoDB tables sometime does not show up as errors with mysqlcheck,
  7. # yet when you try to SELECT, it will give message :
  8. # ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
  9.  
  10. # This script will check every available table & find all with such problem
  11.  
  12. # one-liner version
  13. ###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 ;
  14.  
  15. ##### START #####################
  16.  
  17. omitted=information_
  18.  
  19. # clean up
  20. rm result.txt
  21.  
  22. databases=( `mysql -e "show databases"` )
  23.  
  24. for d in "${databases[@]}"
  25. do
  26.  
  27.     tables=( `mysql -e "use $d ; show tables"` )
  28.  
  29.     for t in "${tables[@]}"  
  30.     do
  31.         printf "\n DATABASE: $d ; TABLE: $t \n" >> result.txt
  32.  
  33.         if [[ "$d" == "$omitted"* ]] ; then
  34.    
  35.             # skip this database
  36.             sleep 0
  37.         else
  38.  
  39.             # get the output into result.txt, and also
  40.             # to stdout, so we can grep / check it for error message
  41.             if mysql -e "select * from $d.$t limit 1"  |& tee -a result.txt | grep -q "ERROR" ; then
  42.  
  43.                 # if we encountered a problematic table
  44.                 # we need to stop for a while
  45.                 # to give time to MySQL to recover
  46.                 # if we continue anyway, we won't be able to connect to MySQL
  47.  
  48.                 sleep 2
  49.             fi
  50.  
  51.         fi
  52.     done
  53.  
  54. done
RAW Paste Data