计算mysql数据库目录中表文件大小并排序



最近需要监控数据库每个表的增长量，其实在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 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()

本文出自 “王伟” 博客，请务必保留此出处