最近开发需要检查生产库中每个表的字段设置是否合理,比如tinyint是否快达到上线峰值了.于是做了一个Python脚本,这里贴出来分享一下
(转载请注明作者出处)
[mysql@localhost ~]$ cat check_field.py
#!/usr/bin/env python
#coding:utf8
from __future__ import division
import os
import sys
import time
from commands import getstatusoutput as getso
user = 'xxxxxx' ---用户
password= 'xxxxxx' ---密码
aaa = '''请正确填写参数！
例如: python check_field.py -h hostname -p port'''
if len(sys.argv) != 5:
print sys.argv
print aaa
sys.exit(1)
print 1
elif sys.argv[1] != '-h' or sys.argv[3] != '-p':
print aaa
sys.exit(1)
else:
pass
def TTT(single, total):
remove = single/total
return remove
os.system('''clear ''')
print "\033[1;32;40m库名 表名 字段 类型 最大值 范围\033[0m"
Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'show databases' ''' % (sys.argv[2],user,password,sys.argv[4])
status, result_database = getso(Cmd)
if status != 0:
print result_database
else:
for i in result_database.split('\n'):
if i == 'Database' or i == 'information_schema' or i == 'mysql' or i == 'performance_schema':
pass
else:
Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; show tables' ''' % (sys.argv[2],user,password,sys.argv[4],i)
status, result_table = getso(Cmd)
for Biao in result_table.split('\n')[1:]:
Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; desc %s' ''' % (sys.argv[2],user,password,sys.argv[4],i,Biao)
status, result_field = getso(Cmd)
for Field in result_field.split('\n')[1:]:
arg = Field.split()
if 'int' in arg[1]:
Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; select max(`%s`) from %s' ''' % (sys.argv[2],user,password,sys.argv[4],i,arg[0],Biao)
status, result_value = getso(Cmd)
if 'tinyint' in arg[1]:
try:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 255)
else:
Back = TTT(int(result_value.split('\n')[1]), 127)
#print Back
except ValueError:
Back = 20
rang = '有符号：-127 ~ 127 无符号：0 ~ 255'
elif 'smallint' in arg[1]:
try:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 65535)
else:
Back = TTT(int(result_value.split('\n')[1]), 32767)
except ValueError:
Back = 20
rang = '有符号：-32768 ~ 32767 无符号：0 ~ 65535 '
elif 'mediumint' in arg[1]:
try:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 16777215)
else:
Back = TTT(int(result_value.split('\n')[1]), 8388607)
except ValueError:
Back = 20
rang = '有符号：-8388608 ~ 8388607 无符号：0 ~ 16777215 '
elif 'bigint' in arg[1]:
try:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 18446744073709551615)
else:
Back = TTT(int(result_value.split('\n')[1]), 9223372036854775807)
except ValueError:
Back = 20
rang = '有符号：-9223372036854775808 ~ 9223372036854775807 无符号：0 ~ 18446744073709551615'
elif 'integer' in arg[1] or 'int' in arg[1]:
try:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 4294967295)
else:
Back = TTT(int(result_value.split('\n')[1]), 2147483647)
except ValueError:
Back = 20
rang = '有符号：-2147483648 ~ 2147483647 无符号：0 ~ 4294967295'
else:
if 'unsigned' == arg[2]:
Back = TTT(int(result_value.split('\n')[1]), 255)
else:
Back = TTT(int(result_value.split('\n')[1]), 127)
rang = ''
if 'unsigned' == arg[2]:
Type_C = arg[1]+' '+arg[2]
else:
Type_C = arg[1]
DataBase_Len = len(i)
Table_Len = len(Biao)
Field_Len = len(arg[0])
Type_Len = len(arg[1])
Value_Len = len(result_value.split('\n')[1])
# if Back < 0.0001 or Back > 80:
Form = i+' '*(19-DataBase_Len)+Biao+' '+' '*(39-Table_Len)+arg[0]+' '+' '*(29-Field_Len)+Type_C+' '+' '*(19-Type_Len)+result_value.split('\n')[1]+' '*(19-Value_Len)+rang
print Form
#print i,Biao,arg[0], arg[1],result_value.split('\n')[1]
#time.sleep(5)
执行方式:
(1)需要在MySQL中建立相应的用户
(2)[mysql@localhost ~]$python check_field.py -h localhost -p 3306
结果样式如下:
库名 表名 字段 类型 最大值 范围
P-test test uid mediumint(8) unsigned 2057275 有符号：-8388608 ~ 8388607 无符号：0 ~ 16777215
阅读(6279) | 评论(0) | 转发(0) |
