上代码:
#!/bin/bash
#author: xiao白
#date: 2015-11-11
#qq: 530035210
#blog: http://my.oschina.net/pwd/blog
#查询数据库以及数据库表的大小
logdir=/data/log/shell #日志路径
log=$logdir/log.log #日志文件
is_font=1 #终端是否打印日志: 1打印 0不打印
is_log=1 #是否记录日志: 1记录 0不记录
datef(){
date "+%Y-%m-%d %H:%M:%S"
}
print_log(){
if [[ $is_log -eq 1 ]];then
[[ -d $logdir ]] || mkdir -p $logdir
echo -e "[ $(datef) ] $1" >> $log
fi
if [[ $is_font -eq 1 ]];then
echo -e "[ $(datef) ] $1"
fi
}
random=$(date "+%s")
getDbSize(){
if [[ ${#dbinfo[@]} -eq 0 ]];then
print_log "$FUNCNAME():参数不能为空,退出函数"
return
fi
dbhost=${dbinfo[0]}
dbuser=${dbinfo[1]}
dbpasswd=${dbinfo[2]}
dbname=${dbinfo[3]}
if [[ $dbname == "" ]];then
dbs=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e ‘show databases;‘ |grep -v Database > /tmp/dbs_$random.txt )
print_log "开始检测->数据库IP:$dbhost"
print_log "数据库名:数据库大小"
while read line
do
db_size=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use information_schema ;select concat(round(sum(DATA_LENGTH/1024/1024),2),‘MB‘) as data from TABLES where table_schema=‘$line‘ \G;
"|grep data |awk ‘{print $2}‘)
echo "$line $db_size" >>/tmp/dbsize_$random.txt
done < /tmp/dbs_$random.txt
cat /tmp/dbsize_$random.txt|sort -n -k 2 -r |column -t > /tmp/dbstring_$random.txt
print_log "\n`cat /tmp/dbsize_$random.txt|sort -n -k 2 -r |column -t |sed "s/^/\\t\\t\\t/"`"
totalStr=$(cat /tmp/dbstring_$random.txt |grep "M" |grep -v "0.00" |awk ‘{print $2}‘ |sed "s/MB//g" |xargs |sed "s/ / + /g")
sum=$(echo "$totalStr" |bc)
print_log "[$dbhost]总数据库的大小:$sum MB"
else
db_size=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use information_schema ;select concat(round(sum(DATA_LENGTH/1024/1024),2),‘MB‘) as data from TABLES where table_schema=‘$dbname‘ \G;
"|grep data |awk ‘{print $2}‘)
print_log "开始检测->数据库IP:$dbhost"
print_log "数据库名:数据库大小"
print_log "\n\t\t\t$dbname:$db_size"
fi
rm -f /tmp/dbs_$random.txt /tmp/dbsize_$random.txt
}
getTableSize(){
if [[ ${#dbinfo[@]} -eq 0 ]];then
print_log "$FUNCNAME():参数不能为空,退出函数"
return
fi
dbhost=${dbinfo[0]}
dbuser=${dbinfo[1]}
dbpasswd=${dbinfo[2]}
dbname=${dbinfo[3]}
if [[ $dbname == "" ]];then
cat /tmp/dbstring_$random.txt |grep "M" |grep -v "0.00" |awk ‘{print $1}‘ > /tmp/databases_$random.txt
print_log "排除空数据,开始检测以下数据的大小: `cat /tmp/databases_$random.txt |xargs`"
dbs=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e ‘show databases;‘ |grep -v Database > /tmp/dbs_$random.txt )
print_log "数据库名:数据库大小"
while read line
do
table_list=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use $line ;show tables \G ;" |grep "Tables_in" |awk ‘{print $2}‘)
db_size=$(cat /tmp/dbstring_$random.txt |grep "M" |grep -v "0.00" |grep "^$line " |awk ‘{print $2}‘)
print_log "数据库[$line]->数据库IP:$dbhost 数据库大小:$db_size 表的数量为:`echo "$table_list"|sed "s/ /\n/g" |wc -l`个"
print_log "表名 表空间大小 表索引大小"
for i in $table_list
do
tablename="$i"
table_size=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use information_schema;select concat(round(sum(data_length/1024/1024),2),‘MB‘) as data_length_MB, concat(round(sum(index_length/1024/1024),2),‘MB‘) as index_length_MB from tables where table_schema=‘$line‘ and table_name = ‘$tablename‘ \G; " |grep "length_MB" | awk ‘{print $2}‘ |xargs )
echo "$tablename $table_size" >> /tmp/$random.txt
done
print_log "\n`cat /tmp/$random.txt|sort -n -k 2 -r |column -t|sed "s/^/\\t\\t\\t/"`"
rm -f /tmp/$random.txt
done < /tmp/databases_$random.txt
rm -f /tmp/databases_$random.txt /tmp/dbstring_$random.txt
else
table_list=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use $dbname ;show tables \G ;" |grep "Tables_in" |awk ‘{print $2}‘)
print_log "数据库[$line]->数据库IP:$dbhost 表的数量为:`echo "$table_list"|sed "s/ /\n/g" |wc -l`个"
print_log "表名 表空间大小 表索引大小"
for i in $table_list
do
tablename="$i"
table_size=$(mysql -h $dbhost -u$dbuser -p"$dbpasswd" -e "use information_schema;select concat(round(sum(data_length/1024/1024),2),‘MB‘) as data_length_MB, concat(round(sum(index_length/1024/1024),2),‘MB‘) as index_length_MB from tables where table_schema=‘$dbname‘ and table_name = ‘$tablename‘ \G; " |grep "length_MB" | awk ‘{print $2}‘ |xargs )
echo "$tablename $table_size" >> /tmp/$random.txt
done
print_log "\n`cat /tmp/$random.txt|sort -n -k 2 -r |column -t |sed "s/^/\\t\\t\\t/"` "
rm -f /tmp/$random.txt
fi
}
dbinfo=("localhost" "root" "password" "")
getDbSize
getTableSize
当dbinfo第4个参数为空默认检测整个数据库的数据库以及数据库表大小:
当第四个参数为具体的数据库时:
原文：http://my.oschina.net/pwd/blog/529009