def step_impl(context):
mysql_node = get_node("mysql-master1")
# copy dble's dbseq.sql to local
dble_node = get_node("dble-1")
source_remote_file = "{0}/dble/conf/dbseq.sql".format(
dble_node.install_dir)
target_remote_file = "{0}/data/dbseq.sql".format(mysql_node.install_path)
local_file = "{0}/dbseq.sql".format(os.getcwd())
ssh_client = mysql_node.ssh_conn
cmd = "rm -rf {0}".format(local_file)
ssh_client.exec_command(cmd)
context.ssh_sftp.sftp_get(source_remote_file, local_file)
mysql_node.sftp_conn.sftp_put(local_file, target_remote_file)
cmd = "mysql -utest -p111111 db1 < {0}".format(target_remote_file)
ssh_client.exec_command(cmd)
#execute dbseq.sql at the node configed in sequence file
execute_sql_in_host(
"mysql-master1",
info_dic={
"sql":
"insert into DBLE_SEQUENCE values ('`schema1`.`test_auto`', 3, 1)",
"db": "db1"
})
def restore(self):
# if "restore_sys_time" in self._scenario.tags:
# utils.restore_sys_time()
#
# if "aft_reset_replication" in self._scenario.tags:
# utils.reset_repl()
#
if "restore_mysql_service" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_service'")
if params_dic:
paras = params_dic["restore_mysql_service"]
else:
paras = {}
logger.debug("try to restore_mysql_service: {0}".format(paras))
for host_name, mysql_vars in paras.items():
logger.debug("the value of host_name is: {0}".format(host_name))
for k, v in mysql_vars.items():
logger.debug("the value of v is: {0}".format(v))
if v:
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.start()
if "restore_global_setting" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_global_setting'")
if params_dic:
paras = params_dic["restore_global_setting"]
else:
paras = {}
logger.debug("try to restore_global_setting of mysqls: {0}".format(paras))
for mysql, mysql_vars in paras.items():
query = "set global "
for k, v in mysql_vars.items():
query = query + "{0}={1},".format(k, v)
sql = query[:-1]#delete the last ','
execute_sql_in_host(mysql, {"sql": sql})
if "restore_mysql_config" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_config'")
if params_dic:
paras = params_dic["restore_mysql_config"]
else:
paras = {}
logger.debug("try to restore_mysql_config of mysqls: {0}".format(paras))
for host_name, mysql_vars in paras.items():
sed_str = ""
for k, v in mysql_vars.items():
sed_str += "/{0}/d\n/server-id/a {0}={1}\n".format(k, v)
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.restart(sed_str)
def step_impl(context, sql, host, results):
for row in context.table:
dict = row.as_dict()
resultList = getattr(context, results)
for result in resultList:
sql = sql + ' ' + '"{0}"'.format(result)
dict.update({"sql": sql})
execute_sql_in_host(host, dict, "mysql")
def step_impl(context, hostname, num, tablename, dbname="schema1"):
sql = ("insert into {0} (id,name) values".format(tablename))
end = int(num)
for i in range(1, end + 1):
inspection_num = 'NJ' + str(100000 + i)
if (i == end):
sql = sql + ("({0},'{1}');".format(i, inspection_num))
else:
sql = sql + ("({0},'{1}'),".format(i, inspection_num))
execute_sql_in_host(hostname, {"sql": sql, "db": dbname}, "user")
def step_impl(context, num):
sql = "insert into test_table values"
gen = generate()
value_nu = int(num)
for i in range(1, value_nu):
c_str = gen.rand_string(10)
pad_str = gen.rand_string(60)
sql += "({0}, {0}, '{1}', '{2}'),".format(i, c_str, pad_str)
c_str = gen.rand_string(10)
pad_str = gen.rand_string(60)
sql += "({0}, {0}, '{1}', '{2}')".format(i + 1, c_str, pad_str)
execute_sql_in_host("dble-1", {"sql": sql}, "user")
def step_impl(context, host_name, result_key, mode_name=None):
row = context.table[0]
info_dict = row.as_dict()
key = result_key
res, _ = execute_sql_in_host(host_name, info_dict, mode_name)
setattr(context, result_key, res)
def step_impl(context, host_name, mode_name, rs_name, result_key=None):
param_value = getattr(context, rs_name)
assert param_value, "expect parameter not found in {0}".format(rs_name)
context.logger.debug("the parameter value is {0} ".format(param_value))
info_dict = context.table[0].as_dict()
info_dict['sql'] = info_dict['sql'].format(param_value)
context.logger.debug("the sql value is {0} ".format(info_dict['sql']))
if 'expect' in info_dict:
expect = info_dict['expect']
length_obj = re.search(r"length\{(.*?)\}", expect, re.I)
context.logger.debug("the length_obj value is {0} ".format(length_obj))
if not length_obj:
info_dict['expect'] = expect.format(param_value)
context.logger.debug("the expect value is {0} ".format(
info_dict['expect']))
res, _ = execute_sql_in_host(host_name, info_dict, mode_name)
if result_key is not None:
setattr(context, result_key, res)
context.logger.debug("the resultset {0}] is {1}".format(
result_key, getattr(context, result_key)))
def step_impl(context, host_name, mode_name="user"):
for row in context.table:
dble_res, dble_err = execute_sql_in_host(host_name, row.as_dict(),
mode_name)
mysql_res, mysql_err = execute_sql_in_host("mysql", row.as_dict(),
"mysql")
if len(dble_res) == len(mysql_res):
sorted_dble_result = sorted(dble_res, key=str)
sorted_mysql_result = sorted(mysql_res, key=str)
assert sorted_dble_result == sorted_mysql_result, "dble and mysql resultSet not same, dbleResult: {0}, mysqlResultSet: {1}".format(
dble_res, mysql_res)
else:
assert False, "dble and mysql resultSet not same, dbleResultSet's length:{0}, mysqlResultSet's length:{1}".format(
len(dble_res), len(mysql_res))
context.logger.info(
"resultSets of all sql executed in dble and mysql are same")
def step_impl(context, host_name, mode_name):
for row in context.table:
execute_sql_in_host(host_name, row.as_dict(), mode_name)
def restore(self):
# if "restore_sys_time" in self._scenario.tags:
# utils.restore_sys_time()
#
# if "aft_reset_replication" in self._scenario.tags:
# utils.reset_repl()
#
if "restore_network" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_network'")
logger.debug("params_dic is: {0}".format(params_dic))
if params_dic:
paras = params_dic["restore_network"].split(",")
# paras = paras.split(",")
else:
paras = ""
logger.debug("try to restore_network: {0}".format(paras))
for host_name in paras:
logger.debug("the value of host_name is: {0}".format(host_name))
cmd = "iptables -F"
ssh = get_ssh(host_name)
rc, stdout, stderr = ssh.exec_command(cmd)
assert_that(len(stderr) == 0, "restore network with command:{1}, got err:{0}".format(stderr, cmd))
if "restore_view" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_view'")
if params_dic:
paras = params_dic["restore_view"]
else:
paras = {}
for host_name, mysql_vars in paras.items():
if host_name.find('dble') != -1:
mode = "user"
else:
mode = "mysql"
for k, v in mysql_vars.items():
list_value = filter(lambda x: x, v.split(","))
view_value = ""
for value in list_value:
view_value = view_value + "{0}.{1},".format(k, value)
query = "drop view if exists " + view_value
sql = query[:-1] # delete the last ','
# logger.debug("the sql is: {0}".format(sql))
execute_sql_in_host(host_name, {"sql": sql}, mode)
if "restore_mysql_service" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_service'")
if params_dic:
paras = params_dic["restore_mysql_service"]
else:
paras = {}
logger.debug("try to restore_mysql_service: {0}".format(paras))
for host_name, mysql_vars in paras.items():
logger.debug("the value of host_name is: {0}".format(host_name))
for k, v in mysql_vars.items():
if v:
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.start()
if "restore_global_setting" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_global_setting'")
if params_dic:
paras = params_dic["restore_global_setting"]
else:
paras = {}
logger.debug("try to restore_global_setting of mysqls: {0}".format(paras))
for mysql, mysql_vars in paras.items():
query = "set global "
for k, v in mysql_vars.items():
query = query + "{0}={1},".format(k, v)
sql = query[:-1] # delete the last ','
execute_sql_in_host(mysql, {"sql": sql})
if "restore_mysql_config" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_config'")
if params_dic:
paras = params_dic["restore_mysql_config"]
else:
paras = {}
logger.debug("try to restore_mysql_config of mysqls: {0}".format(paras))
for host_name, mysql_vars in paras.items():
sed_str = ""
for k, v in mysql_vars.items():
m = ['log-bin', 'binlog_format', 'relay-log']
if k in m:
sed_str += "/{0}/d\n".format(k)
else:
sed_str += "/{0}/d\n/server-id/a {0}={1}\n".format(k, v)
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.restart(sed_str)
if "restore_xa_recover" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_xa_recover'")
if params_dic:
paras = params_dic["restore_xa_recover"]
else:
paras = {}
logger.debug("try to restore_xa_recover of mysqls: {0}".format(paras))
for host_name in paras:
if host_name.find('dble') != -1:
mode = "user"
else:
mode = "mysql"
logger.debug("the value of host_name is: {0}, mode: {1}".format(host_name, mode))
sql = "xa recover"
res, err = execute_sql_in_host(host_name, {"sql": sql}, mode)
if res:
for data in res:
sql = "xa rollback '{0}'".format(data[3])
execute_sql_in_host(host_name, {"sql": sql}, mode)
if "delete_mysql_tables" in self._scenario.tags:
params_dic = self.get_tag_params("{'delete_mysql_tables'")
if params_dic:
paras = params_dic["delete_mysql_tables"]
else:
paras = {}
for host_name in paras.keys():
logger.debug("try to delete_mysql_tables of mysqls: {0}".format(host_name))
ssh = get_ssh(host_name)
if host_name.find("dble") != -1:
mode = "user"
else:
mode = "mysql"
logger.debug("the value of host_name is: {0}, mode: {1}".format(host_name, mode))
# in case of program exits unexpected, tables.txt Residue in /tmp
rc, stdout, stderr = ssh.exec_command("find /tmp -name tables.txt")
if len(stdout) > 0:
ssh.exec_command("rm -rf /tmp/tables.txt")
for database in paras[host_name]:
generate_drop_tables_sql = "select concat('drop table if exists ',table_name,';') from information_schema.TABLES where table_schema='{0}' into outfile '/tmp/tables.txt'".format(database)
execute_sql_in_host(host_name, {"sql": generate_drop_tables_sql}, mode)
# MySQLDB not support source grammar,replace with ssh cmd
rc, stdout, stderr = ssh.exec_command("mysql -uroot -p111111 -D{0} -e 'source /tmp/tables.txt'".format(database))
stderr = stderr.lower()
assert stderr.find("error") == -1, "deletet mysql in {0}:{1} failed, err: {2}".format(host_name, database, stderr)
ssh.exec_command("rm -rf /tmp/tables.txt")
logger.debug("{0} tables has been delete success".format(host_name))
logger.info("all required tables has been delete success")
def restore(self):
# if "restore_sys_time" in self._scenario.tags:
# utils.restore_sys_time()
#
# if "aft_reset_replication" in self._scenario.tags:
# utils.reset_repl()
#
if "restore_network" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_network'")
logger.debug("params_dic is: {0}".format(params_dic))
if params_dic:
paras = params_dic["restore_network"].split(",")
# paras = paras.split(",")
else:
paras = ""
logger.debug("try to restore_network: {0}".format(paras))
for host_name in paras:
logger.debug(
"the value of host_name is: {0}".format(host_name))
cmd = "iptables -F"
ssh = get_ssh(host_name)
rc, stdout, stderr = ssh.exec_command(cmd)
assert_that(
len(stderr) == 0,
"restore network with command:{1}, got err:{0}".format(
stderr, cmd))
if "restore_view" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_view'")
if params_dic:
paras = params_dic["restore_view"]
else:
paras = {}
for host_name, mysql_vars in paras.items():
if host_name.find('dble') != -1:
mode = "user"
else:
mode = "mysql"
for k, v in mysql_vars.items():
list_value = filter(lambda x: x, v.split(","))
view_value = ""
for value in list_value:
view_value = view_value + "{0}.{1},".format(k, value)
query = "drop view if exists " + view_value
sql = query[:-1] #delete the last ','
# logger.debug("the sql is: {0}".format(sql))
execute_sql_in_host(host_name, {"sql": sql}, mode)
if "restore_mysql_service" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_service'")
if params_dic:
paras = params_dic["restore_mysql_service"]
else:
paras = {}
logger.debug("try to restore_mysql_service: {0}".format(paras))
for host_name, mysql_vars in paras.items():
logger.debug(
"the value of host_name is: {0}".format(host_name))
for k, v in mysql_vars.items():
if v:
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.start()
if "restore_global_setting" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_global_setting'")
if params_dic:
paras = params_dic["restore_global_setting"]
else:
paras = {}
logger.debug(
"try to restore_global_setting of mysqls: {0}".format(paras))
for mysql, mysql_vars in paras.items():
query = "set global "
for k, v in mysql_vars.items():
query = query + "{0}={1},".format(k, v)
sql = query[:-1] #delete the last ','
execute_sql_in_host(mysql, {"sql": sql})
if "restore_mysql_config" in self._scenario.tags:
params_dic = self.get_tag_params("{'restore_mysql_config'")
if params_dic:
paras = params_dic["restore_mysql_config"]
else:
paras = {}
logger.debug(
"try to restore_mysql_config of mysqls: {0}".format(paras))
for host_name, mysql_vars in paras.items():
sed_str = ""
for k, v in mysql_vars.items():
sed_str += "/{0}/d\n/server-id/a {0}={1}\n".format(k, v)
mysql = ObjectFactory.create_mysql_object(host_name)
mysql.restart(sed_str)
