MySQL5.0.9版本获取状态脚本



#!/bin/bash

fun_mysql_operations(){
MYSQL_DATATIME=`date +"%Y-%m-%d %H:%M:%S"`
MYSQL_BASEDIR=$1
MYSQL_HOSTNAME=$2
MYSQL_USERNAME=$3
MYSQL_PASSWORD=$4
MYSQL_PORT=$5
MYSQL_SOCKET=$6
MYSQL_CONNECTION_COMMAND="${MYSQL_BASEDIR} -h${MYSQL_HOSTNAME} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -P${MYSQL_PORT} -S${MYSQL_SOCKET}"

SQL1="CREATE TABLE IF NOT EXISTS mysql.mysql_status_check(mysql_status_name varchar(35),mysql_status_value varchar(25),mysql_getstatus_time datetime);"
$MYSQL_CONNECTION_COMMAND -e "${SQL1}"
$MYSQL_CONNECTION_COMMAND -e "show global status; " >/tmp/mysql5.0_status.txt
$MYSQL_CONNECTION_COMMAND -e "SHOW VARIABLES like \"%datadir%\"; " >/tmp/my.txt
sed -i '1d' /tmp/my.txt |cat /tmp/my.txt |awk '{print $2}'  >>/tmp/my.txt
mysql_datadir=`awk '{print $2}' /tmp/my.txt`
cd $mysql_datadir
mysql_data_size=`du -sh|awk '{print $1}' `

ARR_STATUS_VAR=(Key_reads Key_read_requests Qcache_hits Qcache_inserts Innodb_buffer_pool_reads Innodb_buffer_pool_read_requests
Threads_created Connections Slow_queries Created_tmp_disk_tables Created_tmp_tables Select_full_join Threads_connected
 Connect_timeout Innodb_row_lock_current_waits Threads_cached Threads_running Table_locks_waited Questions Queries  Key_writes Key_write_requests data_length index_length Opened_tables processlist_count processlist_sleep)

$MYSQL_CONNECTION_COMMAND -e "delete from mysql.mysql_status_check_new;"
i=1
for status_var in ${ARR_STATUS_VAR[@]};
do
 mysql_status=`grep ${status_var} /tmp/mysql5.0_status.txt| awk '{print $1}'`
 arrary1[i]=${mysql_status}
 mysql_status_values=`grep ${status_var} /tmp/mysql5.0_status.txt| awk '{print $2}'`
 arrary2[i]=${mysql_status_values}

 if [[ $status_var != ${arrary1[i]} ]]; then
    arrary1[i]=$status_var
    arrary2[i]=0
 fi
 if [[ ${i} = 27 ]]; then
   SQL2="CREATE TABLE IF NOT EXISTS mysql.mysql_status_check_new(${arrary1[1]} varchar(35),${arrary1[2]} varchar(35),${arrary1[3]} varchar(35),${arrary1[4]} varchar(35),${arrary1[5]} varchar(35),${arrary1[6]} varchar(35),${arrary1[7]} varchar(35),${arrary1[8]} varchar(35),${arrary1[9]} varchar(35),${arrary1[10]} varchar(35),${arrary1[11]} varchar(35),${arrary1[12]} varchar(35),${arrary1[13]} varchar(35),${arrary1[14]} varchar(35),${arrary1[15]} varchar(35),${arrary1[16]} varchar(35),${arrary1[17]} varchar(35),${arrary1[18]} varchar(35),${arrary1[19]} varchar(35),${arrary1[20]} varchar(35),${arrary1[21]} varchar(35),${arrary1[22]} varchar(35),${arrary1[23]} varchar(35),${arrary1[24]} varchar(35),${arrary1[25]} varchar(35),${arrary1[26]} varchar(35),${arrary1[27]} varchar(35),getstatus_time datetime);"
   SQL3="insert into mysql.mysql_status_check_new values('${arrary2[1]}','${arrary2[2]}','${arrary2[3]}','${arrary2[4]}','${arrary2[5]}','${arrary2[6]}','${arrary2[7]}','${arrary2[8]}','${arrary2[9]}','${arrary2[10]}','${arrary2[11]}','${arrary2[12]}','${arrary2[13]}','${arrary2[14]}','${arrary2[15]}','${arrary2[16]}','${arrary2[17]}','${arrary2[18]}','${arrary2[19]}','${arrary2[20]}','${arrary2[21]}','${arrary2[22]}','${arrary2[23]}','${arrary2[24]}','${arrary2[25]}','${arrary2[26]}','${arrary2[27]}','${MYSQL_DATATIME}');"
echo "${SQL3}"
   $MYSQL_CONNECTION_COMMAND -e "${SQL2}"
   $MYSQL_CONNECTION_COMMAND -e "${SQL3}"
 fi
#echo "${i}  ${arrary1[i]}  ${arrary2[i]}"
#echo "length:${#ARR_STATUS_VAR[@]}"
let i=i+1
done
sql_4="update mysql.mysql_status_check_new set data_length='${mysql_data_size}'; "
$MYSQL_CONNECTION_COMMAND -e "${sql_4}"
}

mysql_connections_info=/root/get_mysqlstatus_connections_info.txt
while read line
do
MYSQL_BASEDIR=`echo $line| awk '{print $1}'`
MYSQL_HOSTNAME=`echo $line | awk '{print $2}'`
MYSQL_USERNAME=`echo $line | awk '{print $3}'`
MYSQL_PASSWORD=`echo $line | awk '{print $4}'`
MYSQL_PORT=`echo $line | awk '{print $5}'`
MYSQL_SOCKET=`echo $line | awk '{print $6}'`
fun_mysql_operations ${MYSQL_BASEDIR}   ${MYSQL_HOSTNAME} ${MYSQL_USERNAME} ${MYSQL_PASSWORD}  ${MYSQL_PORT} ${MYSQL_SOCKET}
done < ${mysql_connections_info}
exit 0