#!/bin/bash
# This script will count the number of records in all tables
# in all VAR_DATABASE_NAME* (note the wildcard at the end)
# with the result in just one line
# in CSV-compatible layout
## one-liner
## 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 ;
## sample result :
## my_table_name,74883
VAR_DATABASE_NAME=mydb_
databases=( `ls /var/lib/mysql/ `)
for d in "${databases[@]}" ; do
if [[ "$d" == "$VAR_DATABASE_NAME"* ]] ; then
for d in "${databases[@]}"
do
tables=( `mysql -e "use $d ; show tables"` )
for t in "${tables[@]}"
do
# the CONCAT() function gather the results in one line
# then we exclude the CONCAT statement itself from the output using grep -v
mysql -s -r -e "SELECT CONCAT('$d',',','$t',',', count(*)) from $d.$t " 2> /dev/null | grep -v CONCAT >> all.csv
done
fi
done
Comments