最近需要监控数据库每个表的增长量，其实在mysql中的information_schema.TABLES表中有记录表的大小，但是不准，要是计算每天每个表大小的话不是很准确，刚好我的mysql是独享表空间，所以只要计算出数据目录中的表文件大小即可实现这个目的。以下代码实现了计算在独享表空间下，计算数据库中所有表的物理大小，并计算整个mysql数据库目录的大小和数据库目录所在分区的剩余空间。以下是代码：
|
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/bin/env python
# -*- coding: utf-8 -*-
import os,time,MySQLdb
''
'CREATE TABLE DBA.datasize (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host` varchar(20) NOT NULL COMMENT
'服务器IP'
,
`dataname` varchar(100) NOT NULL COMMENT
'数据库名字'
,
`tablename` varchar(100) NOT NULL COMMENT
'表名字'
,
`datasize` double NOT NULL COMMENT
'表大小，单位:M'
,
`uptime` datetime NOT NULL COMMENT
'更新时间'
,
PRIMARY KEY (`id`,`host`,`dataname`,`tablename`,`datasize`,`uptime`),
KEY `index_uptime` (`uptime`),
KEY `index_tablename` (`tablename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
''
' #表结构
def log_w(text):#写日志
logfile =
"datasize.txt"
f = open(logfile,
'a+'
)
text = text+
'\n'
f.write(text)
f.close()
def log2db(size_log):#把结果写入数据库
log_host =
'192.168.100.100'
log_user =
'wangwei'
log_pass =
'wangwei'
try
:
conn = MySQLdb.connect(host = log_host,port = 3306,user = log_user,passwd = log_pass,charset=
'utf8'
,connect_timeout=20)
cursor = conn.cursor()
cursor.executemany(
"insert into DBA.datasize (`host`,`dataname`,`tablename`,`datasize`,`uptime`) VALUES(%s,%s,%s,%s,%s)"
,tuple(size_log))
conn.commit()
cursor.close()
conn.close()
except Exception,e:
print
e
def main():
uptime = time.
strftime
(
"%Y-%m-%d %H:%M:%S"
)
text =
"======================== %s ======================"
% uptime
print
text
#log_w(text)
mysqldir =
"/home/mysql/"
tables = {}
host =
'192.168.100.10'
#数据库本地IP
conm =
'du -sh %s'
% mysqldir
datasize = os.popen(conm).readlines()[0].split(
'\t'
)[0]
dir_list = os.listdir(mysqldir)
for
i in dir_list:
dirname = os.path.join(mysqldir,i)
if
os.path.isdir(dirname):
tb_list = os.listdir(dirname)
table_list = list(set([os.path.splitext(ii)[0]
for
ii in tb_list]))
for
t_name in table_list:
t_size = 0
for
t in tb_list:
if
t_name in t:
f_size = os.path.getsize(os.path.join(dirname,t))
t_size = t_size + f_size
t_size = t_size/1024/1024
if
t_size != 0:
tables[os.path.join(i,t_name)]=t_size
tables = sorted(tables.iteritems(),key = lambda asd:asd[1],reverse = True)
size_log = []
for
i in tables:
text = str(i[0]).ljust(70)+str(i[1])+
'M'
aa = i[0].split(
"/"
)
res = [host,aa[0],aa[1],i[1],uptime]
size_log.append(res)
#log_w(text)
print
text
text =
"All DataSize :"
.ljust(70)+str(datasize)
size_log.append([host,
"all"
,
"all"
,int(datasize.split(
'G'
)[0])*1024,uptime])
diskfree = os.popen(
"df -h|grep data"
).readlines()[0].split()[3]
size_log.append([host,
"disk"
,
"free"
,int(diskfree.split(
'G'
)[0])*1024,uptime])
#log_w(text)
print
text
text =
"Data Disk free size:"
.ljust(70)+diskfree
#log_w(text)
print
text
log2db(size_log)
if
__name__==
'__main__'
:
main()
本文转自 lover00751CTO博客，原文链接：http://blog.51cto.com/wangwei007/1321517，如需转载请自行联系原作者
|
