def main(): db1 = Connection(options.database_host, options.database_user, options.database_password) #grab all shared files in order sfs = db1.query("""SELECT id FROM sharedfile ORDER BY created_at""") #for each, get counts for sf in sfs: likes = 0 saves = 0 like_counts = db1.query( "SELECT count(id) as like_count from favorite where sharedfile_id = %s and deleted=0", (sf.id)) if like_counts: likes = like_counts[0]['like_count'] save_counts = db1.query( "SELECT count(id) AS save_count FROM sharedfile WHERE original_id = %s and deleted = 0", sf.id) if save_counts[0]['save_count'] > 0: saves = save_counts[0]['save_count'] else: save_counts = db1.query( "SELECT count(id) AS save_count FROM sharedfile WHERE parent_id = %s and deleted = 0", sf.id) saves = save_counts[0]['save_count'] if likes > 0 or saves > 0: print "UPDATE sharedfile SET like_count = %s, save_count = %s WHERE id = %s" % ( likes, saves, sf.id) print db1.execute( "UPDATE sharedfile SET like_count = %s, save_count = %s WHERE id = %s", likes, saves, sf.id)
def get(self, article_id): # template_name = "article_details.html" template_name = "mobile/article_details.html" db = Connection(settings.DATABASE_SERVER, settings.DATABASE_NAME, settings.DATABASE_USER, settings.DATABASE_PASSWORD, ) sql = "SELECT * FROM articles WHERE id='{0}'".format( article_id) article = db.query(sql)[0] # article["read_count"], article["comment_count"] = \ # get_article_statistics(db, article_id) article["url"] = urllib.quote(article["url"]) # Update article read count now = datetime.datetime.now() sql = """INSERT INTO article_reads (`article_id`, `user_id`, `time`) VALUES ('{0}', '{1}', '{2}') """.format(article_id, 0, now) db.execute(sql) kwargs = dict(article=article, day=article["day"]) super(ArticleDetailsHandler, self).render( template_name, **kwargs )
def main(): db1 = Connection(options.database_host, options.database_user, options.database_password) db1.execute("DELETE FROM post WHERE 1") ssfs = db1.query("""SELECT shake_id, sharedfile_id from shakesharedfile order by created_at""") for shakesharedfile in ssfs: sf = db1.get("""SELECT id, source_id, name, deleted, created_at FROM sharedfile WHERE id = %s""", shakesharedfile['sharedfile_id']) print "%s. Adding posts for sharedfile: %s created at %s." % (sf.id, sf.name, sf.created_at) add_posts(shake_id=shakesharedfile['shake_id'], sharedfile_id=sf['id'], sourcefile_id=sf['source_id'], deleted=sf['deleted'], created_at=sf['created_at'])
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 main(): db1 = Connection(options.database_host, options.database_user, options.database_password) #grab all group shakes shakes = db1.query("""SELECT id, user_id FROM shake WHERE type=%s""", "group") for shake in shakes: db1.execute( """INSERT IGNORE INTO subscription (user_id, shake_id, deleted, created_at, updated_at) VALUES (%s, %s, 0, NOW(), NOW())""", shake['user_id'], shake['id'])
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 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 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 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 post(self): title = self.get_argument("title", "") author = self.get_argument("author", "") category = self.get_argument("category", "") date = self.get_argument("date", "") profile = self.get_argument("profile", "") picUrl = self.get_argument("picUrl", "") url = self.get_argument("url", "") time = datetime.datetime.strptime(date, "%m/%d/%Y") day = { 0: "Mon", 1: "Tue", 2: "Wed", 3: "Thu", 4: "Fri", 5: "Sat", 6: "Sun", }[time.weekday()] sql = """INSERT INTO articles (`title`, `author`, `day`, `time`, `url`, `profile`, `picUrl`, `category`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}'); """.format( title, author, day, time, url, profile, picUrl, category ) db = Connection(settings.DATABASE_SERVER, settings.DATABASE_NAME, settings.DATABASE_USER, settings.DATABASE_PASSWORD, ) lastrowid = db.execute(sql) if lastrowid: self.redirect("/articles/details/{0}/".format(lastrowid)) else: template_name = "/upload_error.html" self.render(template_name)
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 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 main(): db1 = Connection(options.database_host, options.database_user, options.database_password) db1.execute("UPDATE shakesharedfile SET deleted = 1 WHERE deleted = 0 AND sharedfile_id IN (SELECT id FROM sharedfile WHERE deleted = 1)")
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 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()
# to learn how to use it. # Installation: pip install torndb # Official doc: http://torndb.readthedocs.org/en/latest/ # Source: https://github.com/bdarnell/torndb/blob/master/torndb.py from torndb import Connection # Connect by IP address db = Connection('127.0.0.1', 'database_name', user='******', password='******') # Connect by IP address with port db = Connection('127.0.0.1:1234', 'database_name', user='******', password='******') # Connect by socket db = Connection('/tmp/mysql.sock', 'database_name', user='******', password='******') # Connection over SSH, open a SSH tunnel with # ssh -L 1234:localhost:3306 [email protected] # then connect to 127.0.0.1:1234 # Retreive one object post = db.get("SELECT * FROM posts LIMIT 1") # Retreive several objects for post in db.query("SELECT * FROM posts"): print post.title # Insert one entry db.execute("INSERT INTO posts (user_id, content) VALUES (%s, %s)", 12, "hello world !") # Insert multiple entries values = [(12, "hello"), (17, "world"), (22, "blah")] db.executemany("INSERT INTO posts (user_id, content) VALUES (%s, %s)", values)
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()
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() '''
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 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()
# -*- coding: utf-8 -*- from torndb import Connection db = Connection('127.0.0.1', 'mysql', 'root') try: db.execute('create database `car_spider`;') print 'create database success.' except: print 'database exists.' pass car_info_sql = """ CREATE TABLE `car_spider`.`car` ( `id` int(11) NOT NULL AUTO_INCREMENT, `logo` varchar(200) NOT NULL, `model` varchar(255), `befor_price` varchar(100), `after_price` int(12), `plan` varchar(500), `Purchased` int(12), `link` varchar(255) default "", `created` datetime, `updated` datetime, `status` varchar(10), PRIMARY KEY (`id`), INDEX `idx_dt` (created) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; """
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()
class Database: """docstring for ClassName""" def __init__( self, master_host,from_host, to_host, db_ip_priv, os_user,os_password_source,os_password_target,os_password_priv, db_user,db_user_pwd_source,db_user_pwd_target, db_user_pwd_priv, db_port_master,db_port_source, db_port_target,db_port_priv, db_user_name_rep,db_rep_pwd, db_root_user,db_root_pwd_target): self.master_host = master_host self.from_host = from_host self.to_host = to_host self.db_ip_priv = db_ip_priv self.port = 22 #ssh 端口 self.os_user = os_user self.os_password_source = os_password_source self.os_password_target = os_password_target self.os_password_priv = os_password_priv self.db_user_name = db_user self.db_user_pwd_source = db_user_pwd_source self.db_user_pwd_target = db_user_pwd_target self.db_user_pwd_priv = db_user_pwd_priv self.db_port_master = db_port_master self.db_port_source = db_port_source self.db_port_target = db_port_target self.db_port_priv = db_port_priv self.db_user_name_rep = db_user_name_rep self.db_rep_pwd = db_rep_pwd self.db_root_user = db_root_user self.db_root_pwd_target = db_root_pwd_target self.today = datetime.date.today().strftime("%Y%m%d") self.xtra_time = datetime.datetime.now().strftime('%Y-%m-%d_%H%M%S') self.xtrabackup_bin_path = config.xtrabackup_bin_path self.xtrabackup_export_path = config.xtrabackup_export_path #self.xtrabackup_restore_path = config.xtrabackup_restore_path self.xtrabackup_restore_path = config.mysql_datadir_path self.mysql_client_path = config.mysql_client_path self.mydumper_bin_path = config.mydumper_bin_path self.mydumper_export_path = config.mydumper_export_path self.mydumper_import_path = config.mydumper_import_path self.db = Connection('/tmp/mysql3306.sock', config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone='+8:00') def export_database_metadata(self,v_prosess_id): # 导出数据库的表结构和视图以及存储过程 text = "%s %s" % ( datetime.datetime.now(), "One: Begin export master Database table stru,views,procs, Please wait ....") print "\033[1;32;40m%s\033[0m" % text # 绿色 log_w(text) v_db_socket='--socket=/tmp/mysql'+str(self.db_port_source)+'.sock' try: s = paramiko.SSHClient() s.set_missing_host_key_policy(paramiko.AutoAddPolicy()) s.connect(self.from_host, self.port, self.os_user, self.os_password_source) # 获取 mysqldump 要导出的数据库列表清单 # 不能加grep -v ,因为logplatform 数据库会被一起过滤掉 conm_db_list_1 = r'''%s/mysql -N -u%s %s -e"show databases"|grep -v information_schema|grep -v common_schema|grep -v performance_schema|grep -v mysql|tr "\n" " "''' % ( self.mysql_client_path,self.db_user_name,v_db_socket) v_update_sql = '''update dba_job_progress set cur_prog_shell_cmd = ' ''' + conm_db_list_1 +''' ' where id= '''+ str(v_prosess_id) print conm_db_list_1 print v_update_sql self.db.execute(v_update_sql) conm_db_list = r'''%s/mysql -N -u%s -p'%s' %s -e"show databases"|grep -v information_schema|grep -v common_schema|grep -v performance_schema|grep -v mysql|tr "\n" " "''' % ( self.mysql_client_path,self.db_user_name,self.db_user_pwd_source,v_db_socket) print conm_db_list stdin, stdout, stderr = s.exec_command(conm_db_list) if stdout.channel.recv_exit_status() ==0: db_list_str = stdout.readlines()[-1] #返回值本身就一行 text = "%s Get mysqldump db list Execute success !" % datetime.datetime.now() log_w(text) print "\033[1;32;40m%s\033[0m" % text # 绿色 else: result = stderr.readlines()[-1].strip() text = "%s Get mysqldump db list execute Error ! %s " % (datetime.datetime.now(),result) log_w(text) print "\033[1;31;40m%s\033[0m" % text # 古铜色 return result # 退出 # mysqldump 导出 表结构和视图和存储过程 conm_1 = r'''mkdir -p %s/bak;%s/mysqldump -u%s %s -f --single-transaction -d -R --skip-triggers -B %s > %s/struc.sql''' % ( self.mydumper_export_path,self.mysql_client_path,self.db_user_name,v_db_socket, db_list_str,self.mydumper_export_path) v_update_sql = '''update dba_job_progress set cur_prog_shell_cmd = ' ''' + conm_1 +''' ' where id= '''+ str(v_prosess_id) print conm_1 print v_update_sql self.db.execute(v_update_sql) conm = r'''mkdir -p %s/bak;%s/mysqldump -u%s -p'%s' %s -f --single-transaction -d -R --skip-triggers -B %s > %s/struc.sql''' % ( self.mydumper_export_path,self.mysql_client_path,self.db_user_name,self.db_user_pwd_source,v_db_socket, db_list_str,self.mydumper_export_path) print conm stdin, stdout, stderr = s.exec_command(conm) if stdout.channel.recv_exit_status() ==0: result = '' else: result = stderr.readlines()[-1].strip() s.close() if result == '': text = "%s Mysqldump export table structure Execute success !" % datetime.datetime.now() log_w(text) print "\033[1;32;40m%s\033[0m" % text # 绿色 else: text = "%s Mysqldump export table structure Execute Error ! %s " % (datetime.datetime.now(),result) log_w(text) print "\033[1;31;40m%s\033[0m" % text # 古铜色 return result except Exception, e: print e.message text = "Mysqldump export table structure Error ! Error Reason: %s" % (e.message) log_w(text) print "\033[1;31;40m%s\033[0m" % text return text
'test.functional.create_account_tests', 'test.functional.verify_email_tests', 'test.functional.api_tests', 'test.functional.voucher_tests', 'test.scripts.calculate_views_tests', ] def all(): return unittest.defaultTestLoader.loadTestsFromNames(TEST_MODULES) if __name__ == '__main__': mltshpoptions.parse_dictionary(test_settings) import tornado.testing db = Connection(options.database_host, 'mysql', options.database_user, options.database_password) try: db.execute("CREATE database %s" % options.database_name) except MySQLdb.ProgrammingError, exc: if exc.args[0] != 1007: # database already exists raise else: with open("setup/db-install.sql") as f: load_query = f.read() db.execute("USE %s" % options.database_name) db.execute(load_query) tornado.testing.main()
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 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()