Practice: mysql collects statistics on the data and index size of all tables in the specified architecture.

# Count the data and index size of all tables in the specified Architecture
# Tablesize. sh
#! /Bin/sh
# Ocpyang@126.com

If ["$ #"-gt 2]; then
Echo "**********************************"
Echo "too wrote 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 when policyymm1_d1_h1_m1_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}"-! -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