Mysql inspection script V1_MySQL

! Binshocpyang @ 126comexportblack033 [0mexportboldblack033 [1; 0mexportred033 [31mexportboldred033 [1; 31mexportgreen033 [32mexportboldgreen0 #! /Bin/sh
# Ocpyang@126.com

Export black = '\ 033 [0m'
Export boldblack = '\ 033 [1; 0m'
Export red = '\ 033 [31m'
Export boldred = '\ 033 [1; 31m'
Export green = '\ 033 [32m'
Export boldgreen = '\ 033 [1; 32m'
Export yellow = '\ 033 [33m'
Export boldyellow = '\ 033 [1; 33m'
Export blue = '\ 033 [34m'
Export boldblue = '\ 033 [1; 34m'
Export magenta = '\ 033 [35m'
Export boldmagenta = '\ 033 [1; 35m'
Export cyan = '\ 033 [36m'
Export boldcyan = '\ 033 [1; 36m'
Export white = '\ 033 [37m'
Export boldwhite = '\ 033 [1; 37m'

Cecho ()

# -- Function to easliy print colored text --##

# Color-echo.
# Parameter $1 = message
# Parameter $2 = color
{
Local default_msg = "No message passed ."

Message =$ {1:-$ default_msg} # if $1 is not input, the default value is default_msg.
Color = $ {2:-black} # if $1 is not input, the default value is black.

Case $ color in
Black)
Printf "$ black ";;
Boldblack)
Printf "$ boldblack ";;
Red)
Printf "$ red ";;
Boldred)
Printf "$ boldred ";;
Green)
Printf "$ green ";;
Boldgreen)
Printf "$ boldgreen ";;
Yellow)
Printf "$ yellow ";;
Boldyellow)
Printf "$ boldyellow ";;
Blue)
Printf "$ blue ";;
Boldblue)
Printf "$ boldblue ";;
Magenta)
Printf "$ magenta ";;
Boldmagenta)
Printf "$ boldmagenta ";;
Cyan)
Printf "$ cyan ";;
Boldcyan)
Printf "$ boldcyan ";;
White)
Printf "$ white ";;
Boldwhite)
Printf "$ boldwhite ";;
Esac
Printf "% s \ n" "$ message"
Tput sgr0 # tput sgr0 restores the default value
Printf "$ black"

Return
}

Cechon ()

# Color-echo.
# Parameter 1 $1 = message
# Parameter 2 $2 = color
{
Local default_msg = "No message passed ."
# DOESN'T really need to be a local variable.

Message =$ {1:-$ default_msg} # if $1 is not input, the default value is default_msg.
Color = $ {2:-black} # if $1 is not input, the default value is black.

Case $ color in
Black)
Printf "$ black ";;
Boldblack)
Printf "$ boldblack ";;
Red)
Printf "$ red ";;
Boldred)
Printf "$ boldred ";;
Green)
Printf "$ green ";;
Boldgreen)
Printf "$ boldgreen ";;
Yellow)
Printf "$ yellow ";;
Boldyellow)
Printf "$ boldyellow ";;
Blue)
Printf "$ blue ";;
Boldblue)
Printf "$ boldblue ";;
Magenta)
Printf "$ magenta ";;
Boldmagenta)
Printf "$ boldmagenta ";;
Cyan)
Printf "$ cyan ";;
Boldcyan)
Printf "$ boldcyan ";;
White)
Printf "$ white ";;
Boldwhite)
Printf "$ boldwhite ";;
Esac
Printf "% s" "$ message"
Tput sgr0 # tput sgr0 restores the default value
Printf "$ black"

Return
}

# Set mysql evn
MYSQL_USER = system # mysql username
MYSQL_PASS = 'password' # mysql logon user password
MYSQL_HOST = 192.168.2.188

# 1.the server infomation
Echo "the system basic infomation :"
Echo "************************************** *********************************"
Echo
Hostname = 'hostname' # host name

Ipaddress = 'ifconfig | grep' inet addr: '| grep-v' 127. 0.0.1' | cut-d:-f2 | awk '{print $1} ''# IP address

Gtway01 = 'cat/etc/sysconfig/network | grep GATEWAY | awk-F "=" '{print $2} ''# GATEWAY

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' # Number of physical memory

Sysver = 'cat/etc/issue | head-1' # -- system version

Kerver = 'uname-a | awk' {print $3} ''# kernel version

# 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'

Cechon "1.1 server hostname is:" red
Echo $ {hostname}

Cechon "1.2 server ipaddree is:" red
Echo $ {ipaddress}

If ["$ {gtway01}" = ""]; then
Cechon "1.3 server gateway is:" red
Echo $ {gtway02}
Else
Cechon "1.3 server gateway is:" red
Echo $ {gtway01}
Fi

Cechon "1.4 server cpuinfo is:" red
Echo $ {cpuinfo}

Cechon "1.5 server Physical memory number is:" red
Echo $ {phmem}

Cechon "1.6 server version is:" red
Echo $ {sysver}

Cechon "1.7 server system kernel version is:" red
Echo $ {kerver}

Cechon "1.8 server Physical memory number is:" red
Echo $ {phmem}

Cechon "1.9 server memory usage rate is:" red
Echo $ {mem_rate: 0: 5} %

Cechon "1.10 server disk usage is:" red
Echo
# Disk usage
Df-H | awk-f' \ t' {print $1, $2, $3, $4, $5, $6 }'

Echo
Cechon "1.11 server CPU load average is:" red
Echo
Uptime | awk 'In in {print "1 min, 5 min, 15 min"} {print $10, $11, $12 }'

Echo
Cechon "1.12 server started services is:" red
Echo
Chkconfig -- list | grep on
Echo

Echo
Cechon "1.13 server CPU free is:" red
Top-B-n 1 | grep Cpu | awk '{print $5}' | cut-f 1-d "."
Echo

Cechon "1.14 mysql ESTABLISHED connect is:" red
Echo
Netstat-an-t | grep ": 3306" | grep ESTABLISHED | awk '{printf "% s \ n", $5, $6} '| sort | sed's/^: ffff ://'
Echo

Cechon "1.15 server established tcp connect number is:" red
Echo
Netstat-n | awk '/^ tcp/{++ S [$ NF]} END {for (a in S) print a, S [a]}'
Echo

Echo
Echo "************************************** *********************************"
Echo

#2. mysql version
V_01 = "select @ version ;"
V_02 = "v02. 'date when policyymm1_d1_h1_m1_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'
Cechon "2.1 mysql runing version is: $ {v_03}" red
Echo ""
Rm-rf $ {v_02}
Mysql_port = 'cat/usr/local/mysql/my. cnf | grep '^ port' | sed-n 1p | awk-F =' {print $2 }''
Cechon "2.2 mysql port is :$ {mysql_port}" red
Echo ""

#3. number of mysql processes
Mysql_processnum = 'PS-ef | grep "mysql" | grep-v "grep" | wc-L'
Cechon "3. mysql process number is: $ {mysql_processnum}" red
Echo ""

#4. number of mysql processes connected to the client

Conn_01 = "conn01. 'date when policyymm1_d1_h1_m1_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'
Cechon "4. mysql client connect number is: $ {client_conn_num}" red
Echo ""
Rm-rf $ {conn_01}

# 5.QPS( transaction volume per second)

Qps_01 = "show global status like 'questions ';"
Qps_re = "qpsre. 'date when policyymm1_d1_h1_m1_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}' # shell does not support floating point operations by default.
Cechon "5. current mysql server QPS is :$ {qps_04: 0: 5}" red
Echo ""
Rm-rf $ {qps_re}

# 6.TPS( transaction volume per second)
Tps_01 = "show status where Variable_name in ('com _ commit ');"
Tps_02 = "show status where Variable_name in ('com _ rollback ');"
Tps_re01 = "tpsre01. 'date when policyymm1_d1_h1_m1_s'.txt"
Tps_re02 = "tpsre02. 'date when policyymm1_d1_h1_m1_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}'} ''# shell does not support floating point operations by default.
Tps_uptime = 'cat/proc/uptime | awk '{print $1 }''
Tps_avg = 'awk' BEGIN {print '$ {tps_sum}'/'$ {tps_uptime}'} ''# shell does not support floating point operations by default.
Cechon "6. current mysql server TPS is: $ {tps_avg}" red
Echo ""
Rm-rf $ {tps_re01}
Rm-rf $ {tps_re02}

# 7.key Buffer hit rate

# 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 too many yymm1_d1_h1_m1_s'.txt"
Kbrd_re02 = "kbrd02. 'date too many yymm1_d1_h1_m1_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
Cechon "7.1 there is no any value! "Green
Echo ""
Else
Kbrd_05 = 'awk' BEGIN {print '$ {kbrd_03}'/'$ {kbrd_04}' # shell does not support floating point operations by default.
Kbrd_06 = 'awk' BEGIN {print '1-$ {kbrd_05} '} ''# The shell does not support floating point operations by default.
Key_buffer_read_hits = 'awk' BEGIN {print '$ {kbrd_06}' * 100 }''
Cechon "7.1 current mysql key_buffer_read_hits is :$ {key_buffer_read_hits: 0: 5 }%" red
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 has been specified yymm1_d1_h1_m1_s'.txt"
Kbwd_re02 = "maid"
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
Cechon "7.2 there is no any value! "Green
Echo ""
Else
Kbwd_05 = 'awk' BEGIN {print '$ {kbwd_03}'/'$ {kbwd_04}' # shell does not support floating point operations by default.
Kbwd_06 = 'awk' BEGIN {print '1-$ {kbwd_05} '} ''# The shell does not support floating point operations by default.
Key_buffer_write_hits = 'awk' BEGIN {print '$ {kbwd_06}' * 100 }''
Cechon "7.2 current mysql key_buffer_write_hits is :$ {key_buffer_write_hits: 0: 5 }%" red
Echo ""
Fi

Rm-rf $ {kbwd_re01}
Rm-rf $ {kbwd_re02}

#8. InnoDB Buffer hit rate
# 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 too many yymm1_d1_h1_m1_s'.txt"
Innob_re02 = "innob_re02. 'date too many yymm1_d1_h1_m1_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
Cechon "8. there is no any value! "Green
Echo ""
Else
Innob_05 = 'awk' BEGIN {print '$ {innob_03}'/'$ {innob_04}' # shell does not support floating point operations by default
Innob_06 = 'awk' BEGIN {print '1-$ {innob_05} '} ''# shell does not support floating point operations by default
Innodb_buffer_read_hits = 'awk' BEGIN {print '$ {innob_06}' * 100 }''
Cechon "8. current mysql Innodb_buffer_read_hits is :$ {innodb_buffer_read_hits: 0: 5} %" red
Echo ""
Fi
Rm-rf $ {innob_re01}
Rm-rf $ {innob_re02}

#9. Query Cache hit rate

# 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 too many yymm1_d1_h1_m1_s'.txt"
Qc_re02 = "qc_re02. 'date too many yymm1_d1_h1_m1_s'.txt"
Qc_re03 = "qc_re03. 'date too many yymm1_d1_h1_m1_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
Cechon "9. there is no any value! "Green
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 }''
Cechon "9. current mysql query_cache_hits is :$ {query_cache_hits: 0: 5 }%" red
Echo ""
Fi
Rm-rf $ {qc_re01}
Rm-rf $ {qc_re02}
Rm-rf $ {qc_re03}