#!/bin/bash
# show mysql data size

readonly HOST=${1-"localhost"}
readonly USER=${2-"root"}
readonly PASSWD=${3-""}

main () {
	mysql -u $USER -p$PASSWD -h $HOST -e "SELECT table_schema, sum(data_length+index_length) /1024 /1024 AS MB
 FROM information_schema.TABLES GROUP BY table_schema 
 ORDER BY sum(data_length+index_length) DESC;
"

	local databases=$(mysql -u $USER -p$PASSWD -h $HOST -BNe "show databases;")
	for database in ${databases[@]};do
		if [ "$database" == "information_schema" \
                     -o "$database" == "performance_schema" \
                     -o "$database" == "mysql" \
                ]; then
			continue
		fi
		 mysql -u $USER -p$PASSWD -h $HOST -e "
SELECT  
 '${database}' as table_schema, table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen,  
 floor((data_length+index_length)/1024/1024) AS allMB,  
 floor((data_length)/1024/1024) AS dMB,  
 floor((index_length)/1024/1024) AS iMB  
 FROM information_schema.TABLES  
 WHERE table_schema=DATABASE()  
 ORDER BY (data_length+index_length) DESC;
" $database
	done
}

main