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

PS: no input schema_name and table_name are judged in the first version. I modified it! Share again!

 

 

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

If ["$ #"-gt 2-o "$ #"-lt 1]; 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

# Set mysql evn
MYSQL_USER = system # mysql Username
MYSQL_PASS = 'Password' # mysql logon User password
MYSQL_HOST = 192.168.2.188

Judegedate_01 = "judegedate01. 'date too many yymm1_d1_hsf-m=s'.txt"

Judegedate_02 = "judegedate02. 'date parameter policyymm1_d1_h1_m1_s'.txt"

SCHEMA_NAME = $1
TABLE_NAME = $2

# Judege

SCHEMA_JUDEGE = "select schema_name from information_schema.schemata where schema_name = '$ {SCHEMA_NAME }';"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {SCHEMA_JUDEGE}" >$ {judegedate_01}

TABLE_JUDEGE = "select table_name from information_schema.tables where table_name = '$ {TABLE_NAME }';"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {TABLE_JUDEGE}" >$ {judegedate_02}

 

If ["$ #"-eq 2]; then
If [! -S "$ {judegedate_01}"]; then
Echo "************************************** **************************************"
Echo "you input schema_name $ {SCHEMA_NAME} not exits, pleae check your databases"
Echo "************************************** ***************************************"
Rm-rf $ {judegedate_01}
Rm-rf $ {judegedate_02}
Exit 0
Fi
If [! -S "$ {judegedate_02}"]; then
Echo "************************************** ***************************************"
Echo "you input table_name $ {TABLE_NAME} not exits, pleae check your databases"
Echo "************************************** ***************************************"
Rm-rf $ {judegedate_01}
Rm-rf $ {judegedate_02}
Exit 0
Fi

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 ;"
Echo "the result is :"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {SQL _CMD }"
Rm-rf $ {judegedate_01}
Rm-rf $ {judegedate_02}
Else
If [! -S "$ {judegedate_01}"]; then
Echo "************************************** ***************************************"
Echo "you input schema_name $ {SCHEMA_NAME} not exits, pleae check your databases"
Echo "************************************** ***************************************"
Rm-rf $ {judegedate_01}
Rm-rf $ {judegedate_02}
Exit 0
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 ;"
Echo "the result is :"
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "$ {SQL _CMD }"
Rm-rf $ {judegedate_01}
Rm-rf $ {judegedate_02}
Fi
Fi