最近需要监控数据库每个表的增长量，其实在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.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 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本文来源gaodai#ma#com搞*!代#%^码$网*:”.ljust(70)+diskfree#log_w(text)print textlog2db(size_log)if __name__==’__main__’:main()
本文出自 “王伟” 博客，请务必保留此出处
转载请注明原文链接：计算mysql数据库目录中表文件大小并排序
