mysql的基本信息采集脚本cmdb_collection_linux_mysqlinfo.sh

#!/bin/bash
#IT_BEGIN
#IT_TYPE=1
#原型指标
#IT DATABASE_MYSQL_HOSTNAME|hostname
#IT DATABASE_MYSQL_IPADDRESS|ip
#IT DATABASE_MYSQL_PSDIR|PSDIR
#IT DATABASE_SPACE_MYSQLPORT|port
#IT DATABASE_MYSQL_user|user
#IT DATABASE_MYSQL_Basedir|Basedir
#IT DATABASE_MYSQL_version|version
#IT DATABASE_MYSQL_Datadir|Datadir
#IT_END
shname=`basename $0`
ATTR="_X(g=$shname,p=cmdb,t=script,f=0)"
mysql_user="root"
mysql_passwd="123456"
temp_user=`mysql -u${mysql_user} -p${mysql_passwd} -e ' use mysql ;select user,host  from  user where user!="";'  2>/dev/null |grep -v "|" | grep -v "^user" |grep -v "^ "   >   /tmp/mysql_user.log`

user_num=`mysql -u${mysql_user}  -p${mysql_passwd} -e ' use mysql ;select user,host  from  user where user!="";' 2>/dev/null |grep -v "|" | grep -v "^user" |grep -v "^ " |wc -l`
if [ $user_num -eq 0 ] ; then
   user_num=1
fi

#多指标输出开始
echo COL_DETAIL_START:
#输出脚本类型
echo "type[$ATTR]|+|mysql"
for ((i=1;i<=${user_num};i++));
do

mysql_user1=`cat /tmp/mysql_user.log | sed -n ''$i'p' |awk '{print $1}'`

mysql_host=`cat /tmp/mysql_user.log | sed -n ''$i'p' |awk '{print $2}'`

hostname=`hostname -s`

#服务ip
if command -v ifconfig >/dev/null 2>&1; then
    ip=`ifconfig -a | grep inet |grep -v 127.0.0.1 | grep -v "broadcast 0.0.0.0" | grep -v inet6 |awk '{print $2}' |cut -d ':' -f 2 | tr "\n" "," |sed 's/.$//g'`
elif command -v ip >/dev/null 2>&1; then
   ip=`ip addr show| grep inet |grep -v 127.0.0.1 | grep -v "broadcast 0.0.0.0" | grep -v inet6 |awk '{print $2}' |cut -d '/' -f 1 | tr "\n" " " |sed 's/.$//g'`
elif command -v hostname >/dev/null 2>&1; then
    ip=`hostname -I|tr " " ","| awk '{print substr($0, 1, length($0)-1)}'`
else
    echo "command ifconfig or ip or hostname not exist!!!"
fi
#生产主IP
primaryip=`ifconfig -a | grep "inet" | grep -v "inet6" | awk '{print $2}' | awk -F '/' '{print $1}' | grep -v 127.0.0.1 | grep -v 172.17.0.1 | grep -v 25.17 |grep -v 192.168 | head -1 | tr -d "addr:"`


#进程
Ps_num=`ps -ef |grep -w "mysql " |grep -v "grep" |awk '{print $2}'`
mysqlinfo=`ps -ef |grep -w "mysql " |grep -v grep`
if [[ $mysalinfo == *"--defaults-file"* ]]; then
 confpath=$(echo "$mysqlinfo"|awk 'BEGIN{FS=" "} {for(i=1;i<=NF;i++){if ($i ~ /--defaults-file=/ ){print substr($i,index($i,"=") + 1)}}}')
 Conf_dir=$confpath
else
#配置文件
Conf_dir="/etc/my.cnf"
fi
Get(){
   cat ${Conf_dir} | sed -n '/^[ ]*#/!p' | grep -w "^$1" | cut -d "="  -f 2  |sed 's/"//g' |sed 's/ //g'
}

#端口
port=`Get port | sed -n '1p'`

#启动用户
user=`ps -ef |grep -w "mysql " |grep -v "grep" |awk '{print $1}'`

#进程位置
Ps_dir=`ps -ef |grep -w "mysql " |grep -v "grep" |awk '{print $8}'`

#安装位置
if [[ $mysqlinfo == *"--basedir"* ]];then
 basedir=$(echo "$mysqlinfo"|awk 'BEGIN{FS=" "} {for(i=1;i<=NF;i++){if ($i ~ /--basedir=/ ){print substr($i,index($i,"=") + 1)}}}')
 Base_dir=$basedir
else 
 Base_dir=`Get basedir `
fi
#版本
#mysql_version=`mysql --help | grep Distrib | awk -F ' ' '{print $5}' | sed s'/.$//'`
mysql_version=`mysql --version|awk '{print $3}'`
#数据目录
if [[ $mysqlinfo == *"--datadir"* ]];then
  datadir=$(echo $mysqlinfo|awk 'BEGIN{FS=" "} {for(i=1;i<=NF;i++){if ($i ~ /--basedir=/ ){print substr($i,index($i,"=") + 1)}}}')
  mysql_datadir=$datadir
else
  mysql_datadir=`Get datadir`
fi
#数据库大小
Data_length=`mysql -u${mysql_user} -p${mysql_passwd} -e 'use information_schema; select concat(round(sum(DATA_LENGTH/1024/1024/1024),2)) as data from TABLES;' 2>/dev/null |grep -v "|" | grep -v "^data" |grep -v "^ " `
echo "hostname[$ATTR]|+|$hostname"
echo "ip[$ATTR]|+|$ip"
echo "port[$ATTR]|+|$port"
echo "user[$ATTR]|+|$user"
echo "PSDIR[$ATTR]|+|$Ps_dir"
echo "Basedir[$ATTR]|+|$Base_dir"
echo "version[$ATTR]|+|$mysql_version"
echo "Datadir[$ATTR]|+|$mysql_datadir"
done | sed '$ s/,//g'
#多指标输出结束
echo COL_DETAIL_END:

运行结果如下:(10.14.1.4服务器运行)

COL_DETAIL_START:
type[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|mysql
hostname[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|dmzneo4j02
ip[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|10.14.1.4
port[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|13306
user[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|mysql
PSDIR[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|/home/ntciom/mysql/bin/mysqld
Basedir[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|/home/ntciom/mysql
version[_X(g=mysql.sh,p=cmdb,t=script,f=0)]|+|8.0.37
Datadir[_X(g=mysql.shp=cmdbt=scriptf=0)]|+|/home/ntciom/mysql
COL_DETAIL_END: