MySQL Performance check Script-section

#!/bin/sh
#[email protected]

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 $ = message
# parameter = = Color
{
Local default_msg= "No message passed."

message=${1:-$default _msg} # is the default value default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.

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 Restore default values
printf "$black"

Return
}

Cechon ()

# Color-echo.
# parameter 1 = message
# parameter 2 = = Color
{
Local default_msg= "No message passed."
# doesn ' t really need to be a local variable.

message=${1:-$default _msg} # is the default value default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.

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 Restore default values
printf "$black"

Return
}

#set MySQL EVN
Mysql_user=system #mysql的用户名
mysql_pass= ' Password ' #mysql的登录用户密码
mysql_host=192.168.2.188

#1. mysql version
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 '
Cechon "1. MySQL runing version is: ${v_03} "red
echo ""
RM-RF ${v_02}

#2. The number of system MySQL processes
Mysql_processnum= ' Ps-ef | grep "MySQL" | Grep-v "grep" | Wc-l '
Cechon "2. MySQL process number is: ${mysql_processnum} "red
echo ""

#3. Number of MySQL processes connected by the client

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 '
Cechon "3. MySQL client connect number is: ${client_conn_num} "red
echo ""
RM-RF ${conn_01}

#4. QPS (transaction volume per second)

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 $} '
qps_04= ' awk ' begin{print ' ${qps_02} '/' ${qps_03} '} ' #shell默认不支持浮点运算
Cechon "4. Current MySQL server QPS is: ${qps_04} "red
echo ""
RM-RF ${qps_re}

#5. 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 +%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} '} ' #shell默认不支持浮点运算
Tps_uptime= ' Cat/proc/uptime |awk ' {print $} '
tps_avg= ' awk ' begin{print ' ${tps_sum} '/' ${tps_uptime} '} ' #shell默认不支持浮点运算
Cechon "5. Current MySQL server TPS is: ${tps_avg} "red
echo ""
RM-RF ${TPS_RE01}
RM-RF ${TPS_RE02}

#6. Key Buffer hit ratio

#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
Cechon "6.1 There is no any value!" green
echo ""
Else
kbrd_05= ' awk ' begin{print ' ${kbrd_03} '/' ${kbrd_04} '} ' #shell默认不支持浮点运算
kbrd_06= ' awk ' begin{print ' 1-${kbrd_05} ' #shell默认不支持浮点运算
key_buffer_read_hits= ' awk ' begin{print ' ${kbrd_06} ' * 100} '
Cechon "6.1 Current MySQL key_buffer_read_hits is: ${key_buffer_read_hits}" 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 +%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
Cechon "6.2 There is no any value!" green
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} '
Cechon "6.2 Current MySQL key_buffer_write_hits is: ${key_buffer_write_hits}" red
echo ""
Fi

RM-RF ${KBWD_RE01}
RM-RF ${KBWD_RE02}

#7. 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 +%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
Cechon "7. There is no any value! "green
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} '
Cechon "7. Current MySQL innodb_buffer_read_hits is: ${innodb_buffer_read_hits} "red
echo ""
Fi
RM-RF ${INNOB_RE01}
RM-RF ${INNOB_RE02}

#8. 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 +%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
Cechon "8. 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 "8. Current MySQL query_cache_hits is: ${query_cache_hits} "red
echo ""
Fi
RM-RF ${QC_RE01}
RM-RF ${QC_RE02}
RM-RF ${QC_RE03}

MySQL Performance check Script-section