由两个程序文件组成，第一个程序实现慢查询分析，文件上传
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
#!/usr/local/python27/bin/python2.7
from
fabric.api
import
*
from
fabric.context_managers
import
*
from
fabric.contrib.console
import
confirm
import
sys
import
os
import
time
import
datetime
sdir
=
'/alidata/server/mysql/data/'
ddir
=
'/root/'
env.user
=
'root'
env.roledefs
=
{
'master_db'
:[
'192.168.1.2'
],
'slave_db'
:[
'192.168.1.3'
],
'quliao_db'
:[
'192.168.1.4'
]
}
env.passwords
=
{
'root@192.168.1.2:22'
:
'123456'
,
'root@192.168.1.3:22'
:
'123456'
,
'root@192.168.1.4:22'
:
'123456'
}
#慢查询分析函数，这个函数会调用服务端本地的工具分析慢查询，之后会清空慢查询文件。
def
my_slow(ori_log,save_log,result_log):
run(
'cp '
+
sdir
+
ori_log
+
' '
+
save_log)
run(
'/usr/local/bin/mysqlsla -lt slow '
+
ddir
+
save_log
+
' -sort c_sum -top 10 >'
+
'/root/'
+
result_log)
run(
'echo '
+
'
'+'
>'
+
sdir
+
ori_log)
#用于下载服务端分析好的慢查询文件保存到本地新建的目录中
def
my_get(fname):
with lcd(
'/alidata/slow_log'
):
dname
=
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
with cd(
'/root/'
):
get(fname,dname
+
'/'
)
#定义文件删除函数，接受两个参数，用于删除拷贝出来的慢查询文件和分析之后的慢查询文件。
def
remove(f1,f2):
with cd(
'/root'
):
run(
'rm -f '
+
f1
+
' '
+
f2 )
#在客户端机器创建一个以当前日期为名称的文件夹，用于保存分析好的慢查询文件
@runs_once
def
mk_dir():
with lcd(
'/alidata/slow_log'
):
dname
=
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
local(
'mkdir '
+
dname)
@roles
(
'slave_db'
)
def
sdb():
print
(
"run slave db pro"
)
run(
"ls /alidata/server/mysql/data"
)
my_slow(
'slow.log'
,
'mysql-r-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
my_get(
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
remove(
'mysql-r-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
@roles
(
'master_db'
)
def
mdb():
print
(
'run master db pro'
)
run(
"ls /alidata/server/mysql/data"
)
my_slow(
'AY131008162509536ef1Z-slow.log'
,
'mysql-w-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
my_get(
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
remove(
'mysql-w-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
@roles
(
'quliao_db'
)
def
ldb():
print
(
'run quliao db pro'
)
run(
"ls /alidata/server/mysql/data"
)
my_slow(
'slow.log'
,
'mysql-q-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
my_get(
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
remove(
'mysql-q-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
def
deploy():
mk_dir()
execute(sdb)
execute(mdb)
execute(ldb)
#最后这里是调用客户端本地的邮件发送程序把刚刚抓回来的慢查询日志以邮件附件的形式发送给管理员。
local(
'/root/tuchao/sendma.py'
)
|
第二个程序负责把分析好的日志以邮件附件的形式发送给管理员
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
#!/usr/local/python27/bin/python2.7
# coding=utf8
import
smtplib
import
time
import
datetime
from
email.mime.text
import
MIMEText
from
email
import
encoders
from
email.header
import
Header
from
email.utils
import
parseaddr, formataddr
from
email.MIMEMultipart
import
MIMEMultipart
from
email.MIMEBase
import
MIMEBase
from_addr
=
'tuchao@mail.admin.net'
password
=
'111111'
smtp_server
=
'114.234.116.2'
to_addr
=
[
'123456@qq.com'
,
'1234567@qq.com'
]
slow_log_path
=
'/alidata/slow_log/'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'/'
def
_format_addr(s):
name,addr
=
parseaddr(s)
return
formataddr((Header(name,
'utf-8'
).encode(),addr.encode(
'utf-8'
)
if
isinstance
(addr,
unicode
)
else
addr))
#需要将多个对象组合起来，在这里构建一个MIMEMultipart对象。
msg
=
MIMEMultipart()
msg[
'From'
]
=
_format_addr(u
'来自运维技术XX <%s>'
%
from_addr)
msg[
'To'
]
=
_format_addr(u
'管理员 <%s>'
%
to_addr)
msg[
'Subject'
]
=
Header(u
'MySQL慢查询日志'
,
'utf-8'
).encode()
msg.attach(MIMEText(
'<html><body><h3>mysql-read:从库读慢查询</h3>'
+
'<p><h3>mysql-write:主库读写慢查询</h3></p>'
'<p><h3>mysql-quliao:外部程序库读写慢查询</h3></p>'
'<p><img src="cid:1"></p>'
+
'</body></html>'
,
'html'
,
'utf-8'
))
with
open
(slow_log_path
+
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'rb'
) as f:
mime
=
MIMEBase(
'text/plain'
,
'txt'
,filename
=
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-Disposition'
,
'attachment'
,filename
=
'mysql-read-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-ID'
,
'<0>'
)
mime.add_header(
'X-Attachment-Id'
,
'0'
)
mime.set_payload(f.read())
encoders.encode_base64(mime)
msg.attach(mime)
with
open
(slow_log_path
+
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'rb'
) as f:
mime
=
MIMEBase(
'text/plain'
,
'txt'
,filename
=
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-Disposition'
,
'attachment'
,filename
=
'mysql-write-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-ID'
,
'<0>'
)
mime.add_header(
'X-Attachment-Id'
,
'0'
)
mime.set_payload(f.read())
encoders.encode_base64(mime)
msg.attach(mime)
with
open
(slow_log_path
+
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
,
'rb'
) as f:
mime
=
MIMEBase(
'text/plain'
,
'txt'
,filename
=
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-Disposition'
,
'attachment'
,filename
=
'mysql-quliao-'
+
time.strftime(
'%Y%m%d'
,time.localtime(time.time()))
+
'.log'
)
mime.add_header(
'Content-ID'
,
'<0>'
)
mime.add_header(
'X-Attachment-Id'
,
'0'
)
mime.set_payload(f.read())
encoders.encode_base64(mime)
msg.attach(mime)
with
open
(
'/alidata/slow_log/aamy.jpg'
,
'rb'
) as f:
mime
=
MIMEBase(
'image'
,
'jpg'
,filename
=
'aamy.jpg'
)
mime.add_header(
'Content-Disposition'
,
'attachment'
,filename
=
'aamy.jpg'
)
mime.add_header(
'Content-ID'
,
'<1>'
)
mime.add_header(
'X-Attachment-Id'
,
'0'
)
mime.set_payload(f.read())
encoders.encode_base64(mime)
msg.attach(mime)
server
=
smtplib.SMTP(smtp_server,
25
)
server.set_debuglevel(
0
)
server.login(from_addr,password)
server.sendmail(from_addr,to_addr,msg.as_string())
server.quit()
|
构造一个邮件对象就是一个Messag对象
构造一个MIMEText对象，就表示一个文本邮件对象
构造一个MIMEImage对象，就表示一个作为附件的图片
要把多个对象组合起来，就用MIMEMultipart对象
MIMEBase可以表示任何对象
本文转自qw87112 51CTO博客，原文链接:
http://blog.51cto.com/tchuairen/1707669
