Ejemplo n.º 1
0
    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)
Ejemplo n.º 2
0
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()
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
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]))
Ejemplo n.º 6
0
 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()
Ejemplo n.º 7
0
    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())
Ejemplo n.º 8
0
    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())
Ejemplo n.º 9
0
    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
Ejemplo n.º 10
0
    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
Ejemplo n.º 11
0
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
Ejemplo n.º 12
0
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()
Ejemplo n.º 13
0
    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)    
Ejemplo n.º 14
0
    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)
Ejemplo n.º 15
0
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()
Ejemplo n.º 16
0
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()
Ejemplo n.º 17
0
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
Ejemplo n.º 18
0
 def __init__(self,host,port=3306,user="",passwd="",db="",charset="utf8"):
     """
     """
     self.db = DB(host,port,user,passwd,db,charset)
Ejemplo n.º 19
0
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()
Ejemplo n.º 20
0
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()