def delete(self):
"""
Delete an object
"""
sql = "delete from " + self.TABLE_NAME
sql += " where " + self.PRIMARY_KEY + " = %(pkval)s"
pkval = self.__dict__[self.PRIMARY_KEY]
DB.perform(sql)
class BaseHandler(tornado.web.RequestHandler):
"""基本的类
"""
def initialize(self):
"""初始化数据库
"""
self.db = DB("localhost",3306,"root","123456","monitor")
SQL = "SELECT ID,IP,PORT,USER,PASSWD FROM T_INSTANCE"
self.db.execute(SQL)
self.rows = self.db.fetchall()
def truncate_slow(host, port, user, passwd):
if host and port and user and passwd:
db = DB(host=host, port=port, user=user, passwd=passwd, db="mysql")
else:
print "You mst give all parmaters as host,port,user,passwd."
exit(1)
try:
db.execute("TRUNCATE mysql.slow_log")
db.commit()
except MySQLdb.OperationalError as e:
print e
def check_slow(host, port, user, passwd):
if host and port and user and passwd:
db = DB(host=host, port=port, user=user, passwd=passwd, db="mysql")
else:
print "You mst give all parmaters as host,port,user,passwd."
exit(1)
try:
sql = "select start_time,user_host,query_time,lock_time,rows_sent,rows_examined,db,last_insert_id,insert_id,server_id,sql_text from slow_log"
db.execute(sql)
return db.fetchall()
except MySQLdb.OperationalError as e:
print e
def main():
cg = parser_config()
en = Encrypt()
monitordb = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
monitordb.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
# check_qps(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
insts = monitordb.fetchall()
for inst in insts:
# rows = check_slow('10.1.2.9',3306,'root','password')
rows = check_slow(inst[1], inst[2], inst[3], en.decrypt(inst[4]))
rtc = store_slow(rows, inst[0], monitordb)
if rtc:
truncate_slow(inst[1], inst[2], inst[3], en.decrypt(inst[4]))
def initialize(self):
"""初始化数据库
"""
self.db = DB("localhost",3306,"root","123456","monitor")
SQL = "SELECT ID,IP,PORT,USER,PASSWD FROM T_INSTANCE"
self.db.execute(SQL)
self.rows = self.db.fetchall()
def insert(self, generate_new_pkey= True):
"""
Insert a new object, but only if it hasn't been inserted yet
"""
if generate_new_pkey:
if self.PRIMARY_KEY in self.__dict__:
if self.__dict__[self.PRIMARY_KEY] != None:
raise Exception('primary key already set')
sql = "insert into " + self.TABLE_NAME
sql += "(" + self.all_fields_sql(with_table_prefix=False) + ") "
if generate_new_pkey:
self.__dict__[self.PRIMARY_KEY] = int(DB.oneval("select nextval('" + self.SEQ_NAME + "')"))
sql += "values (" + ",".join([self._sql_insert_value(f) for f in self.FIELDS]) + ")"
DB.perform(sql, extra_vars = self._prepare_sql_values())
def update(self):
"""
Update an object
"""
sql = "update " + self.TABLE_NAME
sql += " set "
field_statements = []
for field in self.FIELDS:
try:
if field == self.PRIMARY_KEY: continue
except:
continue
field_statements.append(field + " = " + self._sql_insert_value(field))
sql += ",".join(field_statements)
sql += " where " + self.PRIMARY_KEY + " = %(" + self.PRIMARY_KEY + ")s"
DB.perform(sql, extra_vars = self._prepare_sql_values())
def _select_sql_statement(cls, keys = None):
sql = "select "
sql += cls.all_fields_sql(field_substitution=True)
sql += " from " + cls.TABLE_NAME
if keys:
extra_sql = " and ".join(["%s = %s" % (key_name, DB.dbstr(key_value)) for key_name,key_value in keys.iteritems()])
if extra_sql != "":
sql += " where " + extra_sql
return sql
def select(self, keys):
if not keys or len(keys) == 0:
keys[self.PRIMARY_KEY] = self.__attr__[self.PRIMARY_KEY]
sql = self._select_sql_statement(keys)
row = DB.onerow(sql)
if row == None:
return False
self._load_from_row(row)
return True
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
def main():
# 建议读取外部配置文件以获取监控服务器配置
cg = parser_config()
en = Encrypt()
mon_db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
mon_db.execute("SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE WHERE INSTTYPE='SLAVE'")
rows = mon_db.fetchall()
for row in rows:
replicat_status = check(row[1],row[2],row[3],en.decrypt(row[4]))
if replicat_status:
mon_db.execute("""INSERT INTO T_REPLICAT(INSTANCE,IOTHREAD,SQLTHREAD,BEHIND)
VALUES('{0}','{1}','{2}',{3})""".format(row[0],replicat_status.get('SLAVE_IO_RUNNING'),
replicat_status.get('SLAVE_SQL_RUNNING'),replicat_status.get('SECONDS_BEHIND_MASTER')))
mon_db.commit()
def selectAllByKeys(cls, keys, order_by = None, offset = None, limit = None):
for key_name in keys.keys():
if key_name not in cls.FIELDS:
return None
sql = "select "
sql += cls.all_fields_sql(field_substitution=True)
sql += " from " + cls.TABLE_NAME
sql += " where "
sql_clauses = []
for k,v in keys.items():
# null or not null
if v:
sql_value = " = " + str(DB.dbstr(v))
else:
sql_value = " is NULL "
sql_clauses.append(k + sql_value)
sql += " and ".join(sql_clauses)
if order_by:
sql += " order by " + order_by
# add offset and limit
if offset:
sql += " offset " + offset
if limit:
sql += " limit " + limit
rows = DB.multirow(sql)
if rows == None:
return None
return cls.multirow_to_array(rows)
def selectAll(cls, order_by = None, offset = None, limit = None):
sql = "select "
sql += cls.all_fields_sql(field_substitution=True)
sql += " from " + cls.TABLE_NAME
if order_by != None:
sql += " order by " + order_by
if offset:
sql += " offset " + offset
if limit:
sql += " limit " + limit
rows = DB.multirow(sql)
if rows == None:
return None
return cls.multirow_to_array(rows)
def check_slowsql(host,port,user,passwd,db):
size = 1024
en = Encrypt()
db = DB(host,port,user,passwd,db)
db.execute("SELECT ID,INET_NTOA(IP),PORT,SOCKETPORT FROM T_INSTANCE ORDER BY ID DESC")
instrows = db.fetchall()
for inst in instrows:
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
s.connect((inst[1],inst[3]))
print "connected to:{0}\t{1}".format(inst[1],inst[3])
except socket.error as e:
print '\033[91m' + "Socket error:{0}\t{1} for host {2}".format(
e.errno, e.strerror, inst[1]) +\
'\033[0m'
if e.errno == 111:
continue
s.send('GET LaSt SQL')
data_collect = ""
while True:
data = s.recv(size)
if not data: break
data_collect += data
json_collect = json.loads(data_collect)
for row in json_collect:
try:
insert_row = [ i[1] for i in sorted(row.items())]
insert_row.append(inst[0])
db.executemany("""INSERT INTO
T_SLOW(ONDB,LOCKTIME,QUERYTIME,ROWS_EXAMINED,ROWS_SENT,SQL_TEXT,STARTTIME,STATE,USER_HOST,OBJID,INSTID)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
insert_row)
except MySQLdb.IntegrityError as e:
print 'row:{0} occur error:{1}'.format(tuple(insert_row[0:]),e)
state = db.commit()
if not state:
print 'UP ID:{0}'.format(insert_row[9])
tmps = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
tmps.connect((inst[1],inst[3]))
tmps.send('UP ID:{0}'.format(insert_row[9]))
data = tmps.recv(size)
tmps.close()
s.close()
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()
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
def __init__(self,host,port=3306,user="",passwd="",db="",charset="utf8"):
"""
"""
self.db = DB(host,port,user,passwd,db,charset)
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_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()