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

监控数据库每个表的增长量时发现，在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 AUTOINCREMENT,
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 logw(text):#写日志
logfile = "datasize.txt"
f = open(logfile,'a+')
text = text+'\n'
f.write(text)
f.close()
def log2db(sizelog):#把结果写入数据库
loghost = '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()