sufehmi

Count number of records from all databases - CSV compatible

Sep 28th, 2019
141
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/bash
  2.  
  3. # This script will count the number of records in all tables  
  4. # in all VAR_DATABASE_NAME* (note the wildcard at the end)
  5. # with the result in just one line
  6. # in CSV-compatible layout
  7.  
  8. ## one-liner
  9. ## databases=( `ls /var/lib/mysql/ `) ;for d in "${databases[@]}" ; do tables=( `mysql -e "use $d ; show tables"` ) ; for t in "${tables[@]}"  ; do if [[ "$d" == ""* ]]  ; then   mysql -s -r -e "SELECT CONCAT('$d',',','$t',',', count(*)) from $d.$t  " 2> /dev/null | grep -v CONCAT >> all.csv ; fi ; done ; done ;
  10.  
  11.  
  12. ## sample result :
  13. ## my_table_name,74883
  14.  
  15. VAR_DATABASE_NAME=mydb_
  16.  
  17. databases=( `ls /var/lib/mysql/ `)
  18.  
  19. for d in "${databases[@]}" ; do
  20.  
  21.   if [[ "$d" == "$VAR_DATABASE_NAME"* ]]  ; then
  22.  
  23.     for d in "${databases[@]}"
  24.     do
  25.  
  26.       tables=( `mysql -e "use $d ; show tables"` )
  27.  
  28.       for t in "${tables[@]}"  
  29.       do
  30.    
  31.         # the CONCAT() function gather the results in one line
  32.         # then we exclude the CONCAT statement itself from the output using grep -v
  33.  
  34.         mysql -s -r -e "SELECT CONCAT('$d',',','$t',',', count(*)) from $d.$t  " 2> /dev/null | grep -v CONCAT >> all.csv
  35.  
  36.       done
  37.  
  38.   fi
  39.  
  40. done
RAW Paste Data