通过shell检查mysql主机和数据库，生成html报表的脚本
该脚本主要用于大致诊断MYSQL主机和数据库配置及性能收集，脚本部分功能展示如下： 实现该上述展示功能的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"<html> <head> <styletype="text/css"> body{font:12pxCourierNew,Helvetica,sansserif;color:black;background:White;} table,tr,td{font:12pxCourierNew,Helvetica,sansserif;color:Black;background:#FFFFCC;padding:0px0px0px0px;margin:0px0px0px0px;} th{font:bold12pxCourierNew,Helvetica,sansserif;color:White;background:#0033FF;padding:0px0px0px0px;} h1{font:bold12ptCourierNew,Helvetica,sansserif;color:Black;padding:0px0px0px0px;} </style> </head> <body>" } create_html_head(){ echo-e"<h1>$1</h1>" } create_table_head1(){ echo-e"<tablewidth="68%"border="1"bordercolor="#000000"cellspacing="0px"style="border-collapse:collapse">" } create_table_head2(){ echo-e"<tablewidth="100%"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"<td>"$i"</td>";i++}}'` } create_th(){ th_str=`echo$1|awk'BEGIN{FS="|"}''{i=1;while(i<=NF){print"<th>"$i"</th>";i++}}'` } create_tr1(){ create_td"$1" echo-e"<tr> $td_str </tr>">>$file_output } create_tr2(){ create_th"$1" echo-e"<tr> $th_str </tr>">>$file_output } create_tr3(){ echo-e"<tr><td> <prestyle=\"font-family:CourierNew;word-wrap:break-word;white-space:pre-wrap;white-space:-moz-pre-wrap\"> `cat$1` </pre></td></tr>">>$file_output } create_table_end(){ echo-e"</table>" } create_html_end(){ echo-e"</body></html>" } NAME_VAL_LEN=12 name_val(){ printf"%+*s|%s\n""${NAME_VAL_LEN}""$1""$2" } get_virtual(){ localfile="/var/log/dmesg" ifgrep-qi-e"vmware"-e"vmxnet"-e'paravirtualizedkernelonvmi'"${file}";then echo"VMWare"; elifgrep-qi-e'paravirtualizedkernelonxen'-e'Xenvirtualconsole'"${file}";then echo"Xen"; elifgrep-qi"qemu""${file}";then echo"QEmu"; elifgrep-qi'paravirtualizedkernelonKVM'"${file}";then echo"KVM"; elifgrep-q"VBOX""${file}";then echo"VirtualBox"; elifgrep-qi'hd.:Virtual..,ATA.*drive'"${file}";then echo"MicrosoftVirtualPC"; else echo"PhysicalMachine" fi } get_physics(){ name_val"Date""`date-u+'%F%TUTC'`(localTZ:`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}-release2>/dev/null|sort-ru|head-n1`" name_val"Kernel""`uname-r`" name_val"Architecture""CPU=`lscpu|grepArchitecture|awk-F:'{print$2}'|sed's/^[[:space:]]*//g'`;OS=`getconfLONG_BIT`-bit" name_val"Threading""`getconfGNU_LIBPTHREAD_VERSION`" name_val"SELinux""`getenforce`" name_val"Virtualized""`get_virtual`" } get_cpuinfo(){ file="/proc/cpuinfo" virtual=`grep-c^processor"${file}"` physical=`grep'physicalid'"${file}"|sort-u|wc-l` cores=`grep'cpucores'"${file}"|head-n1|cut-d:-f2` model=`grep"modelname""${file}"|sort-u|awk-F:'{print$2}'` speed=`grep-i"cpuMHz""${file}"|sort-u|awk-F:'{print$2}'` cache=`grep-i"cachesize""${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}-gt0-a$cores-lt$virtual];thenhtt=yes;elsehtt=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|FormFactor|Type|TypeDetail">>/tmp/tmpmem3_h1_`date+%y%m%d`.txt dmidecode|grep-v"MemoryDeviceMappedAddress"|grep-A12-w"MemoryDevice"\ |egrep"Locator:|Size:|Speed:|FormFactor:|Type:|TypeDetail:"\ |awk-F:'/Size|Type|Form.Factor|Type.Detail|^[\t]+Locator/{printf("|%s",$2)}/^[\t]+Speed/{print"|"$2}'\ |grep-v"NoModuleInstalled"\ |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%|Mountedon|Opts">>/tmp/tmpdisk_h1_`date+%y%m%d`.txt df-ThP|grep-vtmpfs|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 fordiskin`ls-l/sys/block|awk'{print$9}'|sed'/^$/d'|grep-vfd` 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 foripstrin`ifconfig-a|grep":flags"|awk'{print$1}'|sed's/.$//'` do ipadds=`ifconfig${ipstr}|grep-winet|awk'{print$2}'` mtu=`ifconfig${ipstr}|grepmtu|awk'{print$NF}'` speed=`ethtool${ipstr}|grepSpeed|awk-F:'{print$2}'` duplex=`ethtool${ipstr}|grepDuplex|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(){ #osload echo"osload1">>/tmp/tmpload_`date+%y%m%d`.txt sar-q15>>/tmp/tmpload_`date+%y%m%d`.txt echo"osload2">>/tmp/tmpload_`date+%y%m%d`.txt sar-b15>>/tmp/tmpload_`date+%y%m%d`.txt echo"osload3">>/tmp/tmpload_`date+%y%m%d`.txt vmstat15>>/tmp/tmpload_`date+%y%m%d`.txt #topcpu mpstat15>>/tmp/tmptopcpu_`date+%y%m%d`.txt echo"TOP10CPUResourceProcess">>/tmp/tmptopcpu_`date+%y%m%d`.txt psaux|head-1>>/tmp/tmptopcpu_`date+%y%m%d`.txt psaux|grep-vPID|sort-rn-k+3|head>>/tmp/tmptopcpu_`date+%y%m%d`.txt #top-bn1-o"%CPU"|sed-n'/PID/,17p' #topmem echo"TOP10MEMResourceProcess">>/tmp/tmptopmem_`date+%y%m%d`.txt psaux|head-1>>/tmp/tmptopmem_`date+%y%m%d`.txt psaux|grep-vPID|sort-rn-k+4|head>>/tmp/tmptopmem_`date+%y%m%d`.txt #top-bn1-o"%MEM"|sed-n'/PID/,17p' #topi/o iostat-cdmx23>>/tmp/tmptopio_`date+%y%m%d`.txt #iotop-botq-n3-d2 } my_base_info(){ ${mysql_cmd}-e"selectnow(),current_user(),version()\G" ${mysql_cmd}-e"showglobalvariableslike'autocommit';"|grep-i^auto|awk'{print$1,":",$2}' ${mysql_cmd}-e"showglobalvariables"|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}-estatus>>/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-viLISTEN|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"showglobalvariables"|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"selectTABLE_SCHEMA,concat(truncate(sum(data_length)/1024/1024/1024,2),'GB')asdata_size,\ concat(truncate(sum(index_length)/1024/1024/1024,2),'GB')asindex_size\ frominformation_schema.tablesgroupbyTABLE_SCHEMAorderbydata_lengthdesc;" } my_segm2_info(){ ${mysql_cmd}-H-e"selecttable_schema,table_name,table_rows,concat(truncate(data_length/1024/1024/1024,2),'GB')asdata_size,\ concat(truncate(index_length/1024/1024/1024,2),'GB')asindex_sizefrominformation_schema.tablesorderbydata_lengthdesclimit10;" } my_segm3_info(){ ${mysql_cmd}-H-e"selecttable_name,table_rows,concat(round(data_length/1024/1024,2),'MB')assize,data_free\ frominformation_schema.tableswheredata_free>0orderbydata_lengthdesc;" } my_obj1_info(){ ${mysql_cmd}-H-e"selecttable_schemaasdb,table_typeasobject_type,count(*)ascntfrominformation_schema.tablesgroupbytable_schema,table_typeunionall\ selectroutine_schemaasdb,routine_typeasobject_type,count(*)ascntfrominformation_schema.routinesgroupbyroutine_schema,routine_type;" } my_obj2_info(){ ${mysql_cmd}-H-e"selecttable_schema,engine,count(*)ascntfrominformation_schema.tablesgroupbytable_schema,engine;" } my_obj3_info(){ ${mysql_cmd}-H-e"selecttable_schema,table_namefrominformation_schema.tableswheretable_schemanotin('mysql','information_schema','performance_schema','sys')andtable_namenotin(\ selecttable_namefrominformation_schema.table_constraintstjoininformation_schema.key_column_usagekusing(\ constraint_name,table_schema,table_name)wheret.constraint_type='PRIMARYKEY'andtable_schemanotin('mysql','information_schema','performance_schema','sys'));" } my_lock_info(){ ${mysql_cmd}-H-e"SELECTr.trx_idwaiting_trx_id,r.trx_mysql_thread_idwaiting_thread,r.trx_querywaiting_query,\ b.trx_idblocking_trx_id,b.trx_mysql_thread_idblocking_thread,b.trx_queryblocking_query,\ bl.lock_idblocking_lock_id,bl.lock_modeblocking_lock_mode,bl.lock_typeblocking_lock_type,\ bl.lock_tableblocking_lock_table,bl.lock_indexblocking_lock_index,\ rl.lock_idwaiting_lock_id,rl.lock_modewaiting_lock_mode,rl.lock_typewaiting_lock_type,\ rl.lock_tablewaiting_lock_table,rl.lock_indexwaiting_lock_index\ FROMinformation_schema.INNODB_LOCK_WAITSw\ INNERJOINinformation_schema.INNODB_TRXbONb.trx_id=w.blocking_trx_id\ INNERJOINinformation_schema.INNODB_TRXrONr.trx_id=w.requesting_trx_id\ INNERJOINinformation_schema.INNODB_LOCKSblONbl.lock_id=w.blocking_lock_id\ INNERJOINinformation_schema.INNODB_LOCKSrlONrl.lock_id=w.requested_lock_id\G" } my_innodb_info(){ ${mysql_cmd}-e"showengineinnodbstatus\G" } my_user_info(){ ${mysql_cmd}-e"SELECTDISTINCTCONCAT('showgrantsfor''',user,'''@''',host,''';')ASqueryFROMmysql.user;">>/tmp/tmpmy_user_t_`date+%y%m%d`.txt whilereadline 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"OSBasicSummary">>$file_output create_table_head1>>$file_output get_physics>>/tmp/tmpos_summ_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmpos_summ_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"CPUInfoSummary">>$file_output create_table_head1>>$file_output get_cpuinfo>>/tmp/tmp_cpuinfo_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmp_cpuinfo_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"MEMInfoSummary">>$file_output create_table_head1>>$file_output get_meminfo whilereadline 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 whilereadline do create_tr2"$line" done</tmp/tmpmem3_h1_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmpmem3_t1_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"DiskInfoSummary">>$file_output create_table_head1>>$file_output get_diskinfo whilereadline do create_tr2"$line" done</tmp/tmpdisk_h1_`date+%y%m%d`.txt whilereadline 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"NetworkInfoSummary">>$file_output create_table_head1>>$file_output get_netinfo whilereadline do create_tr2"$line" done</tmp/tmpnet_h1_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmpnet1_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"OSLoadSummary">>$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"TOPCPUSummary">>$file_output create_table_head1>>$file_output create_tr3"/tmp/tmptopcpu_`date+%y%m%d`.txt" create_table_end>>$file_output create_html_head"TOPMEMSummary">>$file_output create_table_head1>>$file_output create_tr3"/tmp/tmptopmem_`date+%y%m%d`.txt" create_table_end>>$file_output create_html_head"TOPIOSummary">>$file_output create_table_head1>>$file_output create_tr3"/tmp/tmptopio_`date+%y%m%d`.txt" create_table_end>>$file_output create_html_head"BasicDatabaseInformation">>$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 whilereadline do create_tr1"$line" done</tmp/tmpmy_base_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"RunningStatusofDatabase">>$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"IPConnectionStatistics">>$file_output create_table_head1>>$file_output my_connip_info whilereadline do create_tr2"$line" done</tmp/tmpmy_connip_h1_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmpmy_connip_t1_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"ImportantParameters">>$file_output create_table_head1>>$file_output my_param_info whilereadline do create_tr2"$line" done</tmp/tmpmy_param_h1_`date+%y%m%d`.txt whilereadline do create_tr1"$line" done</tmp/tmpmy_param_t1_`date+%y%m%d`.txt create_table_end>>$file_output create_html_head"Sizeofeachdatabase">>$file_output my_segm1_info>>$file_output create_html_head"TOP10SpaceTable">>$file_output my_segm2_info>>$file_output create_html_head"HighWaterLevelMeter">>$file_output my_segm3_info>>$file_output create_html_head"Objecttypestatistics">>$file_output my_obj1_info>>$file_output create_html_head"StorageEngineNumberStatistics">>$file_output my_obj2_info>>$file_output create_html_head"Tableswithoutprimarykeys">>$file_output my_obj3_info>>$file_output create_html_head"Lockinformation">>$file_output my_lock_info>>$file_output create_html_head"InnodbStatusInformation">>$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"UserAuthorizationInformation">>$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"SlowSQLstatistics">>$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 } #Thisscriptmustbeexecutedasroot 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"Thisscriptmustbeexecutedasroot" exit1 fi PLATFORM=`uname` if[${PLATFORM}="HP-UX"];then echo"ThisscriptdoesnotsupportHP-UXplatformforthetimebeing" exit1 elif[${PLATFORM}="SunOS"];then echo"ThisscriptdoesnotsupportSunOSplatformforthetimebeing" exit1 elif[${PLATFORM}="AIX"];then echo"ThisscriptdoesnotsupportAIXplatformforthetimebeing" exit1 elif[${PLATFORM}="Linux"];then echo-e" ########################################################################################### ## #Makesurethatthefollowingparametersatthebeginningofthescriptarecorrect.# #myuser="root"(DatabaseAccount)# #mypasswd="password"(Databasepassword)# #myip="192.168.11.101"(DatabasenativeIP)# #myport="3307"(Databaseport)# #Otherwise,thescriptcannotbeexecutedproperly.# ## ########################################################################################### " #read-p"Thedatabaseconnectioninformationisconfiguredcorrectly.Pleaseexecute[yesory]:"SELECT #printf'\n' #if[$SELECT=="yes"-o$SELECT=="y"];then create_html #else #exit1 #fi fi
