文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

通过shell检查mysql主机和数据库,生成html报表的脚本

2016-12-30 11:08

关注

通过shell检查mysql主机和数据库,生成html报表的脚本

该脚本主要用于大致诊断MYSQL主机和数据库配置及性能收集,脚本部分功能展示如下:

郑州不孕不育医院:http://mobile.03913882333.com/

image.png

image.png

image.png

image.png

郑州输卵管造影费用是多少:http://jbk.39.net/yiyuanfengcai/tsyl_zztjyy/3025/

实现该上述展示功能的shell脚本如下:

file_output="os_mysql_summary.html"
td_str=""
th_str=""
myuser="root"
mypasswd="password"
myip="192.168.11.101"
myport="3307"
mysql_cmd="mysql -u${myuser} -p${mypasswd} -h${myip} -P${myport} --protocol=tcp --silent"
create_html_css(){
  echo -e "



"
}
create_html_head(){
echo -e ""
}
create_table_head1(){
  echo -e "
" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"} create_table_head2(){echo -e "
" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"} create_td(){ td_str=`echo $1 | awk "BEGIN{FS="|"}""{i=1; while(i<=NF) {print "";i++}}"`} create_th(){ th_str=`echo $1|awk "BEGIN{FS="|"}""{i=1; while(i<=NF) {print "";i++}}"`} create_tr1(){ create_td "$1"echo -e "$td_str">>$file_output} create_tr2(){ create_th "$1"echo -e "$th_str">>$file_output} create_tr3(){echo -e "">>$file_output} create_table_end(){echo -e "
"$i" "$i"
  `cat $1`
  
"
} create_html_end(){ echo -e "" } NAME_VAL_LEN=12 name_val () { printf "%+*s | %s " "${NAME_VAL_LEN}" "$1" "$2" } get_virtual () { local file="/var/log/dmesg" if grep -qi -e "vmware" -e "vmxnet" -e "paravirtualized kernel on vmi" "${file}"; then echo "VMWare"; elif grep -qi -e "paravirtualized kernel on xen" -e "Xen virtual console" "${file}"; then echo "Xen"; elif grep -qi "qemu" "${file}"; then echo "QEmu"; elif grep -qi "paravirtualized kernel on KVM" "${file}"; then echo "KVM"; elif grep -q "VBOX" "${file}"; then echo "VirtualBox"; elif grep -qi "hd.: Virtual .., ATA.*drive" "${file}"; then echo "Microsoft VirtualPC"; else echo "Physical Machine" fi } get_physics(){ name_val "Date" "`date -u +"%F %T UTC"` (local TZ: `date +"%Z %z"`)" name_val "Hostname" "`uname -n`" name_val "Uptime" "`uptime`" name_val "System" "`dmidecode -s "system-manufacturer" "system-product-name" "system-version" "chassis-type"`" name_val "Service_num" "`dmidecode -s "system-serial-number"`" name_val "Platform" "`uname -s`" name_val "Release" "`cat /etc/{oracle,redhat,SuSE,centos}-release 2>/dev/null|sort -ru|head -n1`" name_val "Kernel" "`uname -r`" name_val "Architecture" "CPU=`lscpu|grep Architecture|awk -F: "{print $2}"|sed "s/^[[:space:]]*//g"`;OS=`getconf LONG_BIT`-bit" name_val "Threading" "`getconf GNU_LIBPTHREAD_VERSION`" name_val "SELinux" "`getenforce`" name_val "Virtualized" "`get_virtual`" } get_cpuinfo () { file="/proc/cpuinfo" virtual=`grep -c ^processor "${file}"` physical=`grep "physical id" "${file}" | sort -u | wc -l` cores=`grep "cpu cores" "${file}" | head -n 1 | cut -d: -f2` model=`grep "model name" "${file}"|sort -u|awk -F: "{print $2}"` speed=`grep -i "cpu MHz" "${file}"|sort -u|awk -F: "{print $2}"` cache=`grep -i "cache size" "${file}"|sort -u|awk -F: "{print $2}"` SysCPUIdle=`vmstat | sed -n "$ p" | awk "{print $15}"` [ "${physical}" = "0" ] && physical="${virtual}" [ -z "${cores}" ] && cores=0 cores=$((${cores} * ${physical})); htt="" if [ ${cores} -gt 0 -a $cores -lt $virtual ]; then htt=yes; else htt=no; fi name_val "Processors" "physical = ${physical}, cores = ${cores}, virtual = ${virtual}, hyperthreading = ${htt}" name_val "Models" "${physical}*${model}" name_val "Speeds" "${virtual}*${speed}" name_val "Caches" "${virtual}*${cache}" name_val "CPUIdle(%)" "${SysCPUIdle}%" } get_meminfo(){ echo "Locator |Size |Speed |Form Factor | Type | Type Detail" >>/tmp/tmpmem3_h1_`date +%y%m%d`.txt dmidecode| grep -v "Memory Device Mapped Address"|grep -A12 -w "Memory Device" |egrep "Locator:|Size:|Speed:|Form Factor:|Type:|Type Detail:" |awk -F: "/Size|Type|Form.Factor|Type.Detail|^[ ]+Locator/{printf("|%s", $2)}/^[ ]+Speed/{print "|" $2}" |grep -v "No Module Installed" |awk -F"|" "{print $4,"|", $2,"|", $7,"|", $3,"|", $5,"|", $6}" >>/tmp/tmpmem3_t1_`date +%y%m%d`.txt free -glht >>/tmp/tmpmem2_`date +%y%m%d`.txt memtotal=`vmstat -s | head -1 | awk "{print $1}"` avm=`vmstat -s| sed -n "3p" | awk "{print $1}"` name_val "Mem_used_rate(%)" "`echo "100*${avm}/${memtotal}" | bc`%" >>/tmp/tmpmem1_`date +%y%m%d`.txt } get_diskinfo(){ echo "Filesystem |Type |Size | Used | Avail | Use% | Mounted on | Opts" >>/tmp/tmpdisk_h1_`date +%y%m%d`.txt df -ThP|grep -v tmpfs|sed "1d"|sort >/tmp/tmpdf1_`date +%y%m%d`.txt mount -l|awk "{print $1,$6}"|grep ^/|sort >/tmp/tmpdf2_`date +%y%m%d`.txt join /tmp/tmpdf1_`date +%y%m%d`.txt /tmp/tmpdf2_`date +%y%m%d`.txt |awk "{print $1,"|", $2,"|", $3,"|", $4,"|", $5,"|", $6,"|", $7,"|", $8}" >>/tmp/tmpdisk_t1_`date +%y%m%d`.txt lsblk >>/tmp/tmpdisk1_`date +%y%m%d`.txt for disk in `ls -l /sys/block|awk "{print $9}"|sed "/^$/d"|grep -v fd` do echo "${disk}" `cat /sys/block/${disk}/queue/scheduler` >>/tmp/tmpdisk2_`date +%y%m%d`.txt done pvs >>/tmp/tmpdisk3_`date +%y%m%d`.txt echo "====================== ===== ===== ===== ===== ===== ========== =======" >>/tmp/tmpdisk3_`date +%y%m%d`.txt vgs >>/tmp/tmpdisk3_`date +%y%m%d`.txt echo "====================== ===== ===== ===== ===== ===== ========== =======" >>/tmp/tmpdisk3_`date +%y%m%d`.txt lvs >>/tmp/tmpdisk3_`date +%y%m%d`.txt } get_netinfo(){ echo "interface | status | ipadds | mtu | Speed | Duplex" >>/tmp/tmpnet_h1_`date +%y%m%d`.txt for ipstr in `ifconfig -a|grep ": flags"|awk "{print $1}"|sed "s/.$//"` do ipadds=`ifconfig ${ipstr}|grep -w inet|awk "{print $2}"` mtu=`ifconfig ${ipstr}|grep mtu|awk "{print $NF}"` speed=`ethtool ${ipstr}|grep Speed|awk -F: "{print $2}"` duplex=`ethtool ${ipstr}|grep Duplex|awk -F: "{print $2}"` echo "${ipstr}" "up" "${ipadds}" "${mtu}" "${speed}" "${duplex}" |awk "{print $1,"|", $2,"|", $3,"|", $4,"|", $5,"|", $6}" >>/tmp/tmpnet1_`date +%y%m%d`.txt done } get_topproc(){ #os load echo "os load 1" >>/tmp/tmpload_`date +%y%m%d`.txt sar -q 1 5 >>/tmp/tmpload_`date +%y%m%d`.txt echo "os load 2" >>/tmp/tmpload_`date +%y%m%d`.txt sar -b 1 5 >>/tmp/tmpload_`date +%y%m%d`.txt echo "os load 3" >>/tmp/tmpload_`date +%y%m%d`.txt vmstat 1 5 >>/tmp/tmpload_`date +%y%m%d`.txt #top cpu mpstat 1 5 >>/tmp/tmptopcpu_`date +%y%m%d`.txt echo "TOP10 CPU Resource Process" >>/tmp/tmptopcpu_`date +%y%m%d`.txt ps aux|head -1 >>/tmp/tmptopcpu_`date +%y%m%d`.txt ps aux|grep -v PID|sort -rn -k +3|head >>/tmp/tmptopcpu_`date +%y%m%d`.txt #top -bn1 -o "%CPU"|sed -n "/PID/,17p" #top mem echo "TOP10 MEM Resource Process" >>/tmp/tmptopmem_`date +%y%m%d`.txt ps aux|head -1 >>/tmp/tmptopmem_`date +%y%m%d`.txt ps aux|grep -v PID|sort -rn -k +4|head >>/tmp/tmptopmem_`date +%y%m%d`.txt #top -bn1 -o "%MEM"|sed -n "/PID/,17p" #top i/o iostat -cdmx 2 3 >>/tmp/tmptopio_`date +%y%m%d`.txt #iotop -botq -n 3 -d 2 } my_base_info(){ ${mysql_cmd} -e "select now(),current_user(),version()G" ${mysql_cmd} -e "show global variables like "autocommit";"|grep -i ^auto|awk "{print $1,":",$2}" ${mysql_cmd} -e "show global variables"|egrep -w "port|character_set_server|datadir|log_error|log_bin_basename|tx_isolation|binlog_format"|awk "{print $1,":",$2}" } my_stat_info(){ ${mysql_cmd} -e status >>/tmp/tmpmy_stat_`date +%y%m%d`.txt } my_connip_info(){ echo "ipadds|conn_status|count" >>/tmp/tmpmy_connip_h1_`date +%y%m%d`.txt netstat -an|grep ${myport}|grep -vi LISTEN|awk "{print $5,$6}"|sed "s/::ffff://g"|sed "s/:[0-9]*//g"|sed "1d"|sort|uniq -c|awk "{print $2,"|",$3,"|",$1}" >>/tmp/tmpmy_connip_t1_`date +%y%m%d`.txt } my_param_info(){ echo "Variable_name|Value" >>/tmp/tmpmy_param_h1_`date +%y%m%d`.txt ${mysql_cmd} -e "show global variables"|egrep -w "innodb_buffer_pool_size|innodb_file_per_table|innodb_flush_log_at_trx_commit|innodb_io_capacity| innodb_lock_wait_timeout|innodb_data_home_dir|innodb_log_file_size|innodb_log_files_in_group|log_slave_updates|long_query_time|lower_case_table_names| max_connections|max_connect_errors|max_user_connections|query_cache_size|query_cache_type |server_id|slow_query_log|slow_query_log_file|innodb_temp_data_file_path| sql_mode|gtid_mode|enforce_gtid_consistency|expire_logs_days|sync_binlog|open_files_limit|myisam_sort_buffer_size|myisam_max_sort_file_size" |awk "{print $1,"|",$2}" >>/tmp/tmpmy_param_t1_`date +%y%m%d`.txt } my_segm1_info(){ ${mysql_cmd} -H -e "select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024/1024,2)," GB") as data_size, concat(truncate(sum(index_length)/1024/1024/1024,2),"GB") as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;" } my_segm2_info(){ ${mysql_cmd} -H -e "select table_schema,table_name,table_rows,concat(truncate(data_length/1024/1024/1024,2)," GB") as data_size, concat(truncate(index_length/1024/1024/1024,2)," GB") as index_size from information_schema.tables order by data_length desc limit 10;" } my_segm3_info(){ ${mysql_cmd} -H -e "select table_name,table_rows,concat(round(data_length/1024/1024, 2), "MB") as size,data_free from information_schema.tables where data_free>0 order by data_length desc;" } my_obj1_info(){ ${mysql_cmd} -H -e "select table_schema as db,table_type as object_type,count(*) as cnt from information_schema.tables group by table_schema,table_type union all select routine_schema as db,routine_type as object_type,count(*) as cnt from information_schema.routines group by routine_schema,routine_type;" } my_obj2_info(){ ${mysql_cmd} -H -e "select table_schema,engine,count(*) as cnt from information_schema.tables group by table_schema,engine;" } my_obj3_info(){ ${mysql_cmd} -H -e "select table_schema,table_name from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema","sys") and table_name not in ( select table_name from information_schema.table_constraints t join information_schema.key_column_usage k using ( constraint_name,table_schema,table_name) where t.constraint_type = "PRIMARY KEY" and table_schema not in ("mysql","information_schema","performance_schema","sys"));" } my_lock_info(){ ${mysql_cmd} -H -e "SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query, bl.lock_id blocking_lock_id, bl.lock_mode blocking_lock_mode, bl.lock_type blocking_lock_type, bl.lock_table blocking_lock_table, bl.lock_index blocking_lock_index, rl.lock_id waiting_lock_id, rl.lock_mode waiting_lock_mode, rl.lock_type waiting_lock_type, rl.lock_table waiting_lock_table, rl.lock_index waiting_lock_index FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_idG" } my_innodb_info(){ ${mysql_cmd} -e "show engine innodb statusG" } my_user_info(){ ${mysql_cmd} -e "SELECT DISTINCT CONCAT("show grants for """,user,"""@""",host,""";") AS query FROM mysql.user;" >>/tmp/tmpmy_user_t_`date +%y%m%d`.txt while read line do echo "=================================================================" >>/tmp/tmpmy_user_`date +%y%m%d`.txt echo "$line" >>/tmp/tmpmy_user_`date +%y%m%d`.txt ${mysql_cmd} -e "$line" >>/tmp/tmpmy_user_`date +%y%m%d`.txt done </tmp/tmpmy_user_t_`date +%y%m%d`.txt } create_html(){ rm -rf $file_output touch $file_output create_html_css >> $file_output create_html_head "OS Basic Summary" >> $file_output create_table_head1 >> $file_output get_physics >>/tmp/tmpos_summ_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpos_summ_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "CPU Info Summary" >> $file_output create_table_head1 >> $file_output get_cpuinfo >>/tmp/tmp_cpuinfo_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmp_cpuinfo_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "MEM Info Summary" >> $file_output create_table_head1 >> $file_output get_meminfo while read line do create_tr1 "$line" done < /tmp/tmpmem1_`date +%y%m%d`.txt create_table_end >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmpmem2_`date +%y%m%d`.txt" create_table_end >> $file_output create_table_head1 >> $file_output while read line do create_tr2 "$line" done < /tmp/tmpmem3_h1_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpmem3_t1_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "Disk Info Summary" >> $file_output create_table_head1 >> $file_output get_diskinfo while read line do create_tr2 "$line" done < /tmp/tmpdisk_h1_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpdisk_t1_`date +%y%m%d`.txt create_table_end >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmpdisk1_`date +%y%m%d`.txt" create_table_end >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmpdisk2_`date +%y%m%d`.txt" create_table_end >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmpdisk3_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "Network Info Summary" >> $file_output create_table_head1 >> $file_output get_netinfo while read line do create_tr2 "$line" done < /tmp/tmpnet_h1_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpnet1_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "OS Load Summary" >> $file_output create_table_head1 >> $file_output get_topproc create_tr3 "/tmp/tmpload_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "TOP CPU Summary" >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmptopcpu_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "TOP MEM Summary" >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmptopmem_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "TOP IO Summary" >> $file_output create_table_head1 >> $file_output create_tr3 "/tmp/tmptopio_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "Basic Database Information" >> $file_output create_table_head1 >> $file_output my_base_info >>/tmp/tmpmy_base_`date +%y%m%d`.txt sed -i -e "1d" -e "s/:/|/g" /tmp/tmpmy_base_`date +%y%m%d`.txt while read line do create_tr1 "$line" done </tmp/tmpmy_base_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "Running Status of Database" >> $file_output create_table_head1 >> $file_output my_stat_info create_tr3 "/tmp/tmpmy_stat_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "IP Connection Statistics" >> $file_output create_table_head1 >> $file_output my_connip_info while read line do create_tr2 "$line" done < /tmp/tmpmy_connip_h1_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpmy_connip_t1_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "Important Parameters" >> $file_output create_table_head1 >> $file_output my_param_info while read line do create_tr2 "$line" done < /tmp/tmpmy_param_h1_`date +%y%m%d`.txt while read line do create_tr1 "$line" done < /tmp/tmpmy_param_t1_`date +%y%m%d`.txt create_table_end >> $file_output create_html_head "Size of each database" >> $file_output my_segm1_info >> $file_output create_html_head "TOP10 Space Table" >> $file_output my_segm2_info >> $file_output create_html_head "High Water Level Meter" >> $file_output my_segm3_info >> $file_output create_html_head "Object type statistics" >> $file_output my_obj1_info >> $file_output create_html_head "Storage Engine Number Statistics" >> $file_output my_obj2_info >> $file_output create_html_head "Tables without primary keys" >> $file_output my_obj3_info >> $file_output create_html_head "Lock information" >> $file_output my_lock_info >> $file_output create_html_head "Innodb Status Information" >> $file_output create_table_head1 >> $file_output my_innodb_info >>/tmp/tmpmy_innodb_`date +%y%m%d`.txt create_tr3 "/tmp/tmpmy_innodb_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "User Authorization Information" >> $file_output create_table_head1 >> $file_output my_user_info create_tr3 "/tmp/tmpmy_user_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "Slow SQL statistics" >> $file_output create_html_end >> $file_output sed -i "s/BORDER=1/width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse"/g" $file_output rm -rf /tmp/tmp*_`date +%y%m%d`.txt } # This script must be executed as root RUID=`id|awk -F( "{print $1}"|awk -F= "{print $2}"` # #OR# RUID=`id | cut -d( -f1 | cut -d= -f2` #OR#ROOT_UID=0 if [ ${RUID} != "0" ];then echo"This script must be executed as root" exit 1 fi PLATFORM=`uname` if [ ${PLATFORM} = "HP-UX" ] ; then echo "This script does not support HP-UX platform for the time being" exit 1 elif [ ${PLATFORM} = "SunOS" ] ; then echo "This script does not support SunOS platform for the time being" exit 1 elif [ ${PLATFORM} = "AIX" ] ; then echo "This script does not support AIX platform for the time being" exit 1 elif [ ${PLATFORM} = "Linux" ] ; then echo -e " ########################################################################################### # # #Make sure that the following parameters at the beginning of the script are correct. # #myuser="root" (Database Account) # #mypasswd="password" (Database password) # #myip="192.168.11.101" (Database native IP) # #myport="3307" (Database port) # # Otherwise, the script cannot be executed properly. # # # ########################################################################################### " #read -p "The database connection information is configured correctly. Please execute[yes or y]:" SELECT #printf " " #if [ $SELECT == "yes" -o $SELECT == "y" ]; then create_html #else # exit 1 #fi fi
阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯