MySQL日常巡检

[root@localhost ~]# vim mysql_check.sh
#!/bin/bash
#set mysql evn
MYSQL_USER=root #mysql????
MYSQL_PASS='mysql' #mysql???????
MYSQL_HOST=192.168.183.128
#1.the server infomation
echo "the system basic infomation:"
echo "***********************************************************************"
echo
hostname=`hostname` #???
ipaddress=`ifconfig |grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'` #IP??
gtway01=`cat /etc/sysconfig/network|grep GATEWAY|awk -F "=" '{print $2}'` #??
gtway02=`netstat -rn | awk '/^0.0.0.0/ {print $2}'`
cpuinfo=`cat /proc/cpuinfo|grep "name"|cut -d: -f2 |awk '{print "*"$1,$2,$3,$4}'|uniq -c` #cpu
phmem=`dmidecode | grep -A 16 "Memory Device$" |grep Size:|grep -v "No Module Installed"|awk '{print "*" $2,$3}'|uniq -c` #??????
sysver=`cat /etc/issue | head -1` #--????
kerver=`uname -a |awk '{print $3}'` #????

[root@oracledb ~]# more mysql_check.sh
#!/bin/bash
#set mysql evn
MYSQL_USER=root #mysql????
MYSQL_PASS='mysql' #mysql???????
MYSQL_HOST=192.168.183.128
#1.the server infomation
echo "the system basic infomation:"
echo "***********************************************************************"
echo
hostname=`hostname` #???
ipaddress=`ifconfig |grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{print $1}'` #IP??
gtway01=`cat /etc/sysconfig/network|grep GATEWAY|awk -F "=" '{print $2}'` #??
gtway02=`netstat -rn | awk '/^0.0.0.0/ {print $2}'`
cpuinfo=`cat /proc/cpuinfo|grep "name"|cut -d: -f2 |awk '{print "*"$1,$2,$3,$4}'|uniq -c` #cpu
phmem=`dmidecode | grep -A 16 "Memory Device$" |grep Size:|grep -v "No Module Instal
led"|awk '{print "*" $2,$3}'|uniq -c` #??????
sysver=`cat /etc/issue | head -1` #--????
kerver=`uname -a |awk '{print $3}'` #????

#mem usage
mem_total=$(free -m |grep Mem|awk '{print $2}')
mem_used=$(free -m |grep Mem|awk '{print $3}')
mem_rate=`expr $mem_used/$mem_total*100|bc -l`
echo "1.1 server hostname is:" ${hostname}
echo "1.2 server ipaddree is: " ${ipaddress}
echo "1.4 server cpuinfo is: " ${cpuinfo}
echo "1.5 server Physical memory number is: " ${phmem}
echo "1.6 server version is: " ${sysver}
echo "1.7 server system kernel version is: " ${kerver}
echo "1.8 server memory usage rate is: " ${mem_rate:0:5}%
echo "1.10 server disk usage is:"
#disk usage
df -H |awk -F '/t' '{ print $1,$2,$3,$4,$5,$6}'
echo
echo "1.11 server CPU load average is:"
echo
uptime | awk 'BEGIN{print "1min, 5min, 15min"} {print $10,$11,$12}'
echo
echo "1.12 server CPU free is:"
top -b -n 1 | grep Cpu | awk '{print $5}' | cut -f 1 -d "."
echo

#2.mysql??
v_01="select @@version;"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}" >${v_02}
v_03=`cat ${v_02} | grep -v @@version`
echo "2.1 mysql runing version is: ${v_03}"
echo " "
rm -rf ${v_02}

v_01="show variables like 'port';;"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "2.2 mysql port is: ${v_03}"
echo " "
rm -rf ${v_02}

v_01="show variables like 'basedir';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "2.3 mysql basedir is: ${v_03}"
echo " "
rm -rf ${v_02}

v_01="show variables like 'datadir';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "2.4 mysql datadir is: ${v_03}"
echo " "
rm -rf ${v_02}

#3.??mysql????
mysql_processnum=`ps -ef | grep "mysql" | grep -v "grep" | wc -l`
echo "3. mysql process number is: ${mysql_processnum} "
echo " "

#4.??????mysql???
conn_01="conn01.`date +%Y%m%d%H%M%S`.txt"
conn_02="show processlist;"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${conn_02}" | grep -v Id >${conn_01}
client_conn_num=`cat ${conn_01} |wc -l`
echo "4. mysql client connect number is: ${client_conn_num}"
echo " "
rm -rf ${conn_01}

#5.QPS(?????)
qps_01="show global status like 'Questions';"
qps_re="qpsre.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qps_01}" |grep -v Variable_name /
|cut -f 2 >${qps_re}
qps_02=`cat ${qps_re}`
qps_03=`cat /proc/uptime |awk '{print $1}'`
qps_04=`awk 'BEGIN{print '${qps_02}' / '${qps_03}'}'`
echo "5. current mysql server QPS is: ${qps_04:0:5}"
echo " "
rm -rf ${qps_re}

#6.TPS(?????)
tps_01="show status where Variable_name in('Com_commit');"
tps_02="show status where Variable_name in('Com_rollback');"
tps_re01="tpsre01.`date +%Y%m%d%H%M%S`.txt"
tps_re02="tpsre02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_01}" |grep -v Variable_name /
|cut -f 2 >${tps_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_02}" |grep -v Variable_name /
|cut -f 2 >${tps_re02}
tps_03=`cat ${tps_re01}`
tps_04=`cat ${tps_re02}`
tps_sum=`awk 'BEGIN{print '${tps_03}' + '${tps_04}'}'`
tps_uptime=`cat /proc/uptime |awk '{print $1}'`
tps_avg=`awk 'BEGIN{print '${tps_sum}' / '${tps_uptime}'}'`
echo "6. current mysql server TPS is: ${tps_avg}"
echo " "
rm -rf ${tps_re01}
rm -rf ${tps_re02}

#7.key Buffer ???
#key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
kbrd_01="show status like 'Key_reads';"
kbrd_02="show status like 'Key_read_requests';"
kbrd_re01="kbrd01.`date +%Y%m%d%H%M%S`.txt"
kbrd_re02="kbrd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_01}" |grep -v Variable_name /
|cut -f 2 >${kbrd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_02}" |grep -v Variable_name /
|cut -f 2 >${kbrd_re02}
kbrd_03=`cat ${kbrd_re01}`
kbrd_04=`cat ${kbrd_re02}`
if [ "${kbrd_03}" -eq 0 ];then
echo "7.1 there is no any value!"
echo " "
else
kbrd_05=`awk 'BEGIN{print '${kbrd_03}' / '${kbrd_04}'}'`
kbrd_06=`awk 'BEGIN{print '1-${kbrd_05}'}'`
key_buffer_read_hits=`awk 'BEGIN{print '${kbrd_06}' * 100}'`
echo "7.1 current mysql key_buffer_read_hits is: ${key_buffer_read_hits:0:5}%"
echo " "
fi
rm -rf ${kbrd_re01}
rm -rf ${kbrd_re02}

#key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
kbwd_01="show status like 'Key_writes';"
kbwd_02="show status like 'Key_write_requests';"
kbwd_re01="kbwd01.`date +%Y%m%d%H%M%S`.txt"
kbwd_re02="kbwd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_01}" |grep -v Variable_name /
|cut -f 2 >${kbwd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_02}" |grep -v Variable_name /
|cut -f 2 >${kbwd_re02}
kbwd_03=`cat ${kbwd_re01}`
kbwd_04=`cat ${kbwd_re02}`
if [ "${kbwd_03}" -eq 0 ] ;then
echo "7.2 there is no any value!"
echo " "
else
kbwd_05=`awk 'BEGIN{print '${kbwd_03}' / '${kbwd_04}'}'` #shell?????????
kbwd_06=`awk 'BEGIN{print '1-${kbwd_05}'}'` #shell?????????
key_buffer_write_hits=`awk 'BEGIN{print '${kbwd_06}' * 100}'`
echo "7.2 current mysql key_buffer_write_hits is: ${key_buffer_write_hits:0:5}% "
echo " "
fi
rm -rf ${kbwd_re01}
rm -rf ${kbwd_re02}

#8.InnoDB Buffer???
#Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
innob_01="show status like 'Innodb_buffer_pool_reads';"
innob_02="show status like 'Innodb_buffer_pool_read_requests';"
innob_re01="innob_re01.`date +%Y%m%d%H%M%S`.txt"
innob_re02="innob_re02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${innob_01}" |grep -v Variable_name /
|cut -f 2 >${innob_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${innob_02}" |grep -v Variable_name /
|cut -f 2 >${innob_re02}
innob_03=`cat ${innob_re01}`
innob_04=`cat ${innob_re02}`
if [ "${innob_03}" -eq 0 ] ;then
echo "8. there is no any value!"
echo " "
else
innob_05=`awk 'BEGIN{print '${innob_03}' / '${innob_04}'}'` #shell?????????
innob_06=`awk 'BEGIN{print '1-${innob_05}'}'` #shell?????????
innodb_buffer_read_hits=`awk 'BEGIN{print '${innob_06}' * 100}'`
echo "8. current mysql Innodb_buffer_read_hits is: ${innodb_buffer_read_hits:0:5}% "
echo " "
fi
rm -rf ${innob_re01}
rm -rf ${innob_re02}

#9.Query Cache???
#Query_cache_hits =((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)
qc_01="show status like 'Qcache_hits';"
qc_02="show status like 'Qcache_inserts';"
qc_03="show status like 'Qcache_not_cached';"
qc_re01="qc_re01.`date +%Y%m%d%H%M%S`.txt"
qc_re02="qc_re02.`date +%Y%m%d%H%M%S`.txt"
qc_re03="qc_re03.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_01}" |grep -v Variable_name /
|cut -f 2 >${qc_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_02}" |grep -v Variable_name /
|cut -f 2 >${qc_re02}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${qc_03}" |grep -v Variable_name /
|cut -f 2 >${qc_re03}
qc_04=`cat ${qc_re01}`
qc_05=`cat ${qc_re02}`
qc_06=`cat ${qc_re03}`
if [ "${qc_04}" -eq 0 ] ;then
echo "9. there is no any value!"
echo " "
else
qc_07=`awk 'BEGIN{print '${qc_04}' + '${qc_05}' + '${qc_06}' }'`
qc_08=`awk 'BEGIN{print '${qc_04}'/'${qc_07}'}'`
query_cache_hits=`awk 'BEGIN{print '${qc_08}' * 100}'`
echo "9. current mysql query_cache_hits is: ${query_cache_hits:0:5}% "
echo " "
fi
rm -rf ${qc_re01}
rm -rf ${qc_re02}
rm -rf ${qc_re03}

v_01="show global status like 'Bytes_received';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "10.1 mysql Bytes_received is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'Bytes_sent';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "10.2 mysql Bytes_sent is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'uptime';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "10.3 mysql uptime is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'Aborted_clients';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "11.1 mysql Aborted_clients is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'Aborted_connects';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "11.2 mysql Aborted_connects is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'Threads_connected';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "11.3 mysql Threads_connected is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show global status like 'Max_used_connections';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "11.4 mysql Max_used_connections is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show variables like 'max_connections';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "11.5 mysql max_connections is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show status like 'Table_locks_immediate';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "12.1 mysql Table_locks_immediate is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="show status like 'Table_locks_waited';"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}"|grep -v Variable_name|cut -f 2 >${v_02}
v_03=`cat ${v_02}`
echo "12.2 mysql Table_locks_waited is: ${v_03} "
echo " "
rm -rf ${v_02}

v_01="select t.TABLE_SCHEMA ,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES t group by t.TABLE_SCHEMA
union all
select 'alldatabase' ,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES; "
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}" >${v_02}
v_03=`cat ${v_02}`
echo "12.2 mysql database usage : ${v_03} "
echo " "
rm -rf ${v_02}

v_01="select t.TABLE_SCHEMA ,t.TABLE_NAME, round(sum(DATA_LENGTH/1024/1024),2) as data from information_schema.TABLES t group by t.TABLE_SCHEMA,t.TABLE_NAME having round(sum(DATA_LENGTH/1024/1024),2)>1024;"
v_02="v02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${v_01}" >${v_02}
v_03=`cat ${v_02}`
echo "12.2 mysql tables usage is: ${v_03} "
echo " "
rm -rf ${v_02}