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)