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

database

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

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

郑州输卵管造影费用是多少: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 "<html>

<head>

<style type="text/css">

body {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}

table,tr,td {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}

th {font:bold 12px Courier New,Helvetica,sansserif; color:White; background:#0033FF; padding:0px 0px 0px 0px;}

h1 {font:bold 12pt Courier New,Helvetica,sansserif; color:Black; padding:0px 0px 0px 0px;}

</style>

</head>

<body>"

}

create_html_head(){

echo -e "<h1>$1</h1>"

}

create_table_head1(){

echo -e "<table width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"

}

create_table_head2(){

echo -e "<table width="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>

<pre style="font-family:Courier New; 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

""${NAME_VAL_LEN}""$1""$2"

}

get_virtual (){

local file="/var/log/dmesg"

ifgrep -qi -e "vmware" -e "vmxnet" -e "paravirtualized kernel on vmi""${file}";then

echo"VMWare";

elifgrep -qi -e "paravirtualized kernel on xen" -e "Xen virtual console""${file}";then

echo"Xen";

elifgrep -qi "qemu""${file}";then

echo"QEmu";

elifgrep -qi "paravirtualized kernel on KVM""${file}";then

echo"KVM";

elifgrep -q "VBOX""${file}";then

echo"VirtualBox";

elifgrep -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

whileread 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

whileread 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

whileread 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

whileread 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

whileread line

do

create_tr2 "$line"

done< /tmp/tmpmem3_h1_`date +%y%m%d`.txt

whileread 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

whileread line

do

create_tr2 "$line"

done< /tmp/tmpdisk_h1_`date +%y%m%d`.txt

whileread 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

whileread line

do

create_tr2 "$line"

done< /tmp/tmpnet_h1_`date +%y%m%d`.txt

whileread 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

whileread 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

whileread line

do

create_tr2 "$line"

done< /tmp/tmpmy_connip_h1_`date +%y%m%d`.txt

whileread 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

whileread line

do

create_tr2 "$line"

done< /tmp/tmpmy_param_h1_`date +%y%m%d`.txt

whileread 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

以上是 通过shell检查mysql主机和数据库,生成html报表的脚本 的全部内容, 来源链接: utcz.com/z/533254.html

回到顶部