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 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 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 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 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 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
# 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 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 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()