def check_qps(host,port,user,passwd,db="",charset="utf8"):
"""统计各MySQL实例的QPS
"""
db = DB(host,port,user,passwd,db,charset,cursorclass="")
en = Encrypt()
db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
rows = db.fetchall()
for row in rows:
db.execute("""SELECT UPTIME,QUESTIONS FROM T_QPS WHERE INSTID = {0}
ORDER BY ID DESC LIMIT 1""".format(row.get('ID')))
insertrow = db.fetchone()
if insertrow == None:
insertrow = dict()
tmpdb = DB(row.get('IPADDR'),row.get('PORT'),row.get('USER'),en.decrypt(row.get('PASSWD')))
tmpdb.execute("show global status like 'Uptime'")
uprow = tmpdb.fetchone()
tmpdb.execute("show global status like 'Questions'")
qurow = tmpdb.fetchone()
db.execute("""INSERT INTO T_QPS(INSTID,UPTIME,QUESTIONS,DIFFUPTIME,DIFFQUESTIONS)
VALUES({0},{1},{2},{3},{4})""".format(
row.get('ID'), uprow[1], qurow[1],
int(uprow[1])-int(insertrow.get('UPTIME',0)),
int(qurow[1])-int(insertrow.get('QUESTIONS', 0))
))
db.commit()
def check_hitrate(host, port, user, passwd, db="", charset="utf8"):
"""
"""
db = DB(host, port, user, passwd, db, charset, cursorclass="")
en = Encrypt()
db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
rows = db.fetchall()
sql = "INSERT INTO T_HITRATE VALUES(NULL,'%s','%s','%s')"
for row in rows:
tmpdb = DB(row.get("IPADDR"), row.get("PORT"), row.get("USER"), en.decrypt(row.get("PASSWD")))
tmpdb.execute("show global status like 'Qcache_hits'")
hits = tmpdb.fetchone()
tmpdb.execute("show global status like 'Com_select'")
selects = tmpdb.fetchone()
try:
hit_rate = int(hits[1]) / int(hits[1] + selects[1])
except ZeroDivisionError:
hit_rate = 0
# 00.0%
db.execute(sql % (row.get("ID"), datetime.datetime.now(), int(hit_rate)))
db.commit()
def main():
en = Encrypt()
cg = parser_config()
db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
SQL = "SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE"
db.execute(SQL)
rows = db.fetchall()
for row in rows:
tmpdb = DB(row[1],row[2],row[3],en.decrypt(row[4]))
tmpdb.execute("SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST")
cnt = tmpdb.fetchone()
db.execute("""INSERT INTO T_CONNECTION(INSTANCE,CNT)
VALUES({0}, {1})""".format(row[0], cnt[0]))
db.commit()
def check(host,port,user,passwd="",db="",charset="utf8"):
"""
"""
db = DB(host,port,user,passwd,db,charset)
cnt = db.execute("SHOW SLAVE STATUS")
if cnt == 0:
return None
row = db.fetchone()
replicat_status = dict(zip(
("SLAVE_IO_STATE","MASTER_HOST","MASTER_USER","MASTER_PORT","CONNECT_RETRY",
"MASTER_LOG_FILE","READ_MASTER_LOG_POS","RELAY_LOG_FILE","RELAY_LOG_POS",
"RELAY_MASTER_LOG_FILE","SLAVE_IO_RUNNING","SLAVE_SQL_RUNNING","REPLICATE_DO_DB",
"REPLICATE_IGNORE_DB","REPLICATE_DO_TABLE","REPLICATE_IGNORE_TABLE",
"REPLICATE_WILD_DO_TABLE","REPLICATE_WILD_IGNORE_TABLE","LAST_ERRNO","LAST_ERROR",
"SKIP_COUNTER","EXEC_MASTER_LOG_POS","RELAY_LOG_SPACE","UNTIL_CONDITION",
"UNTIL_LOG_FILE","UNTIL_LOG_POS","MASTER_SSL_ALLOWED","MASTER_SSL_CA_FILE",
"MASTER_SSL_CA_PATH","MASTER_SSL_CERT","MASTER_SSL_CIPHER","MASTER_SSL_KEY",
"SECONDS_BEHIND_MASTER","MASTER_SSL_VERIFY_SERVER_CERT","LAST_IO_ERRNO","LAST_IO_ERROR",
"LAST_SQL_ERRNO","LAST_SQL_ERROR","REPLICATE_IGNORE_SERVER_IDS","MASTER_SERVER_ID")
,row))
return replicat_status
class DB_Info(object):
"""数据库信息
"""
def __init__(self,host,port=3306,user="",passwd="",db="",charset="utf8"):
"""
"""
self.db = DB(host,port,user,passwd,db,charset)
def get_status(self):
"""
"""
self.db.execute("show status")
rows = self.db.fetchall()
return dict([(row[0].upper(),row[1]) for row in rows])
def get_master(self):
"""
"""
self.db.execute("SHOW MASTER STATUS")
row = self.db.fetchone()
master_status = dict(zip(
("FILE", "POSITION", "BINLOG_DO_DB", "BINLOG_IGNORE_DB"), row
))
cnt = self.db.execute("SHOW SLAVE HOSTS")
rows = self.db.fetchall()
master_hosts= list()
for row in rows:
master_hosts.append(
dict(zip(
("SERVER_ID", "HOST", "PORT", "MASTER_ID"),
row)))
return master_status, master_hosts
def get_slave(self):
"""
"""
self.db.execute("SHOW STATUS")
rows = self.db.fetchall()
server_status = dict([(row[0].upper(),row[1]) for row in rows])
cnt = self.db.execute("SHOW SLAVE STATUS")
row = self.db.fetchone()
if cnt == 0:
print "Slave host not start."
return server_status, None
replicat_status = dict(zip(
("SLAVE_IO_STATE","MASTER_HOST","MASTER_USER","MASTER_PORT","CONNECT_RETRY",
"MASTER_LOG_FILE","READ_MASTER_LOG_POS","RELAY_LOG_FILE","RELAY_LOG_POS",
"RELAY_MASTER_LOG_FILE","SLAVE_IO_RUNNING","SLAVE_SQL_RUNNING","REPLICATE_DO_DB",
"REPLICATE_IGNORE_DB","REPLICATE_DO_TABLE","REPLICATE_IGNORE_TABLE",
"REPLICATE_WILD_DO_TABLE","REPLICATE_WILD_IGNORE_TABLE","LAST_ERRNO","LAST_ERROR",
"SKIP_COUNTER","EXEC_MASTER_LOG_POS","RELAY_LOG_SPACE","UNTIL_CONDITION",
"UNTIL_LOG_FILE","UNTIL_LOG_POS","MASTER_SSL_ALLOWED","MASTER_SSL_CA_FILE",
"MASTER_SSL_CA_PATH","MASTER_SSL_CERT","MASTER_SSL_CIPHER","MASTER_SSL_KEY",
"SECONDS_BEHIND_MASTER","MASTER_SSL_VERIFY_SERVER_CERT","LAST_IO_ERRNO","LAST_IO_ERROR",
"LAST_SQL_ERRNO","LAST_SQL_ERROR","REPLICATE_IGNORE_SERVER_IDS","MASTER_SERVER_ID")
,row))
return server_status, replicat_status
def get_db(self):
"""
"""
self.db.execute("""SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA""")
rows = self.db.fetchall()
database_status = list()
for row in rows:
database_status.append(
dict(zip(("SCHEMA_NAME","DEFAULT_CHARACTER_SET_NAME","DEFAULT_COLLATION_NAME"),
row)))
return database_status
def get_table(self):
"""
"""
SQL = """SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,ROW_FORMAT,CREATE_TIME,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"""
table_status = list()
self.db.execute(SQL)
rows = self.db.fetchall()
for row in rows:
table_status.append(dict(zip(("TABLE_SCHEMA","TABLE_NAME","TABLE_TYPE","ENGINE","ROW_FORMAT","CREATE_TIME","UPDATE_TIME"), row)))
return table_status
def get_column(self):
"""
"""
column_status = list()
SQL = """SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
IS_NULLABLE,DATA_TYPE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS"""
self.db.execute(SQL)
rows = self.db.fetchall()
for row in rows:
column_status.append(dict(zip(("TABLE_SCHEMA","TABLE_NAME","COLUMN_NAME","IS_NULLABLE","DATA_TYPE","CHARACTER_SET_NAME","COLLATION_NAME","COLUMN_TYPE"),
row)))
return column_status