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 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 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],\
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],\