#!/bin/bash
ipaddress=`ip a|grep "global"|awk '{print $2}' |awk -F/ '{print $1}'`
file_output='os_mysql_simple_summary.html'
td_str=''
th_str=''
myuser="root"
mypasswd="123456"
myip="192.168.63.141"
myport="3306"
mysql_cmd="mysql -u${myuser} -p${mypasswd} -h${myip} -P${myport} --protocol=tcp --silent"
#yum -y install bc sysstat net-tools
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\n" "${NAME_VAL_LEN}" "$1" "$2"
}
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
}
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 variables like '%binlog%';"|awk '{print $1,":",$2}'
${mysql_cmd} -e "show variables like 'innodb_flush%';"|awk '{print $1,":",$2}'
}
my_stat_info(){
${mysql_cmd} -e status >>/tmp/tmpmy_stat_`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
}
create_html(){
rm -rf $file_output
touch $file_output
create_html_css >> $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 "Basic Database && binlog 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 "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_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}'`
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="XXXXXX" (Database password)
#myip="localhost" (Database native IP)
#myport="3306" (Database port)
#--> Otherwise, the script cannot be executed properly.
#GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' identified by 'XXXXX' WITH GRANT OPTION;
#flush privileges;
###########################################################################################
"
create_html
fi
git地址：https://github.com/domdanrtsey/mywatch.git
多实例下运行此脚本时，要注意区分不同实例的root用户密码与对应端口号
myuser="root"
mypasswd="XXXXX"
myip="localhost"
myport="3306"
#!/usr/bin/env python3
import psutil
import mysql.connector
import argparse
import json
import datetime
def get_cpu_info(verbose):
cpu_info={}
if verbose >0:
print("[cpu] start collect cpu info ...")
data=psutil.cpu_times_percent(3)
cpu_info['user']=data[0]
cpu_info['system']=data[2]
cpu_info['idle']=data[3]
cpu_info['iowait']=data[4]
cpu_info['hardirq']=data[5]
cpu_info['softirq']=data[6]
cpu_info['cpu_cores']=psutil.cpu_count()
if verbose >0:
print("{0}".format(json.dumps(cpu_info,ensure_ascii=False,indent=4)))
print("[cpu] collection compeleted ...")
return cpu_info
def get_mem_info(verbose):
mem_info={}
if verbose >0:
print("[mem] start collect mem info ...")
data=psutil.virtual_memory()
mem_info['total']=data[0]/1024/1024/1024
mem_info['avariable']=data[1]/1024/1024/1024
if verbose>0:
print("{0}".format(json.dumps(mem_info,ensure_ascii=False,indent=4)))
print("[mem] collection compeletd ...")
return mem_info
def get_disk_info(verbose):
disk_info={}
if verbose >0:
print("[disk] start collect disk info ...")
partitions=psutil.disk_partitions()
partitions=[(partition[1],partition[2])for partition in partitions if partition[2]!='iso9660']
disk_info={}
for partition in partitions:
disk_info[partition[0]]={}
disk_info[partition[0]]['fstype']=partition[1]
for mount_point in disk_info.keys():
data=psutil.disk_usage(mount_point)
disk_info[mount_point]['total']=data[0]/1024/1024/1024
disk_info[mount_point]['used_percent']=data[3]
if verbose >0:
print("{0}".format(json.dumps(disk_info,ensure_ascii=False,indent=4)))
print("[disk] collection compeleted ....")
return disk_info
def get_mysql_info(cnx_args,status_list):
config={
'user':cnx_args.user,
'password':cnx_args.password,
'host':cnx_args.host,
'port':cnx_args.port}
cnx=None
cursor=None
mysql_info={}
try:
cnx=mysql.connector.connect(**config)
cursor=cnx.cursor(prepared=True)
for index in range(len(status_list)):
status_list[index].get_status(cursor)
status=status_list[index]
mysql_info[status.name]=status.value
mysql_info['port']=config['port']
except mysql.connector.Error as err:
print(err)
finally:
if cursor != None:
cursor.close()
if cnx != None:
cnx.close()
return mysql_info
class Status(object):
def __init__(self,name):
self.name=name
self._value=None
def get_status(self,cursor):
stmt="show global status like '{0}';".format(self.name)
cursor.execute(stmt)
value=cursor.fetchone()[1].decode('utf8')
self._value=int(value)
@property
def value(self):
if self._value==None:
raise Exception("cant get value befor execute the get_status function")
else:
return self._value
IntStatus=Status
class diskResource(object):
def __init__(self,mount_point,status):
self.mount_point=mount_point
self.status=status
def __str__(self):
result=''' <div class="stage-list">
<div class="stage-title"><span>{0}</span></div>
<div class="detail">
<p class="detail-list">
<span class="detail-title">区分格式</span>
<span class="detail-describe">{1}</span>
</p>
<p class="detail-list">
<span class="detail-title">总空间大小</span>
<span class="detail-describe">{2:8.2f}G</span>
</p>
<p class="detail-list">
<span class="detail-title">空闲空间(%)</span>
<span class="detail-describe">{3:8.2f}</span>
</p>
<p class="detail-list">
</p>
</div>
</div>\n'''.format(self.mount_point,self.status['fstype'],self.status['total'],self.status['used_percent'])
return result
class diskResources(object):
def __init__(self,status):
self.disks=[]
for mount_point in status.keys():
self.disks.append(diskResource(mount_point,status[mount_point]))
def __str__(self):
result=''' <div class="list-item">
<div class="category">
<span>磁盘</span>
</div>
<div class="second-stage">\n'''
for index in range(len(self.disks)):
result=result+self.disks[index].__str__()
result=result+''' </div>
</div>\n'''
return result
class cpuResources(object):
def __init__(self,status):
self.status=status
def __str__(self):
result=''' <div class="list-item">
<div class="category">
<span>CPU</span>
</div>
<div class="second-stage">
<div class="stage-list">
<div class="stage-title"><span>global</span></div>
<div class="detail">
<p class="detail-list">
<span class="detail-title">用户空间使用（%）</span>
<span class="detail-describe">{0}</span>
</p>
<p class="detail-list">
<span class="detail-title">内核空间使用（%）</span>
<span class="detail-describe">{1}</span>
</p>
<p class="detail-list">
<span class="detail-title">空闲（%）</span>
<span class="detail-describe">{2}</span>
</p>
<p class="detail-list">
<span class="detail-title">硬中断（%）</span>
<span class="detail-describe">{3}</span>
</p>
<p class="detail-list">
<span class="detail-title">软中断（%）</span>
<span class="detail-describe">{4}</span>
</p>
<p class="detail-list">
<span class="detail-title">io等待（%）</span>
<span class="detail-describe">{5}</span>
</p>
<p class="detail-list">
</p>
</div>
</div>
</div>
</div>\n'''.format(self.status['user'],self.status['system'],self.status['idle'],self.status['hardirq'],self.status['softirq'],self.status['iowait'])
return result
class memResources(object):
def __init__(self,status):
self.status=status
def __str__(self):
result=''' <div class="list-item">
<div class="category">
<span>MEM</span>
</div>
<div class="second-stage">
<div class="stage-list">
<div class="stage-title"><span>global</span></div>
<div class="detail">
<p class="detail-list">
<span class="detail-title">总大小</span>
<span class="detail-describe">{0:8.2f}G</span>
</p>
<p class="detail-list">
<span class="detail-title">空闲大小</span>
<span class="detail-describe">{1:8.2f}G</span>
</p>
<p class="detail-list">
</p>
</div>
</div>
</div>
</div>'''.format(self.status['total'],self.status['avariable'])
return result
class mysqlResources(object):
def __init__(self,status):
self.status=status
def __str__(self):
result=''' <div class="list-item">
<div class="category">
<span>MYSQL</span>
</div>
<div class="second-stage">
<div class="stage-list">
<div class="stage-title"><span>{0}</span></div>
<div class="detail">
<p class="detail-list">
<span class="detail-title">innodb_log_wait</span>
<span class="detail-describe">{1}</span>
</p>
<p class="detail-list">
<span class="detail-title">binlog_cache_use</span>
<span class="detail-describe">{2}</span>
</p>
<p class="detail-list">
<span class="detail-title">create_temp_disk_table</span>
<span class="detail-describe">{3}</span>
</p>
<p class="detail-list">
<span class="detail-title">Slow_querys</span>
<span class="detail-describe">{4}</span>
</p>
<p class="detail-list">
</p>
</div>
</div>
</div>
</div>'''.format(self.status['port'],self.status['Innodb_log_waits'],self.status['Binlog_cache_use'],
self.status['Created_tmp_disk_tables'],self.status['Slow_queries'])
return result
class hostResources(object):
def __init__(self,cpu_info,mem_info,disk_info,mysql_info,report_title='MySQL巡检报告'):
self.cpu=cpuResources(cpu_info)
self.mem=memResources(mem_info)
self.disk=diskResources(disk_info)
self.mysql=mysqlResources(mysql_info)
self.report_title=report_title
def __str__(self):
result='''<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>巡检报告</title>
<style>
*{
margin: 0;
padding: 0;
}
.content{
width:1000px;
height: auto;
margin: 30px auto;
border-bottom:1px solid #b2b2b2;
}
.list-item{
border:1px solid #b2b2b2;
border-bottom: none;
transition: all .35s;
overflow: hidden;
display: flex;
}
.list-item:empty{
display: none;
}
.top-title{
line-height: 32px;
font-size: 16px;
color: #333;
text-indent: 10px;
font-weight: 600;
}
.category{
width:97px;
height: auto;
border-right: 1px solid #b2b2b2;
float: left;
text-align: center;
position: relative;
}
.stage-title>span,
.category>span{
display: block;
height: 20px;
width:100%;
text-align: center;
line-height: 20px;
position: absolute;
top: 50%;
margin-top: -10px;left: 0;
}
.second-stage{
width:900px;
float: left;
}
.stage-list{
border-bottom: 1px solid #b2b2b2;
display: flex;
}
.stage-list:last-child{
border-bottom: 0;
}
.stage-title{
width:99px;
border-right: 1px solid #b2b2b2;
position: relative;
}
.detail{
flex: 1;
}
.detail-list{
border-bottom: 1px solid #b2b2b2;
height: 40px;
display: flex;
transition: all .35s;
}
.detail-title{
padding: 10px;
height: 20px;
line-height: 20px;
border-right: 1px solid #b2b2b2;
width:200px;
}
.detail-describe{
flex: 1;
padding: 10px;line-height: 20px;
}
.detail-list:last-child{
border-bottom: 0;
}
.list-item:hover{
background-color: #eee;
}
.detail-list:hover{
background-color: #d1d1d1;
}
</style>
</head>
<body>
<div class="content">
<div class="list-item">
<p class="top-title">report_title</p>
</div>\n'''
result=result.replace('report_title',self.report_title)
result=result+self.cpu.__str__()
result=result+self.mem.__str__()
result=result+self.disk.__str__()
result=result+self.mysql.__str__()
result=result+''' </div>
</body>
</html>'''
return result
if __name__=="__main__":
parser=argparse.ArgumentParser()
parser.add_argument('--verbose',type=int,default=1,help='verbose for output')
parser.add_argument('--user',default='root',help='user name for connect to mysql')
parser.add_argument('--password',default='123456',help='user password for connect to mysql')
parser.add_argument('--host',default='192.168.63.141',help='mysql host ip')
parser.add_argument('--port',default=3306,type=int,help='mysql port')
parser.add_argument('--int-status',default=('Com_select,Com_insert,Com_update,Com_delete,Innodb_log_waits,'
'Binlog_cache_disk_use,Binlog_cache_use,Created_tmp_disk_tables,'
'Slow_queries')
,help='mysql status its value like int')
parser.add_argument('--report-title',default='MySQL巡检报告',help='report title')
parser.add_argument('--output-dir',default='/tmp/',help='default report file output path')
args=parser.parse_args()
cpu_info=get_cpu_info(args.verbose)
mem_info=get_mem_info(args.verbose)
disk_info=get_disk_info(args.verbose)
status_list=[ IntStatus(name=item) for item in args.int_status.split(',')]
mysql_info=get_mysql_info(args,status_list)
#dr=diskResources(disk_info)
#cr=cpuResources(cpu_info)
#mr=memResources(mem_info)
#msr=mysqlResources(mysql_info)
hr=hostResources(cpu_info,mem_info,disk_info,mysql_info,args.report_title)
now=str(datetime.datetime.now()).replace(' ','^')
if args.output_dir.endswith('/') != True:
args.output_dir=args.output_dir+'/'
filename=args.output_dir+'mysql_inspection_{0}.html'.format(now)
with open(filename,'w') as output:
output.write(hr.__str__())
print('[report] the report been saved to {0} ok.... ....'.format(filename))
8821
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
