def get_single_rollback(backupDb,opid_time): sqllist = [] tbNamesql = "select tablename from %s.$_$Inception_backup_information$_$ where opid_time='%s';" % (backupDb, opid_time) results,col = func.mysql_query(tbNamesql,incept_backup_user,incept_backup_passwd,incept_backup_host,int(incept_backup_port),backupDb) tbName = results[0][0] backsql = "select rollback_statement from %s.%s where opid_time='%s' order by id desc" % (backupDb, tbName, opid_time) results,col = func.mysql_query(backsql,incept_backup_user,incept_backup_passwd,incept_backup_host,int(incept_backup_port),backupDb) for row in results : sqllist.append(row[0]) return sqllist
def task_running_status(idnum): task = Task.objects.get(id=idnum) if task.status=='executed failed'or task.status=='executed': data = Incep_error_log.objects.filter(create_time=task.create_time).filter(finish_time=task.update_time).order_by("-myid") col =[f.name for f in Incep_error_log._meta.get_fields()] #delete first element "ID" del col[0] return data,col elif task.status == 'running': text = task.sqlsha if text=='': try: tar_username, tar_passwd, tar_host, tar_port,tar_dbname = get_db_info(task.dbtag) sql = "select * from information_schema.processlist where Db='" + tar_dbname + "'" + " and USER='******' order by TIME desc" return func.mysql_query(sql, tar_username, tar_passwd, tar_host, int(tar_port), 'information_schema') except Exception,e: return(['get info wrong'],''),['info'] else: for i in text.split('^^'): x = i.split('*') if len(x)>=2: sqlsha = '*'+ x[1] datalist,collist,mynum = incep_getstatus(sqlsha) #add sqltext to the end of the tuple if mynum >0: for d in datalist: data=d+(x[0],) collist.append('SQLTEXT') cols = collist data = (data,) break if not vars().has_key('data'): data = (['wait in running queue'], '') cols = ['info'] return data,cols
def backupdb_check_db(user, passwd, host, port, dbname): str_db = str(host).replace( '.', '_') + '_' + str(port) + '_' + str(user).lower() chk_sql = "SELECT count(1) rowcount FROM mysql.`db` a WHERE a.db='%s'" % str_db results_0, col_0 = func.mysql_query(chk_sql, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), 'mysql') if results_0 is not None: if long(str(results_0[0][0])) < 1: cdb_sql = "CREATE DATABASE IF NOT EXISTS %s default charset utf8 COLLATE utf8_general_ci" % str_db rowsinfo, sucess_flag = func.mysql_exec(cdb_sql, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), 'mysql') str_inception_backup_information = '$_$inception_backup_information$_$' chk_sql_inception_backup_information = "select count(1) rowcount from information_schema.`TABLES` a where a.TABLE_SCHEMA='%s' and a.TABLE_NAME='%s'" % ( str_db, str_inception_backup_information) results_1, col_1 = func.mysql_query(chk_sql_inception_backup_information, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), 'mysql') if results_1 is not None: if long(str(results_1[0][0])) < 1: str_inception_backup_information_ddl = '''CREATE TABLE `$_$inception_backup_information$_$` ( `opid_time` varchar(50) NOT NULL DEFAULT '', `start_binlog_file` varchar(512) DEFAULT NULL, `start_binlog_pos` int(11) DEFAULT NULL, `end_binlog_file` varchar(512) DEFAULT NULL, `end_binlog_pos` int(11) DEFAULT NULL, `sql_statement` text, `host` varchar(64) DEFAULT NULL, `dbname` varchar(64) DEFAULT NULL, `tablename` varchar(64) DEFAULT NULL, `port` int(11) DEFAULT NULL, `time` timestamp NULL DEFAULT NULL, `type` varchar(20) DEFAULT NULL, PRIMARY KEY (`opid_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8''' results_2, col_2 = func.mysql_exec( str_inception_backup_information_ddl, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), str_db)
def inception_check(hosttag,sql,flag=0): make_sure_mysql_usable() a = Db_name.objects.get(dbtag=hosttag) #a = Db_name.objects.get(dbtag=hosttag) tar_dbname = a.dbname if (not cmp(sql,wrong_msg)): results,col = func.mysql_query(wrong_msg,user,passwd,host,int(port),dbname) return results,col,tar_dbname try: if a.instance.all().filter(role='write')[0]: tar_host = a.instance.all().filter(role='write')[0].ip tar_port = a.instance.all().filter(role='write')[0].port except Exception,e: try: tar_host = a.instance.all().filter(role='all')[0].ip tar_port = a.instance.all().filter(role='all')[0].port except Exception,e: wrongmsg = "select \"" +str(e).replace('"',"\"")+"\"" results,col = func.mysql_query(wrongmsg,user,passwd,host,int(port),dbname) return results,col,tar_dbname
def backupdb_check_table(user, passwd, host, port, dbname, tablename, undo_sql, sequence, sql, in_sql_type): str_db = str(host).replace( '.', '_') + '_' + str(port) + '_' + str(user).lower() chk_sql_inception_backup_information = "select count(1) rowcount from information_schema.`TABLES` a where a.TABLE_SCHEMA='%s' and a.TABLE_NAME='%s'" % ( str_db, tablename) results_1, col_1 = func.mysql_query(chk_sql_inception_backup_information, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), str_db) if results_1 is not None: if long(str(results_1[0][0])) < 1: str_inception_backup_table = '''CREATE TABLE %s ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `rollback_statement` mediumtext, `opid_time` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8''' % tablename results_2, col_2 = func.mysql_exec(str_inception_backup_table, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), str_db) #insert into inception_backup_information sql_inception_backup_information = '''INSERT INTO %s.`$_$inception_backup_information$_$` (`opid_time`, `start_binlog_file`, `start_binlog_pos`, `end_binlog_file`, `end_binlog_pos`, `sql_statement`, `host`, `dbname`, `tablename`, `port`, `time`, `type`) VALUES ('%s', '', '0', '', '0', "%s", '%s', '%s', '%s', '%s',now(), '%s')''' % ( str_db, sequence, sql, host, dbname, tablename, str(port), str(in_sql_type).upper()) rowcount0, info0 = func.mysql_exec(sql_inception_backup_information, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), str_db) #insert into tble sql_inception_backup_table = '''INSERT INTO %s.%s ( `rollback_statement`, `opid_time`) VALUES ( "%s", '%s')''' % (str_db, tablename, undo_sql, sequence) rowcount1, info1 = func.mysql_exec(sql_inception_backup_table, incept_backup_user, incept_backup_passwd, incept_backup_host, int(incept_backup_port), str_db) if info0[0] == 'success' and info1[0] == 'success': return 'success', 1 else: return 'failed', 0
except Exception,e: wrongmsg = "select \"" +str(e).replace('"',"\"")+"\"" results,col = func.mysql_query(wrongmsg,user,passwd,host,int(port),dbname) return results,col,tar_dbname for i in a.db_account_set.all(): if i.role=='admin': tar_username = i.user tar_passwd = i.passwd break #print tar_port+tar_passwd+tar_username+tar_host try: results,col = incep_exec(sql,tar_username,tar_passwd,tar_host,tar_port,tar_dbname,flag) return results,col,tar_dbname except Exception,e: wrongmsg = "select \"no admin account being setted\"" results, col = func.mysql_query(wrongmsg, user, passwd, host, int(port), dbname) return results, col, tar_dbname # def process_runtask(hosttag,sqltext,mytask): # time.sleep(1) # results,col,tar_dbname = inception_check(hosttag,sqltext,1) # status='executed' # c_time = mytask.create_time # mytask.update_time = datetime.datetime.now() # make_sure_mysql_usable() # mytask.save() # for row in results: # try: # inclog = Incep_error_log(myid=row[0],stage=row[1],errlevel=row[2],stagestatus=row[3],errormessage=row[4],\ # sqltext=row[5],affectrow=row[6],sequence=row[7],backup_db=row[8],execute_time=row[9],sqlsha=row[10],\