mysql 表大小 排序_计算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 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()