最近需要监控数据库每个表的增长量,其实在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()
阅读(2393) | 评论(0) | 转发(0) |