def delete_object(info): """Delete object from DB""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if object_id already exists, then create object if not object_id = "" for item in db.query("select * from Object where name='{0}'".format(info["hostname"])): object_id = item.id # delete object if already exists if object_id: url = """http://{0}/racktables/index.php?module=redirect&op=deleteObject&page=depot&tab=addmore&object_id={1}""".format( rt_server, object_id ) req = requests.get(url, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to delete the existing object: {0}".format(info["hostname"]) return False else: print "OK - Deleted the existing object: {0}".format(info["hostname"]) # close db db.close() return True
def get_project_keys_from_mysql(self): from torndb import Connection db = Connection( "%s:%s" % (self.config.MYSQL_HOST, self.config.MYSQL_PORT), self.config.MYSQL_DB, user=self.config.MYSQL_USER, password=self.config.MYSQL_PASS ) query = "select project_id, public_key, secret_key from sentry_projectkey" logging.info("Executing query %s in MySQL", query) project_keys = {} try: db_projects = db.query(query) if db_projects is None: return None for project in db_projects: logging.info("Updating information for project with id %s...", project.project_id) self.add_project(project_keys, project.project_id, project.public_key, project.secret_key) finally: db.close() return project_keys
def _connection(config): conn = None try: if config.DB_BACKEND == 'mysql': from torndb import Connection conn = Connection("%s:%s" % (config.DB_HOST, config.DB_PORT), config.DB_NAME, user=config.DB_USER, password=config.DB_PASS) elif config.DB_BACKEND == 'sqlite': import sqlite3 conn = sqlite3.connect(config.DB_NAME) conn.row_factory = _dict_factory elif config.DB_BACKEND == 'postgres': import psycopg2 from psycopg2.extras import DictConnection conn = psycopg2.connect( database=config.DB_NAME, user=config.DB_USER, password=config.DB_PASS, host=config.DB_HOST, port=config.DB_PORT, connection_factory=DictConnection, ) else: raise ValueError("Unknown backend %r" % config.DB_BACKEND) yield conn finally: if conn is not None: conn.close()
def get_env_iter(): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_show_env_type = config.SHOW_ENV_TYPE if v_show_env_type==1: v_more_sql = '' elif v_show_env_type==2: #id =3 表示生产环境 v_more_sql = ' and id=3' elif v_show_env_type==3: v_more_sql = ' and id!=3' else: pass str_sql = "select id,name from resources_env where 1=1 %s order by id" % (v_more_sql) env_list = db.iter(str_sql) db.close() return env_list
def update_rack(info, object_id): """Automate server audit for rack info into Racktables""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # update the rackspace if opts['rackspace']: rs_info = opts['rackspace'].split(':') colo = "".join(rs_info[0:1]) row = "".join(rs_info[1:2]) rack = "".join(rs_info[2:3]) atom = "".join(rs_info[3:4]) if not atom: print "The rackspace is not correct" return False # get rack_id for item in db.query( "select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'" .format(rack, colo, row)): rack_id = item.id if not rack_id: print "Failed to get rack_id" return False atom_list = atom.split(',') atom_data = [] for i in atom_list: if opts['rackposition']: if opts['rackposition'] in ['left', 'front']: atom_data.append("&atom_{0}_{1}_0=on".format(rack_id, i)) if opts['rackposition'] in ['right', 'back']: atom_data.append("&atom_{0}_{1}_2=on".format(rack_id, i)) if opts['rackposition'] in ['interior']: atom_data.append("&atom_{0}_{1}_1=on".format(rack_id, i)) else: atom_data.append( "&atom_{0}_{1}_0=on&atom_{0}_{1}_1=on&atom_{0}_{1}_2=on". format(rack_id, i)) atom_url = "".join(atom_data) url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=rackspace&op=updateObjectAllocation""".format( rt_server) payload = """object_id={0}&rackmulti%5B%5D={1}&comment=&got_atoms=Save{2}"""\ .format(object_id,rack_id,atom_url) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update rackspace" return False print "OK - Updated rackspace" # close db db.close() # end return True
def delete_object(info): """Delete object from DB""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if object_id already exists, then create object if not object_id = "" for item in db.query("select * from Object where name='{0}'".format( info['hostname'])): object_id = item.id # delete object if already exists if object_id: url = """http://{0}/racktables/index.php?module=redirect&op=deleteObject&page=depot&tab=addmore&object_id={1}"""\ .format(rt_server,object_id) req = requests.get(url, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to delete the existing object: {0}".format( info['hostname']) return False else: print "OK - Deleted the existing object: {0}".format( info['hostname']) # close db db.close() return True
def _connection(config): conn = None try: if config.DB_BACKEND == 'mysql': from torndb import Connection conn = Connection( "%s:%s" % (config.DB_HOST, config.DB_PORT), config.DB_NAME, user=config.DB_USER, password=config.DB_PASS ) elif config.DB_BACKEND == 'sqlite': import sqlite3 conn = sqlite3.connect(config.DB_NAME) conn.row_factory = _dict_factory elif config.DB_BACKEND == 'postgres': import psycopg2 from psycopg2.extras import DictConnection conn = psycopg2.connect( database=config.DB_NAME, user=config.DB_USER, password=config.DB_PASS, host=config.DB_HOST, port=config.DB_PORT, connection_factory=DictConnection, ) else: raise ValueError("Unknown backend %r" % config.DB_BACKEND) yield conn finally: if conn is not None: conn.close()
def update_rack(info, object_id): """Automate server audit for rack info into Racktables""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # update the rackspace if opts["rackspace"]: rs_info = opts["rackspace"].split(":") colo = "".join(rs_info[0:1]) row = "".join(rs_info[1:2]) rack = "".join(rs_info[2:3]) atom = "".join(rs_info[3:4]) if not atom: print "The rackspace is not correct" return False # get rack_id for item in db.query( "select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'".format( rack, colo, row ) ): rack_id = item.id if not rack_id: print "Failed to get rack_id" return False atom_list = atom.split(",") atom_data = [] for i in atom_list: if opts["rackposition"]: if opts["rackposition"] in ["left", "front"]: atom_data.append("&atom_{0}_{1}_0=on".format(rack_id, i)) if opts["rackposition"] in ["right", "back"]: atom_data.append("&atom_{0}_{1}_2=on".format(rack_id, i)) if opts["rackposition"] in ["interior"]: atom_data.append("&atom_{0}_{1}_1=on".format(rack_id, i)) else: atom_data.append("&atom_{0}_{1}_0=on&atom_{0}_{1}_1=on&atom_{0}_{1}_2=on".format(rack_id, i)) atom_url = "".join(atom_data) url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=rackspace&op=updateObjectAllocation""".format( rt_server ) payload = """object_id={0}&rackmulti%5B%5D={1}&comment=&got_atoms=Save{2}""".format( object_id, rack_id, atom_url ) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update rackspace" return False print "OK - Updated rackspace" # close db db.close() # end return True
def execute_query(query, **kw_dict): """Execute query """ conn = Connection(**MYSQL_SETTINGS) if kw_dict is None: try: result = conn.execute(query) conn.close() return result except Exception, e: print e
def get_query_result(query, **kw_dict): """get query result """ conn = Connection(**MYSQL_SETTINGS) if kw_dict is None: try: result = conn.query(query) conn.close() return result except Exception, e: print e
def delete_posts(sharedfile_id=0, **kwargs): """ This task will update the post table setting any post containing the shared file to deleted = 1 """ db = Connection(options.database_host, options.database_name, options.database_user, options.database_password) db.execute( """UPDATE post SET deleted=1 WHERE sharedfile_id = %s AND deleted=0""", sharedfile_id) db.close()
def update_blank_switch_security_pdu_offline(info): """Automate server autodir for PatchPanel/NetworkSwitch/NetworkSecurity/PDU into Racktables or as offline mode""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # delete object if already exists delete_object(info) # create object url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(rt_server) if opts["blank"]: payload = """0_object_type_id=9&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format( info["hostname"] ) if opts["switch"]: payload = """0_object_type_id=8&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format( info["hostname"] ) if opts["security"]: payload = """0_object_type_id=798&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format( info["hostname"] ) if opts["pdu"]: payload = """0_object_type_id=2&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format( info["hostname"] ) if opts["offline"]: payload = """0_object_type_id=4&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format( info["hostname"] ) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to create object: {0}".format(info["hostname"]) return False else: print "OK - Created object: {0}".format(info["hostname"]) # get object_id for item in db.query("select * from Object where name='{0}'".format(info["hostname"])): object_id = item.id if not object_id: print "Failed to get object_id" return False # update rack info update_rack(info, object_id) # close db db.close() # end return True
def read_db(info): """Get info from Racktables DB""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if object_id already exists object_id = "" for item in db.query("select * from Object where name='{0}'".format( info['hostname'])): object_id = item.id if not object_id: print "Object:{0} does not exist".format(info['hostname']) return False # get the location info rack_id_list = [] unit_no_list = [] for item in db.query( "select rack_id,unit_no from RackSpace where object_id=(select id from Object where name='{0}')" .format(info['hostname'])): rack_id_list.append(int(item.rack_id)) unit_no_list.append(int(item.unit_no)) if not item: print "Object:{0} does not have location info".format(info['hostname']) return False rack_id = ','.join(str(i) for i in list(set(rack_id_list))) unit_no = ','.join(str(i) for i in list(set(unit_no_list))) location_name = "" row_name = "" rack_name = "" for item in db.query( "select location_name,row_name,name from Rack where id='{0}'". format(rack_id)): location_name = item.location_name row_name = item.row_name rack_name = item.name if not location_name or not row_name or not rack_name: print "Object:{0} does not have location info".format(info['hostname']) return False print "RACKSPACE: {0}:{1}:{2}:{3}".format(location_name, row_name, rack_name, unit_no) # close db db.close() return { 'location_name': location_name, 'row_name': row_name, 'rack_name': rack_name, 'unit_no': unit_no }
def update_blank_switch_security_pdu_offline(info): """Automate server autodir for PatchPanel/NetworkSwitch/NetworkSecurity/PDU into Racktables or as offline mode""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # delete object if already exists delete_object(info) # create object url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format( rt_server) if opts['blank']: payload = """0_object_type_id=9&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\ .format(info['hostname']) if opts['switch']: payload = """0_object_type_id=8&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\ .format(info['hostname']) if opts['security']: payload = """0_object_type_id=798&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\ .format(info['hostname']) if opts['pdu']: payload = """0_object_type_id=2&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\ .format(info['hostname']) if opts['offline']: payload = """0_object_type_id=4&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\ .format(info['hostname']) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to create object: {0}".format(info['hostname']) return False else: print "OK - Created object: {0}".format(info['hostname']) # get object_id for item in db.query("select * from Object where name='{0}'".format( info['hostname'])): object_id = item.id if not object_id: print "Failed to get object_id" return False # update rack info update_rack(info, object_id) # close db db.close() # end return True
def log_dba_jobs_progress(v_job_id,v_cur_prog_desc,v_cur_cum_prog_desc,v_cur_prog_com_rate): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_add_job_progress_sql='''insert into dba_job_progress(job_id,cur_prog_desc,cur_cum_prog_desc,cur_prog_com_rate) values(%d,'%s','%s',%d)''' % ( v_job_id,v_cur_prog_desc,v_cur_cum_prog_desc,v_cur_prog_com_rate) db.execute(v_add_job_progress_sql.replace('%','%%')) db.close()
def list_object(info): """List the objects of the given rackspace""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if rackspace is correct rs_info = opts['hostname'].split(':') colo = "".join(rs_info[0:1]) row = "".join(rs_info[1:2]) rack = "".join(rs_info[2:3]) if not rack: print "The rackspace is not correct" return False # get rack_id for item in db.query( "select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'" .format(rack, colo, row)): rack_id = item.id if not rack_id: print "Failed to get rack_id" return False # get object_id object_id_list = [] for item in db.query( "select * from RackSpace where rack_id={0}".format(rack_id)): object_id_list.append(item.object_id) if len(object_id_list) == 0: print "Failed to get object_id" return False # get rid of the duplicated items then sort and read one by one for object_id in sorted(list(set(object_id_list))): for item in db.query( "select * from Object where id={0}".format(object_id)): object_name = item.name object_type_id = item.objtype_id for item in db.query( "select * from Dictionary where dict_key={0}".format( object_type_id)): object_type_name = item.dict_value print "{0}: {1}".format(object_type_name, object_name) # close db db.close() return True
def calculate_likes(sharedfile_id, **kwargs): """ This task will get all likes for a shared file, count them, then update the sharedfile.like_count """ db = Connection(options.database_host, options.database_name, options.database_user, options.database_password) result = db.get( "SELECT count(id) as like_count from favorite where sharedfile_id = %s and deleted=0", (sharedfile_id)) like_count = int(result['like_count']) db.execute("UPDATE sharedfile set like_count = %s WHERE id = %s", like_count, sharedfile_id) tweet_or_magic(db, sharedfile_id, like_count) db.close()
def final_dba_job(v_job_id): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') # 更新job队列状态为完成 v_update_job_sql = '''update dba_jobs set status=1 where job_id=%d''' % ( v_job_id) db.execute(v_update_job_sql.replace('%','%%')) db.close()
def initial_dba_job(v_op_user,v_op_comment): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_add_job_sql = '''insert into dba_jobs(op_user,job_desc) values('%s','%s')''' % ( v_op_user,v_op_comment) v_job_id=db.execute(v_add_job_sql.replace('%','%%')) db.close() return v_job_id
def upload_processlist(): # 连接配置中心库 # db = Connection('/home/apps/inception/inc.socket', # '', # '', # '', # time_zone='+8:00') db = Connection("127.0.0.1:6669", "", "", "", time_zone="+8:00") print "aa" v_sql = r"""/*--user=mysqladmin;--password=mysql;--host=172.26.137.125; --enable-check;--port=3306;*/ inception_magic_start; use test; CREATE TABLE adaptive_office23(id int); inception_magic_commit;""" # print v_sql upload_server_list = db.iter(v_sql) if upload_server_list: # 对实例表进行循环 i = 0 print upload_server_list for upload_server in upload_server_list: stage = upload_server["stage"] print stage stagestatus = upload_server["stagestatus"] print stagestatus # mysql_port = upload_server['port'] # v_host =host_ip + ':' + str(mysql_port) # i=i+1 db.close()
def read_db(info): """Get info from Racktables DB""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if object_id already exists object_id = "" for item in db.query("select * from Object where name='{0}'".format(info["hostname"])): object_id = item.id if not object_id: print "Object:{0} does not exist".format(info["hostname"]) return False # get the location info rack_id_list = [] unit_no_list = [] for item in db.query( "select rack_id,unit_no from RackSpace where object_id=(select id from Object where name='{0}')".format( info["hostname"] ) ): rack_id_list.append(int(item.rack_id)) unit_no_list.append(int(item.unit_no)) if not item: print "Object:{0} does not have location info".format(info["hostname"]) return False rack_id = ",".join(str(i) for i in list(set(rack_id_list))) unit_no = ",".join(str(i) for i in list(set(unit_no_list))) location_name = "" row_name = "" rack_name = "" for item in db.query("select location_name,row_name,name from Rack where id='{0}'".format(rack_id)): location_name = item.location_name row_name = item.row_name rack_name = item.name if not location_name or not row_name or not rack_name: print "Object:{0} does not have location info".format(info["hostname"]) return False print "RACKSPACE: {0}:{1}:{2}:{3}".format(location_name, row_name, rack_name, unit_no) # close db db.close() return {"location_name": location_name, "row_name": row_name, "rack_name": rack_name, "unit_no": unit_no}
def get_job_status(v_job_id): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_get_sql = '''SELECT cur_cum_prog_desc,cur_prog_com_rate,cur_prog_shell_cmd from dba_job_progress where id=(select max(id) from dba_job_progress where job_id=%d)''' % ( v_job_id) job_list = db.query(v_get_sql) db.close() return job_list
def list_object(info): """List the objects of the given rackspace""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # check if rackspace is correct rs_info = opts["hostname"].split(":") colo = "".join(rs_info[0:1]) row = "".join(rs_info[1:2]) rack = "".join(rs_info[2:3]) if not rack: print "The rackspace is not correct" return False # get rack_id for item in db.query( "select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'".format(rack, colo, row) ): rack_id = item.id if not rack_id: print "Failed to get rack_id" return False # get object_id object_id_list = [] for item in db.query("select * from RackSpace where rack_id={0}".format(rack_id)): object_id_list.append(item.object_id) if len(object_id_list) == 0: print "Failed to get object_id" return False # get rid of the duplicated items then sort and read one by one for object_id in sorted(list(set(object_id_list))): for item in db.query("select * from Object where id={0}".format(object_id)): object_name = item.name object_type_id = item.objtype_id for item in db.query("select * from Dictionary where dict_key={0}".format(object_type_id)): object_type_name = item.dict_value print "{0}: {1}".format(object_type_name, object_name) # close db db.close() return True
def add_posts(shake_id=0, sharedfile_id=0, sourcefile_id=0, deleted=0, created_at=None, **kwargs): """ This task will get the subscribers for a shake and insert a post for every user. If the sourcefile_id exists in posts it will set "seen" equal to 1. """ db = Connection(options.database_host, options.database_name, options.database_user, options.database_password) #get subscribers for shake_id results = db.query( """SELECT user_id as id FROM subscription WHERE shake_id = %s and deleted = 0""", shake_id) for user in results: seen = 0 #does this sourcefile exist in post for this user? existing = db.query( """SELECT id FROM post WHERE user_id = %s and sourcefile_id = %s and deleted=0 ORDER BY created_at desc LIMIT 1""", user['id'], sourcefile_id) #if so, set seen = 1 if existing: seen = 1 #if not, insert a new row if created_at: db.execute( """INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s)""", user['id'], sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) else: db.execute( """INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, NOW())""", user['id'], sourcefile_id, sharedfile_id, seen, deleted, shake_id) #now insert a post for the user who originally posted it. sharedfile = db.get("""SELECT user_id from sharedfile WHERE id = %s""", sharedfile_id) db.execute( """INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, NOW())""", sharedfile['user_id'], sourcefile_id, sharedfile_id, 1, 0, shake_id) db.close()
def get_domain_list_from_env(v_role_id,belong_env): db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') str_sql = '''select b.id,b.name from resources_role_app a,resources_app b where a.app_id = b.id and a.role_id = %d and b.app_type= %d ''' % (v_role_id,belong_env) app_list = db.query(str_sql) db.close() return app_list
def remote_start_mysql_server(v_host,v_os_user,v_os_password, v_db_port): # v_mysql_version 1: mysql5 4:mariadb5 5:mariadb10 # db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') # 获得Mysql server 版本: mysql5,mariadb5,mariadb10 v_get_sql = r'''SELECT case mysql_version when 1 then 'mysql5' when 4 then 'mariadb5' when 5 then 'mariadb10' when 6 then 'percona5.6' when 7 then 'pxc5.6' end mysql_version from tag where ip='%s' and port=%d ''' % (v_host,int(v_db_port)) v_list = db.get(v_get_sql) v_mysql_version = v_list['mysql_version'] db.close() #v_db_socket='--socket=/tmp/mysql'+str(v_db_port)+'.sock' #nohup 防止paramiko 进程退出后,中断执行 # 必须要加 1>/dev/null 2>&1,否则命令不执行。可能是paramiko 模式下,不加的话,执行命令时日志无法输出 v_exe_cmd = r'''nohup /apps/svr/%s/bin/mysqld_safe --defaults-file=%s/%s_%d.cnf 1>/dev/null 2>&1 &''' % ( v_mysql_version,config.mysql_conf_path,v_mysql_version,v_db_port) print v_exe_cmd # 远程paramiko调用 在本机执行sql result = remote_shell_cmd_no_result(v_host,v_os_user,v_os_password,v_exe_cmd) time.sleep(20) # 等待20秒 mysql 完全启动,更好的方法是,做个循环判断mysql 完全起来了,再退出 return result #返回空串表示成功
def calculate_saves(sharedfile_id, **kwargs): """ Take the id of a sharedfile that just got saved by another user. If this file is an original, meaning it has no original_id set, we can safely calculate the save count by how many sharedfiles have the file's id as their original_id. However if the file has an original, we also need to recalculate the original's count, as well as this file's save count. """ db = Connection(options.database_host, options.database_name, options.database_user, options.database_password) sharedfile = db.get("select original_id from sharedfile where id = %s", sharedfile_id) original_id = sharedfile['original_id'] # If this file is original, calculate it's save count by all sharedfile where this file is the original_id. if original_id == 0: original_saves = db.get( "SELECT count(id) AS count FROM sharedfile where original_id = %s and deleted = 0", sharedfile_id) db.execute("UPDATE sharedfile set save_count = %s WHERE id = %s", original_saves['count'], sharedfile_id) # Otherwise, we need to update the original's save count and this file's save count. else: original_saves = db.get( "SELECT count(id) AS count FROM sharedfile where original_id = %s and deleted = 0", original_id) db.execute("UPDATE sharedfile set save_count = %s WHERE id = %s", original_saves['count'], original_id) # Calc this files new save count, only based on parent since its not original. parent_saves = db.get( "SELECT count(id) AS count FROM sharedfile where parent_id = %s and deleted = 0", sharedfile_id) db.execute("UPDATE sharedfile set save_count = %s WHERE id = %s", parent_saves['count'], sharedfile_id) db.close()
def remote_off_get_datadir_path(v_host,v_db_port,v_type): # v_mysql_version 1: mysql5 4:mariadb5 5:mariadb10 # db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') # 获得Mysql server 版本: mysql5,mariadb5,mariadb10 v_get_sql = r'''SELECT case mysql_version when 1 then 'mysql5' when 4 then 'mariadb5' when 5 then 'mariadb10' when 6 then 'percona5.6' when 7 then 'pxc5.6' end mysql_version from tag where ip='%s' and port=%d ''' % (v_host,int(v_db_port)) v_list = db.get(v_get_sql) v_mysql_version = v_list['mysql_version'] db.close() # /apps/dbdat/mariadb10_data3306 # if v_type==1: v_datadir_path = '%s/%s_data%s' % (config.mysql_datadir_path,v_mysql_version,v_db_port) else: v_datadir_path = '%s_data%s' % (v_mysql_version,v_db_port) print v_datadir_path return v_datadir_path
def update(self): config = self.application.config db = Connection( "%s:%s" % (config.MYSQL_HOST, config.MYSQL_PORT), config.MYSQL_DB, user=config.MYSQL_USER, password=config.MYSQL_PASS ) query = "select project_id, public_key, secret_key from sentry_projectkey" logging.info("Executing query %s in MySQL" % query) projects = {} try: db_projects = db.query(query) if db_projects is None: logging.warn("Could not retrieve nformation from sentry's database because MySQL Server was unavailable") return for project in db_projects: logging.info("Updating information for project with id %s..." % project.project_id) if not project.project_id in projects.keys(): projects[project.project_id] = { "public_key": [], "secret_key": [] } projects[project.project_id]['public_key'].append(project.public_key) projects[project.project_id]['secret_key'].append(project.secret_key) self.application.project_keys = projects finally: db.close()
class MySQLHelper: def __init__(self, host, user, password, database): self._db = Connection(host, database, user=user, password=password, max_idle_time=10) if not self._db._db: raise Exception('%s' % self._db.error_msg) def query(self, sql, *parameters): return self._db.query(sql, *parameters) def query_one(self, sql, *parameters): res = self._db.query(sql, *parameters) return res.pop() if res else {} def write(self, sql, *parameters): return self._db.execute(sql, *parameters) def gen_insert(self, tablename, rowdict, replace=False): return self._db.gen_insert_sql(tablename, rowdict, replace) def insert_dict(self, tablename, rowdict): key_strs = ", ".join(["""`%s`""" % key for key in rowdict.keys()]) value_strs = ", ".join(["""'%s'""" % rowdict.get(key) for key in rowdict.keys()]) sql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, key_strs, value_strs) return self._db.execute(sql) def insert_batch(self, tablename, batch_params): value_batch = [] for param in batch_params: keys = param.keys() key_strs = ", ".join(["""`%s`""" % key for key in keys]) value_strs = "(%s)" % ", ".join( ["""'%s'""" % "%s" % param.get(key) for key in keys]) value_batch.append(value_strs) sql = """INSERT INTO %s (%s) VALUES %s""" % (tablename, key_strs, ",".join(value_batch)) return self._db.execute(sql) def update_dict(self, tablename, rowdict, where): sql = """UPDATE %s SET %s WHERE %s""" % ( tablename, self._formatter(rowdict, ', '), self._formatter(where, " AND ")) return self._db.execute(sql) def transaction(self, query, parameters): return self._db.transaction(query, parameters) def get(self, tablename, conds, cols='', extra_conds={}): if not tablename: return False cols = "%s" % ','.join(cols) if cols else '*' wheres = [] values = [] if conds and isinstance(conds, dict): for key, value in conds.items(): if isinstance(value, (list, tuple)): wheres.append("`%s` IN (%s)" % (key, "'%s'" % "','".join([str(v) for v in value]))) else: wheres.append("`%s`=%%s" % key) values.append("%s" % value) where_str = ' AND '.join(wheres) sql = """ SELECT %s FROM `%s` """ % (cols, tablename) if where_str: sql += """ WHERE %s """ % (where_str) if extra_conds.get('group_by'): sql += """ GROUP by %s """ % ','.join(extra_conds['group_by']) if extra_conds.get('order_by'): sql += """ ORDER by %s """ % ','.join(extra_conds['order_by']) if extra_conds.get('limit'): sql += """ LIMIT %s """ % ','.join(map(str, extra_conds['limit'])) return self._db.query(sql, *values) def _serialize(self, value): if isinstance(value, (dict, list, set)): value = json.dumps(value) else: value = "%s" % value return value def _formatter(self, pairs, delimiter): values = [] for key, value in pairs.items(): if not isinstance(value, list): value = self._serialize(value) values.append("""`%s`='%s'""" % (key, value)) else: values.append("""`%s` in ("%s")""" % (key, '","'.join([self._serialize(val) for val in value]))) return delimiter.join(values) def __del__(self): self._db.close()
def add_single_backup_job(v_setup_id): # mysql_ins_bak_setup 表的当前更新或插入的备份实例id os_user = config.OS_USER os_password = config.OS_APPS_PASSWD scheduler = BackgroundScheduler() # 默认内存的jobstore url = "sqlite:////home/apps/dbajob.sqlite" scheduler.add_jobstore("sqlalchemy", url=url, alias="sqlite_js") scheduler.print_jobs() print "a" # 连接配置中心库,获取数据库备份周期等信息 db = Connection("/tmp/mysql3306.sock", config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone="+8:00") v_sql = r"""SELECT a.instance_id,b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where a.instance_id=b.id and a.id=%d""" % ( v_setup_id ) print v_sql bak_server = db.get(v_sql) instance_id = bak_server["instance_id"] from_host = bak_server["ip"] # print from_host mysql_port = bak_server["port"] backup_interval_type = bak_server["backup_interval_type"] backup_start_time = bak_server["backup_start_time"] str_start_date = time.strftime("%Y-%m-%d") + " " + backup_start_time print str_start_date v_job_id = "backup_%s_%s" % (from_host, str(mysql_port)) if backup_interval_type == 1: # every day # scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], jobstore='file') scheduler.add_job( backup, "interval", id=v_job_id, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) elif backup_interval_type == 2: # every week weeks=1 scheduler.add_job( backup, "interval", id=v_job_id, weeks=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) elif backup_interval_type == 3: # every hour hours=1 scheduler.add_job( backup, "interval", id=v_job_id, hours=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) else: pass scheduler.print_jobs() print "b" scheduler.start() scheduler.print_jobs() print "c" # 开始在数据库记录备份的调度任务状态 0:调度任务已启动,实际备份还没有开始 v_sche_start_sql = """insert into mysql_ins_bak_log(instance_id,backup_result_type) values(%d,0)""" % ( instance_id ) db.execute(v_sche_start_sql) db.close()
def add_schedule_backup_job(): # if __name__ == '__main__': os_user = config.OS_USER os_password = config.OS_APPS_PASSWD scheduler = BackgroundScheduler() # 默认内存的jobstore url = "sqlite:////home/apps/dbajob.sqlite" scheduler.add_jobstore("sqlalchemy", url=url, alias="sqlite_js") scheduler.print_jobs() print "a" scheduler.remove_all_jobs(jobstore="sqlite_js") scheduler.print_jobs() print "remove" # v_current_jobs = scheduler.get_jobs() # print v_current_jobs # if v_current_jobs: # 如果job存在的话,先请客 # scheduler.remove_job('backup') # 连接配置中心库,获取数据库备份周期等信息 db = Connection("/tmp/mysql3306.sock", config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone="+8:00") v_sql = r"""SELECT a.instance_id,b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where a.instance_id=b.id """ print v_sql bak_server_list = db.query(v_sql) if bak_server_list: # 有server需要配置 i = 0 # 把还没有开始的调度任务,置为手工结束 backup_result_type=4 v_manual_end_sql = "update mysql_ins_bak_log set backup_result_type=4 where backup_result_type=0" db.execute(v_manual_end_sql) for bak_server in bak_server_list: instance_id = bak_server["instance_id"] from_host = bak_server["ip"] # print from_host mysql_port = bak_server["port"] backup_interval_type = bak_server["backup_interval_type"] backup_start_time = bak_server["backup_start_time"] str_start_date = time.strftime("%Y-%m-%d") + " " + backup_start_time print str_start_date v_job_id = "backup_%s_%s" % (from_host, str(mysql_port)) if backup_interval_type == 1: # every day # scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], jobstore='file') scheduler.add_job( backup, "interval", id=v_job_id, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) elif backup_interval_type == 2: # every week weeks=1 scheduler.add_job( backup, "interval", id=v_job_id, weeks=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) elif backup_interval_type == 3: # every hour hours=1 scheduler.add_job( backup, "interval", id=v_job_id, hours=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], replace_existing=True, jobstore="sqlite_js", ) else: pass # 开始在数据库记录备份的调度任务状态 0:调度任务已启动,实际备份还没有开始 v_sche_start_sql = """insert into mysql_ins_bak_log(instance_id,backup_result_type) values(%d,0)""" % ( instance_id ) db.execute(v_sche_start_sql) i = i + 1 scheduler.print_jobs() print "b" scheduler.start() scheduler.print_jobs() print "c" db.close()
def generate_mysql_config(tlp_file, schema_file, force=False): """ Args: tlp_file(unicode): config template to be written in to config center db schema_file(unicode): scheme file """ if not os.path.exists(tlp_file): raise ImproperlyConfigured(u"invalid config template file `%s`" % tlp_file) if not os.path.exists(schema_file): raise ImproperlyConfigured(u"invalid config scheme file path `%s`" % schema_file) with open(tlp_file) as fi: config = json.load(fi) with open(schema_file) as fi: schema = json.load(fi) Draft4Validator(schema).validate(config) config_center = settings.CONFIG_CENTER env = settings.ENV logging.warn("init config center with env `%s`" % env) from torndb import Connection db = None try: db_name = config_center["database"] db = Connection(host=config_center["host"], database=db_name, user=config_center["user"], password=config_center["password"]) table_name = "t_s_config_%s" % env if force: db.execute("DROP TABLE if EXISTS %s" % table_name) if db.query("show tables where Tables_in_%s = '%s'" % (db_name, table_name)): raise RuntimeError( u"config center of env `%s` has already been initialized !") create_table_sql = u""" CREATE TABLE IF NOT EXISTS %s ( `id` int(20) unsigned zerofill NOT NULL AUTO_INCREMENT, `node` varchar(40) COLLATE utf8_bin NOT NULL COMMENT '模块节点名', `key` varchar(40) COLLATE utf8_bin NOT NULL COMMENT '模块下键名', `value` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '模块键值', `key_type` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT 'string' COMMENT '配置键对应的类型', `key_note` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT '键名备注说明', PRIMARY KEY (`id`), UNIQUE KEY `unique_config` (`node`,`key`) USING BTREE COMMENT '配置唯一索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='%s环境 系统运行配置表'"""\ % (table_name, env) db.execute(create_table_sql) sql = "INSERT INTO %s" % table_name sql += "(`node`, `key`, `value`, `key_type`) VALUES (%s, %s, %s, %s)" datas = [] for key, value in config.items(): for k, v in value.items(): key_type = "string" if isinstance(v, list): v = ", ".join(v) elif isinstance(v, bool): key_type = "bool" elif isinstance(v, int): key_type = "int" data = (key, k, v, key_type) datas.append(data) db.executemany_rowcount(sql, datas) except KeyError as e: raise ImproperlyConfigured(u"need config key `%s` of config_center" % (e.args[0])) finally: if db: db.close()
print from_host, mysql_port, os_user, os_password #scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) scheduler.add_interval_job(backup, days=1, start_date='2014-07-18 18:17:01', args=[from_host, mysql_port, os_user, os_password]) elif backup_interval_type == 2: # every week scheduler.add_interval_job(backup, weeks=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) elif backup_interval_type == 3: # every hour scheduler.add_interval_job(backup, hours=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) i=i+1 db.close() #scheduler.add_interval_job(func.remote_mysql_backup, seconds=5, args=[from_host, port, os_user, os_password]) #scheduler.add_interval_job(log_w, seconds=5, start_date='2014-07-18 16:21', args=['abc']) if bak_server_list: # 有server需要配置 print('Press Ctrl+C to exit') try: scheduler.start() except (KeyboardInterrupt, SystemExit): pass
def migrate_for_user(user_id=0, **kwargs): """ This tasks handles copying MLKSHK user data over to the MLTSHP tables. """ db = Connection(options.database_host, options.database_name, options.database_user, options.database_password) state = db.get("SELECT is_migrated FROM migration_state WHERE user_id=%s", user_id) if not state or state["is_migrated"] == 1: logger.info("User %s already migrated" % str(user_id)) db.close() return logger.info("Migrating user_id %s..." % str(user_id)) logger.info("- app records") db.execute( """INSERT IGNORE INTO app (id, user_id, title, description, secret, redirect_url, deleted, created_at, updated_at) SELECT ma.id, ma.user_id, ma.title, ma.description, ma.secret, ma.redirect_url, 0, ma.created_at, ma.updated_at FROM mlkshk_app ma WHERE ma.user_id=%s AND ma.deleted=0""", user_id) # comments logger.info("- comment records") db.execute( """INSERT IGNORE INTO comment (id, user_id, sharedfile_id, body, deleted, created_at, updated_at) SELECT mc.id, mc.user_id, mc.sharedfile_id, mc.body, mc.deleted, mc.created_at, mc.updated_at FROM mlkshk_comment mc WHERE mc.user_id=%s AND mc.deleted=0""", user_id) # conversation logger.info("- conversation records") db.execute( """INSERT IGNORE INTO conversation (id, user_id, sharedfile_id, muted, created_at, updated_at) SELECT mc.id, mc.user_id, mc.sharedfile_id, mc.muted, mc.created_at, mc.updated_at FROM mlkshk_conversation mc WHERE mc.user_id=%s""", user_id) # favorites logger.info("- favorite records") db.execute( """INSERT IGNORE INTO favorite (id, user_id, sharedfile_id, deleted, created_at, updated_at) SELECT mf.id, mf.user_id, mf.sharedfile_id, mf.deleted, mf.created_at, mf.updated_at FROM mlkshk_favorite mf WHERE mf.user_id=%s AND mf.deleted=0""", user_id) # invitation_request logger.info("- invitation_request records") db.execute( """INSERT IGNORE INTO invitation_request (id, user_id, manager_id, shake_id, deleted, created_at, updated_at) SELECT mi.id, mi.user_id, mi.manager_id, mi.shake_id, mi.deleted, mi.created_at, mi.updated_at FROM mlkshk_invitation_request mi WHERE mi.user_id=%s AND mi.deleted=0""", user_id) # notification logger.info("- notification records") db.execute( """INSERT IGNORE INTO notification (id, sender_id, receiver_id, action_id, type, deleted, created_at) SELECT mn.id, mn.sender_id, mn.receiver_id, mn.action_id, mn.type, mn.deleted, mn.created_at FROM mlkshk_notification mn WHERE mn.receiver_id=%s AND mn.deleted=0""", user_id) # shake logger.info("- shake records") db.execute("""UPDATE shake SET deleted=0 WHERE user_id=%s""", user_id) # shake_manager logger.info("- shake_manager records") db.execute( """INSERT IGNORE INTO shake_manager (id, shake_id, user_id, deleted, created_at, updated_at) SELECT ms.id, ms.shake_id, ms.user_id, ms.deleted, ms.created_at, ms.updated_at FROM mlkshk_shake_manager ms WHERE ms.user_id=%s AND ms.deleted=0""", user_id) # shakesharedfile logger.info("- shakesharedfile records") db.execute( """INSERT IGNORE INTO shakesharedfile (id, shake_id, sharedfile_id, deleted, created_at) SELECT ms.id, ms.shake_id, ms.sharedfile_id, ms.deleted, ms.created_at FROM mlkshk_shakesharedfile ms WHERE ms.shake_id IN (SELECT DISTINCT id FROM shake WHERE deleted=0 AND user_id=%s) AND ms.deleted=0""", user_id) # sharedfile logger.info("- sharedfile records") db.execute( """INSERT IGNORE INTO sharedfile (id, source_id, user_id, name, title, source_url, description, share_key, content_type, size, activity_at, parent_id, original_id, deleted, like_count, save_count, view_count, updated_at, created_at) SELECT ms.id, ms.source_id, ms.user_id, ms.name, ms.title, ms.source_url, ms.description, ms.share_key, ms.content_type, ms.size, ms.activity_at, ms.parent_id, ms.original_id, ms.deleted, ms.like_count, ms.save_count, ms.view_count, ms.updated_at, ms.created_at FROM mlkshk_sharedfile ms WHERE ms.user_id=%s AND ms.deleted=0""", user_id) # nsfw_log logger.info("- nsfw_log records") db.execute( """INSERT IGNORE INTO nsfw_log (id, user_id, sharedfile_id, sourcefile_id, created_at) SELECT mn.id, mn.user_id, mn.sharedfile_id, mn.sourcefile_id, mn.created_at FROM mlkshk_nsfw_log mn WHERE mn.user_id=%s""", user_id) # magicfile logger.info("- magicfile records") db.execute( """INSERT IGNORE INTO magicfile (id, sharedfile_id, created_at) SELECT mm.id, mm.sharedfile_id, mm.created_at FROM mlkshk_magicfile mm WHERE mm.sharedfile_id IN (SELECT id FROM sharedfile WHERE user_id=%s)""", user_id) # subscription logger.info("- subscription records") db.execute( """INSERT IGNORE INTO subscription (id, user_id, shake_id, deleted, created_at, updated_at) SELECT ms.id, ms.user_id, ms.shake_id, ms.deleted, ms.created_at, ms.updated_at FROM mlkshk_subscription ms WHERE ms.user_id=%s AND ms.deleted=0""", user_id) # tagged_file logger.info("- tagged_file records") db.execute( """INSERT IGNORE INTO tagged_file (id, tag_id, sharedfile_id, deleted, created_at) SELECT mt.id, mt.tag_id, mt.sharedfile_id, mt.deleted, mt.created_at FROM mlkshk_tagged_file mt WHERE mt.sharedfile_id IN (SELECT DISTINCT id FROM sharedfile WHERE user_id=%s) AND mt.deleted=0""", user_id) # special handling for post table migration since that thing is so large (300mm rows) logger.info("- post records") db.execute( """INSERT IGNORE INTO post (id, user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) SELECT mp.id, mp.user_id, mp.sourcefile_id, mp.sharedfile_id, mp.seen, mp.deleted, mp.shake_id, mp.created_at FROM mlkshk_post mp WHERE mp.user_id=%s""", user_id) # now delete any imported soft-deleted rows db.execute("""DELETE FROM post WHERE user_id=%s AND deleted=1""", user_id) # this should already be done by the web app, but we may running this # via a script logger.info("- user/migration_state update") db.execute("""UPDATE user SET deleted=0 WHERE deleted=2 and id=%s""", user_id) db.execute("""UPDATE migration_state SET is_migrated=1 WHERE user_id=%s""", user_id) logger.info("- Migration for user %s complete!" % str(user_id)) user = db.get("SELECT name, email FROM user WHERE id=%s", user_id) db.close() if options.postmark_api_key and not options.debug_workers: pm = postmark.PMMail( api_key=options.postmark_api_key, sender="*****@*****.**", to=user["email"], subject="MLTSHP restore has finished!", text_body= """We'll keep this short and sweet. Your data should be available now!\n\n""" + ("""Head over to: https://mltshp.com/user/%s and check it out!""" % user["name"])) pm.send()
def update_db(info): """Automate server audit into Racktables""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # get object_type_id for item in db.query("select * from Dictionary where dict_value='{0}'".format(info["server_type"])): object_type_id = item.dict_key # delete object if already exists delete_object(info) # create object url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(rt_server) if info["server_type"] in ["Server", "XenServer"]: payload = """0_object_type_id={0}&0_object_name={1}&0_object_label=&0_object_asset_no={1}&got_fast_data=Go%21""".format( object_type_id, info["hostname"] ) if info["server_type"] in ["VM", "EC2"]: payload = """virtual_objects=&0_object_type_id={0}&0_object_name={1}&got_fast_data=Go%21""".format( object_type_id, info["hostname"] ) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to create object: {0}".format(info["hostname"]) return False else: print "OK - Created object: {0}".format(info["hostname"]) object_id = "" # get object_id for item in db.query("select * from Object where name='{0}'".format(info["hostname"])): object_id = item.id if not object_id: print "Failed to get object_id" return False # get os_release_id os_release_key = "" for item in db.query("select * from Dictionary where dict_value='{0}'".format(info["os_release"])): os_release_key = item.dict_key if not os_release_key: print "Failed to get object_type_id, please add '{0}' to 'Configuration - Dictionary - Server OS type'.".format( info["os_release"] ) return False # update the informations of object, all post data formats were got by firebug on firefox url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=edit&op=update""".format(rt_server) if info["server_type"] == "Server": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=2&4_value=0&5_attr_id=26&5_value=0&6_attr_id=10006&6_value={6}\ &7_attr_id=10003&7_value={7}&8_attr_id=1&8_value=&9_attr_id=28&9_value=&10_attr_id=21&10_value=&11_attr_id=4&11_value={8}\ &12_attr_id=24&12_value=&13_attr_id=10005&13_value={9}&14_attr_id=25&14_value=&num_attrs=15&object_comment=&submit.x=15&submit.y=13""".format( object_id, object_type_id, info["hostname"], info["cpu_cores"], quote_plus(info["disk"]), info["fqdn"], info["memory"], quote_plus(info["network"]), os_release_key, info["swap"], ) if info["server_type"] == "XenServer": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=1&7_value=&8_attr_id=28&8_value=&9_attr_id=4&9_value={8}&10_attr_id=24&10_value=&11_attr_id=10005&11_value={9}\ &12_attr_id=25&12_value=&13_attr_id=10008&13_value={10}&num_attrs=14&object_comment=&submit.x=18&submit.y=21""".format( object_id, object_type_id, info["hostname"], info["cpu_cores"], quote_plus(info["disk"]), info["fqdn"], info["memory"], quote_plus(info["network"]), os_release_key, info["swap"], quote_plus(info["vm_list"]), ) if info["server_type"] == "EC2": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=10010&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=19&submit.y=27""".format( object_id, object_type_id, info["hostname"], info["cpu_cores"], quote_plus(info["disk"]), info["fqdn"], info["memory"], quote_plus(info["network"]), info["ec2_pubname"], os_release_key, info["swap"], ) if info["server_type"] == "VM": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=10007&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=25&submit.y=14'""".format( object_id, object_type_id, info["hostname"], info["cpu_cores"], quote_plus(info["disk"]), info["fqdn"], info["memory"], quote_plus(info["network"]), info["resident_on"], os_release_key, info["swap"], ) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update attributes" return False print "OK - Updated attributes" # ec2 servers don't need to update the ip pool if info["server_type"] not in ["EC2"]: # update the ip pool nics = ("".join(info["network"].split())).split(",") for i in nics: nic_info = i.split(":") nic_name = "".join(nic_info[0:1]) nic_addr = "".join(nic_info[1:2]) # check if nic_name is not correct if nic_name.isalnum(): # create nic url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=ip&op=add""".format(rt_server) payload = """object_id={0}&bond_name={1}&ip={2}&bond_type=regular&submit.x=11&submit.y=6""".format( object_id, nic_name, nic_addr ) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update ip pool for {0}:{1}".format(nic_name, nic_addr) return False print "OK - Updated ip pool for {0}:{1}".format(nic_name, nic_addr) # virtual servers don't need to update the rackspace if info["server_type"] not in ["EC2", "VM"]: # update rack info update_rack(info, object_id) # close db db.close() # end return True
def run(self): # init our varying wrappers self.config = self.config_parser rabbit_queue = self.conf('rabbitqueue') rabbit_requeue = self.conf('rabbitrequeue') rabbitmq_url = self.conf('rabbitmq_url') client = puka.Client(rabbitmq_url) promise = client.connect() client.wait(promise) promise = client.queue_declare(queue=rabbit_queue, durable=True) client.wait(promise) if rabbit_requeue: promise = client.queue_declare(queue=rabbit_requeue, durable=True) client.wait(promise) consume_promise = client.basic_consume(queue=rabbit_queue, prefetch_count=1) host = self.conf('mysql_host') user = self.conf('mysql_user') password = self.conf('mysql_password') database = self.conf('mysql_database') table = self.conf('mysql_table') mysql_query_fields = self.conf('mysql_query_fields').split(",") mysql_key_field = self.conf('mysql_key_field') mysql_field_length = self.conf('mysql_field_length') mysql_insert_query = self.conf('mysql_insert_query') mysqldb = Connection(host, database, user=user, password=password) while True: try: result = client.wait(consume_promise) payload = json.loads(result['body']) key = payload[mysql_key_field] # Do some processing to get all they key/val pairs for k, v in payload['body'].iteritems(): values = [] for field in mysql_query_fields: if isinstance(payload[field], basestring): values.append(payload[field].strip()) else: values.append(payload[field]) if k == 'body': values.append(key) else: values.append(k) values.append(v) logger.debug(k) if len(v) > 0 and len(v) < int(mysql_field_length): try: mysqldb.executemany(mysql_insert_query, [values]) except Exception as e: logger.error(e) client.basic_ack(result) if rabbit_requeue: promise = client.basic_publish(exchange='', routing_key=rabbit_requeue, body=result['body']) client.wait(promise) except KeyboardInterrupt as e: logger.error(e) promise = client.close() client.wait(promise) mysqldb.close() raise
def start_schedule(): #if __name__ == '__main__': os_user = config.OS_USER os_password = config.OS_APPS_PASSWD scheduler = Scheduler(daemonic = False) scheduler.print_jobs() #scheduler.remove_jobstore('file',close=True) #scheduler.shutdown(wait=False) scheduler.shutdown() #scheduler.unschedule_func(backup) scheduler.add_jobstore(ShelveJobStore('/tmp/db_schedule'), 'file') v_current_jobs = scheduler.get_jobs() print v_current_jobs if v_current_jobs: # 如果job存在的话,先请客 scheduler.unschedule_func(backup) #scheduler = Scheduler(standalone=True) #scheduler = Scheduler(daemon=True) #连接配置中心库,获取数据库备份周期等信息 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_sql = r"""SELECT a.instance_id,b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where a.instance_id=b.id """ print v_sql bak_server_list = db.query(v_sql) if bak_server_list: # 有server需要配置 i=0 # 把还没有开始的调度任务,置为手工结束 backup_result_type=4 v_manual_end_sql = 'update mysql_ins_bak_log set backup_result_type=4 where backup_result_type=0' db.execute(v_manual_end_sql) for bak_server in bak_server_list: instance_id = bak_server['instance_id'] from_host = bak_server['ip'] #print from_host mysql_port = bak_server['port'] backup_interval_type = bak_server['backup_interval_type'] backup_start_time = bak_server['backup_start_time'] str_start_date= time.strftime("%Y-%m-%d") + ' ' + backup_start_time print str_start_date if backup_interval_type == 1: # every day #内存jobstore #scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) #文件jobstore jobstore='file' scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], jobstore='file') #scheduler.add_interval_job(backup, days=1, start_date='2014-07-18 18:17:01', args=[from_host, mysql_port, os_user, os_password]) elif backup_interval_type == 2: # every week scheduler.add_interval_job(backup, weeks=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) elif backup_interval_type == 3: # every hour scheduler.add_interval_job(backup, hours=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password]) # 开始在数据库记录备份的调度任务状态 0:调度任务已启动,实际备份还没有开始 v_sche_start_sql = """insert into mysql_ins_bak_log(instance_id,backup_result_type) values(%d,0)""" % (instance_id) db.execute(v_sche_start_sql) i=i+1 db.close() if bak_server_list: # 有server需要配置 scheduler.start() print 'success!' scheduler.print_jobs() '''
def update_db(info): """Automate server audit into Racktables""" # connect to racktables db db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass) # get object_type_id for item in db.query( "select * from Dictionary where dict_value='{0}'".format( info['server_type'])): object_type_id = item.dict_key # delete object if already exists delete_object(info) # create object url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format( rt_server) if info['server_type'] in ["Server", "XenServer"]: payload = """0_object_type_id={0}&0_object_name={1}&0_object_label=&0_object_asset_no={1}&got_fast_data=Go%21"""\ .format(object_type_id,info['hostname']) if info['server_type'] in ["VM", "EC2"]: payload = """virtual_objects=&0_object_type_id={0}&0_object_name={1}&got_fast_data=Go%21"""\ .format(object_type_id,info['hostname']) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to create object: {0}".format(info['hostname']) return False else: print "OK - Created object: {0}".format(info['hostname']) object_id = "" # get object_id for item in db.query("select * from Object where name='{0}'".format( info['hostname'])): object_id = item.id if not object_id: print "Failed to get object_id" return False # get os_release_id os_release_key = "" for item in db.query( "select * from Dictionary where dict_value='{0}'".format( info['os_release'])): os_release_key = item.dict_key if not os_release_key: print "Failed to get object_type_id, please add '{0}' to 'Configuration - Dictionary - Server OS type'.".format( info['os_release']) return False # update the informations of object, all post data formats were got by firebug on firefox url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=edit&op=update""".format( rt_server) if info['server_type'] == "Server": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=2&4_value=0&5_attr_id=26&5_value=0&6_attr_id=10006&6_value={6}\ &7_attr_id=10003&7_value={7}&8_attr_id=1&8_value=&9_attr_id=28&9_value=&10_attr_id=21&10_value=&11_attr_id=4&11_value={8}\ &12_attr_id=24&12_value=&13_attr_id=10005&13_value={9}&14_attr_id=25&14_value=&num_attrs=15&object_comment=&submit.x=15&submit.y=13"""\ .format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'], info['memory'],quote_plus(info['network']),os_release_key,info['swap']) if info['server_type'] == "XenServer": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=1&7_value=&8_attr_id=28&8_value=&9_attr_id=4&9_value={8}&10_attr_id=24&10_value=&11_attr_id=10005&11_value={9}\ &12_attr_id=25&12_value=&13_attr_id=10008&13_value={10}&num_attrs=14&object_comment=&submit.x=18&submit.y=21"""\ .format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'], info['memory'],quote_plus(info['network']),os_release_key,info['swap'],quote_plus(info['vm_list'])) if info['server_type'] == "EC2": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=10010&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=19&submit.y=27"""\ .format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'], info['memory'],quote_plus(info['network']),info['ec2_pubname'],os_release_key,info['swap']) if info['server_type'] == "VM": payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\ &2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\ &7_attr_id=10007&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=25&submit.y=14'"""\ .format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'], info['memory'],quote_plus(info['network']),info['resident_on'],os_release_key,info['swap']) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update attributes" return False print "OK - Updated attributes" # ec2 servers don't need to update the ip pool if info['server_type'] not in ["EC2"]: # update the ip pool nics = ("".join(info['network'].split())).split(',') for i in nics: nic_info = i.split(':') nic_name = "".join(nic_info[0:1]) nic_addr = "".join(nic_info[1:2]) # check if nic_name is not correct if nic_name.isalnum(): # create nic url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=ip&op=add""".format( rt_server) payload = """object_id={0}&bond_name={1}&ip={2}&bond_type=regular&submit.x=11&submit.y=6"""\ .format(object_id,nic_name,nic_addr) req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth) if req.status_code != requests.codes.ok: print "Failed to update ip pool for {0}:{1}".format( nic_name, nic_addr) return False print "OK - Updated ip pool for {0}:{1}".format( nic_name, nic_addr) # virtual servers don't need to update the rackspace if info['server_type'] not in ["EC2", "VM"]: # update rack info update_rack(info, object_id) # close db db.close() # end return True
def upload_processlist(): #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1""" #print v_sql upload_server_list = db.query(v_sql) if upload_server_list: # 对实例表进行循环 i=0 for upload_server in upload_server_list: instance_id = upload_server['id'] host_ip = upload_server['ip'] mysql_port = upload_server['port'] v_host =host_ip + ':' + str(mysql_port) #连接远程实例 db_remote = Connection(v_host, 'information_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') v_pl = r"""SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED from PROCESSLIST""" #print v_pl process_list = db_remote.query(v_pl) for process_row in process_list: vp_id = process_row['ID'] vp_user = process_row['USER'] vp_host = process_row['HOST'] vp_db = process_row['DB'] # 若是空,变成mysql的null,否则加上引号再传递进去,格式为%s ,而不是'%s' if vp_db is None: vp_db = 'NULL' else: vp_db = "'"+vp_db+"'" #print vp_db vp_command = process_row['COMMAND'] vp_time = process_row['TIME'] vp_state = process_row['STATE'] vp_info = process_row['INFO'] if vp_info is None: vp_info = 'NULL' else: #vp_info = "'"+vp_info+"'" vp_info = vp_info.replace('"',"'") # 双引号替换为单引号 vp_info = '"'+vp_info+'"' # 防止字符里面本身包含单引号 vp_time_ms = process_row['TIME_MS'] vp_rows_sent = process_row['ROWS_SENT'] vp_rows_examined = process_row['ROWS_EXAMINED'] # v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO, # TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % ( # instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined) #try: v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO, TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s',%s,'%s',%d,'%s',%s,%d,%d,%d)''' % ( instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined) #print v_insert_sql db.execute(v_insert_sql.replace('%','%%')) #db.execute(v_insert_sql) # except Exception, e: # print e.message # print v_insert_sql # text = "insert process_list error!," + e.message + ',' + v_insert_sql # log_w(text) db_remote.close() i=i+1 db.close()
def upload_meta_tables(): #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') db.execute('truncate table meta_tables') # 先truncate 再插入 db.execute('truncate table meta_statistics') # 先truncate 再插入 db.execute('truncate table meta_redundant_keys') # 先truncate 再插入 # 由于id为64的E4S 服务器不在平台管辖,先手工剔除 b.id !=64 v_sql = r"""SELECT b.ip,b.port,b.id as instance_id,a.id as schema_id,a.name as db_name from resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id""" #print v_sql upload_tables_list = db.query(v_sql) if upload_tables_list: # 对实例表进行循环 i=0 for upload_table in upload_tables_list: instance_id = upload_table['instance_id'] schema_id = upload_table['schema_id'] db_name = upload_table['db_name'] host_ip = upload_table['ip'] mysql_port = upload_table['port'] v_host =host_ip + ':' + str(mysql_port) #连接远程实例 db_remote = Connection(v_host, 'information_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') # 取表的元信息然后插入 v_pl = r"""SELECT table_catalog,table_schema,table_name,table_type,engine,version,row_format, table_rows,avg_row_length,data_length,max_data_length,index_length,data_free,auto_increment,create_time, update_time,check_time,table_collation,checksum, create_options,table_comment from tables where table_type='BASE TABLE' and TABLE_SCHEMA='%s' """ % (db_name) #print v_pl table_list = db_remote.query(v_pl) for table_row in table_list: table_catalog = table_row['table_catalog'] table_schema = table_row['table_schema'] table_name = table_row['table_name'] table_type = table_row['table_type'] engine = table_row['engine'] version = table_row['version'] row_format = table_row['row_format'] table_rows = table_row['table_rows'] avg_row_length = table_row['avg_row_length'] data_length = table_row['data_length'] max_data_length = table_row['max_data_length'] index_length = table_row['index_length'] data_free = table_row['data_free'] auto_increment = table_row['auto_increment'] # 本来Int类型,但为了处理Null值插入,以字符串类型%s 进行插入 if auto_increment is None: auto_increment = 'NULL' else: auto_increment = "'"+str(auto_increment)+"'" # 日期型插入,由于要处理null值插入,经过处理后,以%s 进行插入 create_time = table_row['create_time'] if create_time is None: create_time = 'NULL' else: create_time = "'"+str(create_time)+"'" # 日期型插入,由于要处理null值插入,经过处理后,以%s 进行插入 update_time = table_row['update_time'] if update_time is None: update_time = 'NULL' else: update_time = "'"+str(update_time)+"'" check_time = table_row['check_time'] if check_time is None: check_time = 'NULL' else: check_time = "'"+str(check_time)+"'" table_collation = table_row['table_collation'] # 本来Int类型,但为了处理Null值插入,以字符串类型%s 进行插入 checksum = table_row['checksum'] if checksum is None: checksum = 'NULL' else: checksum = "'"+str(checksum)+"'" create_options = table_row['create_options'] table_comment = table_row['table_comment'] #try: v_insert_sql='''insert into meta_tables(instance_id,schema_id,table_catalog,table_schema,table_name, table_type,engine,version,row_format,table_rows,avg_row_length,data_length,max_data_length, index_length,data_free,auto_increment,create_time,update_time,check_time,table_collation, checksum,create_options,table_comment) values(%d,%d,'%s','%s','%s', '%s','%s',%d,'%s',%d,%d,%d,%d, %d,%d,%s,%s,%s,%s,'%s', %s,'%s','%s')''' % ( instance_id,schema_id,table_catalog,table_schema,table_name, table_type,engine,version,row_format,table_rows,avg_row_length,data_length,max_data_length, index_length,data_free,auto_increment,create_time,update_time,check_time,table_collation, checksum,create_options,table_comment ) #print v_insert_sql db.execute(v_insert_sql.replace('%','%%')) # except Exception, e: # print e.message # print v_insert_sql # text = "insert meta tables error!," + e.message + ',' + v_insert_sql # log_w(text) # 取索引的元信息,然后插入 v_pl2 = r"""SELECT table_catalog,table_schema,table_name,non_unique,index_schema, index_name,seq_in_index,column_name,collation,cardinality,sub_part,packed,nullable, index_type,comment,index_comment from statistics where TABLE_SCHEMA='%s' """ % (db_name) table_list2 = db_remote.query(v_pl2) for table_row2 in table_list2: table_catalog = table_row2['table_catalog'] table_schema = table_row2['table_schema'] table_name = table_row2['table_name'] non_unique = table_row2['non_unique'] index_schema = table_row2['index_schema'] index_name = table_row2['index_name'] seq_in_index = table_row2['seq_in_index'] column_name = table_row2['column_name'] collation = table_row2['collation'] cardinality = table_row2['cardinality'] # 本来Int类型,但为了处理Null值插入,以字符串类型%s 进行插入 if cardinality is None: cardinality = 'NULL' else: cardinality = "'"+str(cardinality)+"'" sub_part = table_row2['sub_part'] # 本来Int类型,但为了处理Null值插入,以字符串类型%s 进行插入 if sub_part is None: sub_part = 'NULL' else: sub_part = "'"+str(sub_part)+"'" packed = table_row2['packed'] if packed is None: packed = 'NULL' else: packed = '"'+packed+'"' # 防止字符里面本身包含单引号 nullable = table_row2['nullable'] index_type = table_row2['index_type'] comment = table_row2['comment'] index_comment = table_row2['index_comment'] #try: v_insert_sql2='''insert into meta_statistics(instance_id,schema_id,table_catalog, table_schema,table_name,non_unique,index_schema,index_name,seq_in_index, column_name,collation,cardinality,sub_part,packed,nullable,index_type,comment,index_comment) values(%d,%d,'%s', '%s','%s',%d,'%s','%s',%d, '%s','%s',%s,%s,%s,'%s','%s','%s','%s')''' % ( instance_id,schema_id,table_catalog, table_schema,table_name,non_unique,index_schema,index_name,seq_in_index, column_name,collation,cardinality,sub_part,packed,nullable,index_type,comment,index_comment ) #print v_insert_sql db.execute(v_insert_sql2.replace('%','%%')) # except Exception, e: # print e.message # print v_insert_sql # text = "insert meta tables error!," + e.message + ',' + v_insert_sql # log_w(text) db_remote.close() #连接远程实例,提取冗余索引信息 db_remote2 = Connection(v_host, 'common_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') # 取common_schema.redundant_keys 视图信息然后插入 v_pl3 = r"""SELECT table_schema,table_name,redundant_index_name,redundant_index_columns, redundant_index_non_unique,dominant_index_name,dominant_index_columns, dominant_index_non_unique,subpart_exists,sql_drop_index from redundant_keys where table_schema='%s' """ % (db_name) #print v_pl table_list3 = db_remote2.query(v_pl3) for table_row3 in table_list3: table_schema = table_row3['table_schema'] table_name = table_row3['table_name'] redundant_index_name = table_row3['redundant_index_name'] redundant_index_columns = table_row3['redundant_index_columns'] redundant_index_non_unique = table_row3['redundant_index_non_unique'] dominant_index_name = table_row3['dominant_index_name'] dominant_index_columns = table_row3['dominant_index_columns'] dominant_index_non_unique = table_row3['dominant_index_non_unique'] subpart_exists = table_row3['subpart_exists'] sql_drop_index = table_row3['sql_drop_index'] #try: v_insert_sql3='''insert into meta_redundant_keys(instance_id,schema_id,table_schema, table_name,redundant_index_name,redundant_index_columns,redundant_index_non_unique, dominant_index_name,dominant_index_columns, dominant_index_non_unique,subpart_exists,sql_drop_index) values(%d,%d,'%s', '%s','%s','%s',%d, '%s','%s', %d,%d,'%s')''' % ( instance_id,schema_id,table_schema, table_name,redundant_index_name,redundant_index_columns,redundant_index_non_unique, dominant_index_name,dominant_index_columns, dominant_index_non_unique,subpart_exists,sql_drop_index ) #print v_insert_sql db.execute(v_insert_sql3.replace('%','%%')) db_remote2.close() i=i+1 db.close()
def upload_user_stats(): #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') db.execute('truncate table meta_table_statistics') # 先truncate 再插入 db.execute('truncate table meta_index_statistics') # 先truncate 再插入 # 由于id为64的E4S 服务器不在平台管辖,先手工剔除 b.id !=64 v_sql = r"""SELECT b.ip,b.port,b.id as instance_id,a.id as schema_id,a.name as db_name from resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id""" #print v_sql upload_tables_list = db.query(v_sql) if upload_tables_list: # 对实例表进行循环 i=0 for upload_table in upload_tables_list: instance_id = upload_table['instance_id'] schema_id = upload_table['schema_id'] db_name = upload_table['db_name'] host_ip = upload_table['ip'] mysql_port = upload_table['port'] v_host =host_ip + ':' + str(mysql_port) #连接远程实例 db_remote = Connection(v_host, 'information_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') # 取表的元信息然后插入 v_pl = r"""SELECT table_schema,table_name,rows_read,rows_changed,rows_changed_x_indexes from table_statistics where table_schema='%s' """ % (db_name) #print v_pl table_list = db_remote.query(v_pl) for table_row in table_list: table_schema = table_row['table_schema'] table_name = table_row['table_name'] rows_read = table_row['rows_read'] rows_changed = table_row['rows_changed'] rows_changed_x_indexes = table_row['rows_changed_x_indexes'] #try: v_insert_sql='''insert into meta_table_statistics(instance_id,schema_id,table_schema, table_name,rows_read,rows_changed,rows_changed_x_indexes) values(%d,%d,'%s','%s',%d,%d,%d)''' % ( instance_id,schema_id,table_schema, table_name,rows_read,rows_changed,rows_changed_x_indexes ) #print v_insert_sql db.execute(v_insert_sql.replace('%','%%')) # except Exception, e: # print e.message # print v_insert_sql # text = "insert meta tables error!," + e.message + ',' + v_insert_sql # log_w(text) # 取索引的元信息,然后插入 v_pl2 = r"""SELECT table_schema,table_name,index_name,rows_read from index_statistics where TABLE_SCHEMA='%s' """ % (db_name) table_list2 = db_remote.query(v_pl2) for table_row2 in table_list2: table_schema = table_row2['table_schema'] table_name = table_row2['table_name'] index_name = table_row2['index_name'] rows_read = table_row2['rows_read'] #try: v_insert_sql2='''insert into meta_index_statistics(instance_id,schema_id,table_schema, table_name,index_name,rows_read) values(%d,%d,'%s','%s','%s',%d)''' % ( instance_id,schema_id,table_schema, table_name,index_name,rows_read ) #print v_insert_sql db.execute(v_insert_sql2.replace('%','%%')) # except Exception, e: # print e.message # print v_insert_sql # text = "insert meta tables error!," + e.message + ',' + v_insert_sql # log_w(text) db_remote.close() # 开始生成log_hot_tables 统计表数据 # statistic_time 为统计数据的实际生成时间 v_insert_sql3='''insert into log_hot_tables(instance_id,schema_id,table_schema, table_name,TABLE_COMMENT,TABLE_CREATE_TIME,rows_read,rows_changed, rows_changed_x_indexes) select a.instance_id,a.schema_id,a.TABLE_SCHEMA, a.TABLE_NAME,b.TABLE_COMMENT,b.CREATE_TIME,a.ROWS_READ,a.ROWS_CHANGED, a.ROWS_CHANGED_X_INDEXES from meta_table_statistics a,meta_tables b where a.instance_id=%d and a.schema_id=%d and a.TABLE_SCHEMA='%s' and a.instance_id=b.instance_id and a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME''' % ( instance_id,schema_id,table_schema ) print v_insert_sql3 db.execute(v_insert_sql3.replace('%','%%')) i=i+1 db.close() # 开始远程flush 统计信息表 # for remote execute os_user = '******' OS_APPS_PASSWD = config.OS_APPS_PASSWD DB_USER = config.DB_USER DB_PASSWD = config.DB_PASSWD #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') # 由于id为64的E4S 服务器不在平台管辖,先手工剔除 b.id !=64 v_sql = r"""SELECT distinct b.ip,b.port from resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id""" #print v_sql server_list = db.query(v_sql) if server_list: # 对实例表进行循环 i=0 for single_server in server_list: host_ip = single_server['ip'] mysql_port = single_server['port'] # 远程paramiko调用 在本机执行sql exe_sql = 'flush table_statistics;flush index_statistics;' result = func.remote_exe_sql(host_ip,os_user,OS_APPS_PASSWD,mysql_port,'information_schema',exe_sql,DB_USER,DB_PASSWD) if result == '': result = '执行成功!' print result
def upload_processlist(): #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1""" #print v_sql upload_server_list = db.query(v_sql) if upload_server_list: # 对实例表进行循环 i=0 for upload_server in upload_server_list: instance_id = upload_server['id'] host_ip = upload_server['ip'] mysql_port = upload_server['port'] v_host =host_ip + ':' + str(mysql_port) #连接远程实例 db_remote = Connection(v_host, 'information_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') v_pl = r"""SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED from PROCESSLIST""" #print v_pl process_list = db_remote.query(v_pl) for process_row in process_list: vp_id = process_row['ID'] vp_user = process_row['USER'] vp_host = process_row['HOST'] vp_db = process_row['DB'] vp_command = process_row['COMMAND'] vp_time = process_row['TIME'] vp_state = process_row['STATE'] vp_info = process_row['INFO'] vp_time_ms = process_row['TIME_MS'] vp_rows_sent = process_row['ROWS_SENT'] vp_rows_examined = process_row['ROWS_EXAMINED'] v_insert_sql='''insert into processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO, TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % ( instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined) db.execute(v_insert_sql) db_remote.close() i=i+1 db.close()
def upload_auto_increment(): #连接配置中心库 db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1""" print v_sql upload_server_list = db.query(v_sql) if upload_server_list: # 对实例表进行循环 i=0 for upload_server in upload_server_list: instance_id = upload_server['id'] host_ip = upload_server['ip'] mysql_port = upload_server['port'] v_host =host_ip + ':' + str(mysql_port) print v_host #连接远程实例 db_remote = Connection(v_host, 'information_schema', config.DBA_QUERY_USER, config.DBA_QUERY_PASSWD, time_zone='+8:00') v_pl = r"""select information_schema . columns . TABLE_SCHEMA AS TABLE_SCHEMA, information_schema . columns . TABLE_NAME AS TABLE_NAME, information_schema . columns . COLUMN_NAME AS COLUMN_NAME, information_schema . columns . DATA_TYPE AS DATA_TYPE, information_schema . columns . COLUMN_TYPE AS COLUMN_TYPE, ((case information_schema . columns . DATA_TYPE when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned', information_schema . columns . COLUMN_TYPE) > 0), 0, 1)) AS MAX_VALUE, information_schema . tables . AUTO_INCREMENT AS AUTO_INCREMENT, (information_schema . tables . AUTO_INCREMENT / ((case information_schema . columns . DATA_TYPE when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned', information_schema . columns . COLUMN_TYPE) > 0), 0, 1))) AS AUTO_INCREMENT_RATIO from (information_schema . columns join information_schema . tables on(((information_schema . columns . TABLE_SCHEMA = information_schema . tables . TABLE_SCHEMA) and (information_schema . columns . TABLE_NAME = information_schema . tables . TABLE_NAME)))) where ((information_schema . columns .TABLE_SCHEMA not in ('mysql', 'INFORMATION_SCHEMA', 'performance_schema','common_schema')) and (information_schema . tables . TABLE_TYPE = 'BASE TABLE') and (information_schema . columns . EXTRA = 'auto_increment'))""" print v_pl process_list = db_remote.query(v_pl) for process_row in process_list: vp_server_ip = v_host vp_instance_id = instance_id #vp_id = process_row['ID'] vp_table_schema = process_row['TABLE_SCHEMA'] vp_table_name = process_row['TABLE_NAME'] vp_column_name = process_row['COLUMN_NAME'] vp_data_type = process_row['DATA_TYPE'] # 若是空,变成mysql的null,否则加上引号再传递进去,格式为%s ,而不是'%s' #if vp_db is None: # vp_db = 'NULL' #else: # vp_db = "'"+vp_db+"'" #print vp_db vp_column_type = process_row['COLUMN_TYPE'] vp_max_value = process_row['MAX_VALUE'] vp_auto_increment = process_row['AUTO_INCREMENT'] vp_auto_increment_ratio = process_row['AUTO_INCREMENT_RATIO'] #vp_info = process_row['INFO'] #if vp_info is None: # vp_info = 'NULL' #else: # #vp_info = "'"+vp_info+"'" # vp_info = vp_info.replace('"',"'") # 双引号替换为单引号 # vp_info = '"'+vp_info+'"' # 防止字符里面本身包含单引号 # v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO, # TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % ( # instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined) #try: v_delete_sql='''delete from auto_increment_list where INSTANCE_ID = '%s' and TABLE_SCHEMA = '%s' and TABLE_NAME = '%s' and COLUMN_NAME = '%s' ''' % ( vp_instance_id,vp_table_schema,vp_table_name,vp_column_name) print v_delete_sql db.execute(v_delete_sql) v_insert_sql='''insert into auto_increment_list(instance_id,server_ip,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,MAX_VALUE,AUTO_INCREMENT,AUTO_INCREMENT_RATIO) values(%d,'%s','%s','%s','%s','%s','%s',%d,%d,%d)''' % ( vp_instance_id,vp_server_ip,vp_table_schema,vp_table_name,vp_column_name,vp_data_type,vp_column_type,int(vp_max_value),int(vp_auto_increment),int(vp_auto_increment_ratio)) print v_insert_sql db.execute(v_insert_sql) #db.execute(v_insert_sql) # except Exception, e: # print e.message # print v_insert_sql # text = "insert process_list error!," + e.message + ',' + v_insert_sql # log_w(text) db_remote.close() i=i+1 v_sendmail_sql=r'''select count(*) count from auto_increment_list b where AUTO_INCREMENT_RATIO >= 80 ''' if_list = db.query(v_sendmail_sql) for if_row in if_list: v_if = if_row['count'] print v_if if v_if >= 1 : print '11' v_warn_sql=r'''select SERVER_IP,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,MAX_VALUE,AUTO_INCREMENT,AUTO_INCREMENT_RATIO from auto_increment_list b where AUTO_INCREMENT_RATIO >= 80 ''' print v_warn_sql warn_list = db.query(v_warn_sql) v_server_ip = '\r\n' for warn_row in warn_list: v_server_ip = v_server_ip + warn_row['SERVER_IP'] +'|对象名:' + warn_row['TABLE_SCHEMA'] +'|表名:'+ warn_row['TABLE_NAME'] + '|字段名:' + warn_row['COLUMN_NAME']+'\r\n' print v_server_ip v_msg_text = v_server_ip v_receiver = '[email protected],[email protected]' v_subject = '有快溢出的自增ID,细节请去监控页面查看' v_return = func.send_mail_to_devs(v_receiver,v_subject,v_msg_text) db.close()