为方便统计mysql实例里的数据库数据大小，以及索引占用的空间大小，故设计该脚本。
一、文件名与说明：
1、.db_bak.tmp：执行脚本时生成的单个实例的数据路列表临时文件
2、db_exec.conf：数据库实例的IP与端口配置列表
3、db_exec.sh：主脚本
4、db_space.txt：执行后生成的输出文件
二、使用方法：
将需要检查的mysql实例的IP与端口填入到db_exec.conf，再运行db_exec.sh脚本即可。
1、db_exec.conf配置文件内容：
ISD_28_21_sles10_64:/data/apps/monitor/tlogdb_space # cat db_exec.conf
###############################
1.2.87.100:1085
1.2.87.100:1084
1.2.87.100:1105
###############################
执行db_exec.sh脚本后得到的结果
ISD_28_21_sles10_64:/data/apps/monitor/tlogdb_space # cat db_space.txt
###############################
Mon Feb 24 14:25:05 CST 2014
DB_HOST DB_PORT DB_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DB_SPACE
1.2.87.100 1085 db_zlsg_logdb_1082 4493.9150K 688.1250M 1619.4844M 2307.6094M
1.2.87.100 1085 db_zlsg_logdb_1083 5084.5000K 763.5625M 1743.5938M 2507.1563M
1.2.87.100 1085 db_zlsg_logdb_1084 7102.5740K 1010.7031M 2164.7031M 3175.4063M
1.2.87.100 1085 db_zlsg_logdb_11 82361.4150K 10094.7188M 16668.0938M 26762.8125M
1.2.87.100 1085 db_zlsg_logdb_24 39392.0950K 4773.1563M 8180.9688M 12954.1250M
1.2.87.100 1085 db_zlsg_logdb_664 10860.8940K 1424.7031M 2819.1875M 4243.8906M
1.2.87.100 1085 db_zlsg_logdb_872 911.8250K 208.6250M 811.5469M 1020.1719M
1.2.87.100 1085 db_zlsg_logdb_873 1384.8600K 273.0469M 918.4219M 1191.4688M
1.2.87.100 1085 db_zlsg_logdb_874 6880.1040K 956.7969M 2085.0469M 3041.8438M
1.2.87.100 1085 db_zlsg_logdb_923 2403.5670K 411.8281M 1189.8750M 1601.7031M
1.2.87.100 1085 db_zlsg_logdb_924 3389.8610K 534.2188M 1376.4219M 1910.6406M
1.2.87.100 1085 db_zlsg_logdb_925 3911.1940K 593.6875M 1458.4688M 2052.1563M
1.2.87.100 1084 db_zlsg_logdb_104 280354.5190K 34192.5000M 60178.2656M 94370.7656M
1.2.87.100 1084 db_zlsg_logdb_1079 8166.3120K 1136.4844M 2252.0000M 3388.4844M
1.2.87.100 1084 db_zlsg_logdb_1080 5040.1350K 735.4688M 1613.0000M 2348.4688M
1.2.87.100 1084 db_zlsg_logdb_1081 8107.3190K 1125.3125M 2250.3281M 3375.6406M
1.2.87.100 1084 db_zlsg_logdb_1085 7310.8310K 1019.8281M 2101.3594M 3121.1875M
1.2.87.100 1084 db_zlsg_logdb_1086 6033.0190K 855.6250M 1814.9531M 2670.5781M
1.2.87.100 1084 db_zlsg_logdb_1087 4081.9930K 615.0625M 1400.3594M 2015.4219M
1.2.87.100 1084 db_zlsg_logdb_1142 6.0860K 94.3594M 718.7500M 813.1094M
1.2.87.100 1084 db_zlsg_logdb_1143 6.0860K 94.3594M 718.7500M 813.1094M
1.2.87.100 1084 db_zlsg_logdb_1144 6.0860K 94.3594M 718.7500M 813.1094M
1.2.87.100 1084 db_zlsg_logdb_616 6170.8090K 860.5469M 1839.2656M 2699.8125M
1.2.87.100 1084 db_zlsg_logdb_625 10606.1840K 1351.8281M 2595.6719M 3947.5000M
###############################
三、db_exec.sh脚本内容：
ISD_28_21_sles10_64:/data/apps/monitor/tlogdb_space # cat db_exec.sh
###############################
#!/bin/sh
export PATH=/bin:/sbin:/usr/bin:/usr/sbin:/data/apps/mysql/bin:$PATH
IF(){
if [ $? -eq 0 ]
then
echo “$DATE” >> $OUT_LOGS
cat $TMP_LOGS >> $OUT_LOGS
sleep 1
else
echo “$DATE” >> $ERR_LOGS
cat $TMP_LOGS >> $ERR_LOGS
sleep 1
continue
fi
}
ROOT=`dirname $0`
DB_LIST=$ROOT/.db_bak.tmp
DATE=`date +%Y-%m-%d-%H-%M`
LOG_DIR=/data/logs/monitor
ERR_LOGS=$LOG_DIR/bak_err.log
OUT_LOGS=$LOG_DIR/bak_out.log
TMP_LOGS=/tmp/.db_bak.tmp
DB_SERVER=$ROOT/db_exec.conf
DB_SPACE=${ROOT}/db_space.txt
PASSWD=’数据库root密码’
[ -f ${ROOT}/db_bak_ip.conf ] && . ${ROOT}/db_bak_ip.conf
[ -f /etc/sysconfig/network-scripts/ifcfg-eth0 ] && . /etc/sysconfig/network-scripts/ifcfg-eth0
[ -f /etc/sysconfig/network/ifcfg-eth0 ] && . /etc/sysconfig/network/ifcfg-eth0
[ -f ${DB_SPACE} ] && echo “${DATE}” > ${DB_SPACE} && echo ‘DB_HOST DB_PORT DB_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DB_SPACE’ >> ${DB_SPACE}
#mkdir -p $DB_BAK_ROOT
#rm $OLD_DB -rf 2>$TMP_LOGS
for SERVER in `cat $DB_SERVER|egrep -v ^#`
do
IP=`echo $SERVER|awk -F’:’ ‘{print $1}’`
PORT=`echo $SERVER|awk -F’:’ ‘{print $2}’`
MYSQL=”/data/apps/mysql/bin/mysql -uroot -h${IP} -P${PORT} -p${PASSWD} –default-character-set=utf8″
$MYSQL -e ‘show databases’ |egrep -v ‘information_schema|Database|test|mysql’ > $ROOT/.db_bak.tmp
echo ” >> ${DB_SPACE}
echo “${SERVER}” >> ${DB_SPACE}
for DBNAME in `cat $DB_LIST`
do
#数据库大小
TOTAL_SPACE=`${MYSQL} -e “use information_schema;SELECT concat(sum(table_rows/1000),’K’) AS ‘Number of Rows’ ,concat(sum(data_length/(1024*1024)),’M’) AS ‘Data Size’ ,concat(sum(index_length/(1024*1024)),’M’) AS ‘Index Size’,concat(sum((data_length+index_length)/(1024*1024)),’M’) AS ‘Total’ FROM information_schema.TABLES WHERE table_schema = ‘${DBNAME}’;”|tail -n1`
echo “${IP} ${PORT} ${DBNAME} ${TOTAL_SPACE}” >> ${DB_SPACE}
IF
done
done >> ${ROOT}/db.log
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2018-8-15 15:34:14 |
赞(
操作
