#统计指定架构的所有表的数据和索引大小情况 #tablesize.sh #!/bin/sh #ocpyang@126.com
if [ "$#" -gt 2 ];then echo "**********************************" echo "too many input parameters" echo "**********************************" echo "USAGE01: $0 schema_name table_name" echo "eg01: $0 wind t1" echo "USAGE02: $0 schema_name " echo "eg02: $0 wind " exit 1; fi
source /usr/local/ mysql /scripts/mysql_env.ini logfiledate=tmp.`date +%Y%m%d%H%M%S`.txt
SCHEMA_NAME=$1 TABLE_NAME=$2
if [ "$#" -eq 2 ];then SQL_CMD="select table_schema, table_name,table_rows, round(sum(data_length+index_length)/1024/1024) as total_MB, round(sum(data_length)/1024/1024) as data_MB, round(sum(index_length)/1024/1024) as index_MB from information_schema.tables where table_type='BASE TABLE' and table_schema='${SCHEMA_NAME}' and table_name='${TABLE_NAME}' group by table_schema, table_name,table_rows;" else SQL_CMD="select table_schema, table_name,table_rows, round(sum(data_length+index_length)/1024/1024) as total_MB, round(sum(data_length)/1024/1024) as data_MB, round(sum(index_length)/1024/1024) as index_MB from information_schema.tables where table_type='BASE TABLE' and table_schema='${SCHEMA_NAME}' group by table_schema, table_name,table_rows;" fi
SCHEMA_JUDEGE="select * from information_schema.schemata where schema_name='${SCHEMA_NAME}';" mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${SCHEMA_JUDEGE}" >${logfiledate}
if [ -e "${logfiledate}" -a ! -s "${logfiledate}" ];then echo "you input ${SCHEMA_NAME} not exits,pleae check your databases" rm -rf ${logfiledate} else echo "the result is :" mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${SQL_CMD}" rm -rf ${logfiledate} fi
查看更多关于实战：mysql统计指定架构的所有表的数据和索引大的详细内容...