最近需要监控数据库每个表的增长量，其实在mysql中的information_schema.tables表中有记录表的大小，但是不准，要是计算每天每个表大小的话不是很准确，刚好我的
最近需要监控数据库每个表的增长量，其实在mysql中的information_schema.tables表中有记录表的大小，但是不准，要是计算每天每个表大小的话不是很准确，刚好我的mysql是独享表空间，，所以只要计算出数据目录中的表文件大小即可实现这个目的。以下代码实现了计算在独享表空间下，计算数据库中所有表的物理大小，并计算整个mysql数据库目录的大小和数据库目录所在分区的剩余空间。以下是代码：
#!/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.txtf = 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 edef main():uptime = time.strftime(%y-%m-%d %h:%m:%s)text = ======================== %s ====================== % uptimeprint text#log_w(text)mysqldir = /home/mysql/tables = {}host = '192.168.100.10'#数据库本地ipconm = 'du -sh %s' % mysqldirdatasize = 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 = 0for t in tb_list:if t_name in t:f_size = os.path.getsize(os.path.join(dirname,t))t_size = t_size + f_sizet_size = t_size/1024/1024if t_size != 0:tables[os.path.join(i,t_name)]=t_sizetables = 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 texttext = 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 texttext = data disk free size:.ljust(70)+diskfree#log_w(text)print textlog2db(size_log)if __name__=='__main__':main()
本文出自 “王伟” 博客，请务必保留此出处