查看Mysql集群状态

深藏阁楼爱情的钟 2023-06-09 12:23 14阅读 0赞

# 查看Mysql集群状态 #

> 依赖sshpass命令，所以需要先安装sshpass-1.06-1.el7.x86\_64.rpm

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0R1a2UxNDc_size_16_color_FFFFFF_t_70]

*  授权

chmod +x check-mysql.sh

#!/bin/sh
    #set -ex
    
    #日志名称
    LOG_FILE=host_check_$(date "+%Y%m%d").log
    #注释变量或密码留空则执行脚本时输入
    SSHPASS=[password]
    MYSQLPASS=[password]
    
    if [[ ! -x $(command -v sshpass 2> /dev/null) ]]; then
      echo -e 'sshpass does not exist, you should install sshpass.'
      exit 0
    fi
    
    if [[ -z ${SSHPASS} ]]; then
      read -s -p 'Enter SSH password for hosts:' SSHPASS
      export SSHPASS
      echo
    else
      export SSHPASS
    fi
    
    if [[ -z ${MYSQLPASS} ]]; then
      read -s -p 'Enter MySQL password for hosts:' MYSQLPASS
      export MYSQLPASS
      echo
    else
      export MYSQLPASS
    fi
    
    cat << EOF > ~/.ssh/config
    StrictHostKeyChecking no
    EOF
    
    function format_output { 
      COL=$(($(tput cols) - 2))
      IFS= read -r -d '\n' INPUT
      MAX_LEN=$(echo "${INPUT}" | awk '{ print length($0)}' | awk 'BEGIN { max = 0} { if ($1+0 > max+0) { max=$1; content=$0} } END { print content}')
      if [[ ${MAX_LEN} -gt ${COL} ]]; then
        MAX_LEN=${COL}
      fi
      MLINE=$(awk -v len=${ MAX_LEN} 'BEGIN{OFS="─"; NF=len+1; print}')
      echo "┌${MLINE}┐"
      IFS=''
      echo -n "${INPUT}" | while read LINE; do
        if [[ -z ${LINE} ]]; then
          echo "├${MLINE}┤"
        else
          printf "│%-${MAX_LEN}s│\n" "${LINE}"
        fi
      done
      echo -e "└${MLINE}┘\n"
    }
    
    function ping_host { 
      ping -c 1 -W 1 $1 2>&1 > /dev/null || (echo -e "Host $1: Ping Failed.\n" && exit 1)
    }
    
    function get_mysql_service { 
      CT='$(cat /tmp/.mysql.socks)'
      for HOST_IP in $(echo $1 | tr ',' ' '); do
        (ping_host ${HOST_IP} && sshpass -e ssh root@${HOST_IP} 'bash -s' << EOF) | format_output | tee -a ${LOG_FILE}
    export LANG=en_US.UTF-8
    echo -e "MySQL Server: ${HOST_IP}\n"
    echo -e "MySQL Status:"
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Uptime '
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Com_select '
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Com_insert '
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Com_update '
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Com_delete '
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e 'show status' 2>&1 | sed -r 's/\t/ /g' | grep 'Threads_connected '
    echo -e "\nMySQL Database Status:"
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e "select table_schema as 'Database',sum(table_rows) as 'Records',sum(truncate(data_length/1024/1024, 2)) as 'Data(MB)',sum(truncate(index_length/1024/1024, 2)) as 'Index(MB)'from information_schema.tables where table_schema='cbei_isp';" 2>&1 | sed -r 's/\t/ /g' | grep -v 'password'
    echo -e "\nMySQL Tables Status:"
    /opt/upsql/mysql/bin/mysql -h ${HOST_IP} -P 60001 -ucup_dba -p${MYSQLPASS} -e "select table_schema as 'Database',table_name as 'Table',table_rows as 'Records', truncate(data_length/1024/1024, 2) as 'Data(MB)', truncate(index_length/1024/1024, 2) as 'Index(MB)' from information_schema.tables where table_schema='cbei_isp' order by data_length desc,index_length desc;" 2>&1 | sed 's/\t/ /g' | grep -v 'password' > /tmp/.mysql.socks
    printf '%-10s %-30s %-10s %-10s %-10s\n' $CT
    rm -f /tmp/.mysql.socks
    EOF
      done
    }
    
    get_mysql_service 192.168.1.1
    
    rm -f ~/.ssh/config

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0R1a2UxNDc_size_16_color_FFFFFF_t_70]: /images/20230531/5425633d4ca144c09edc7e191637f680.png