def user_confirm(self):
info = """
将生成在服务器{mysql_host}上归档数据
迁移数据条件为:
INSERT INTO `{target_database_name}`.`{target_table_name}`
SELECT * FROM `{source_database_name}`.`{source_table_name}`
WHERE {data_condition}
"""
info = info.format(mysql_host=self.source_mysql_server.mysql_host,
source_database_name=self.source_database_name,
source_table_name=self.source_table_name,
target_database_name=self.target_database_name,
target_table_name=self.target_table_name,
data_condition=self.data_condition)
PrintHelper.print_info_message(info)
if self.is_dry_run:
PrintHelper.print_info_message("模拟运行。。。")
return True
if not self.is_user_confirm:
return True
input_options = ['yes', 'no']
input_message = """
请输入yes继续或输入no退出，yes/no?
"""
user_option = self.get_user_choose_option(input_options=input_options,
input_message=input_message)
if user_option == "no":
return False
else:
return True
def create_target_table(self):
if not self.is_target_table_exist():
PrintHelper.print_info_message("准备创建目标表")
table_script = self.get_target_table_create_script()
self.target_mysql_server.mysql_exec(sql_script=table_script)
else:
PrintHelper.print_info_message("目标表已存在")
def create_target_table(self):
sql_script = "CREATE TABLE IF NOT EXISTS `{target_database_name}`.`{target_table_name}` LIKE `{source_database_name}`.`{source_table_name}`;".format(
target_database_name=self.target_database_name,
source_database_name=self.source_database_name,
target_table_name=self.target_table_name,
source_table_name=self.source_table_name)
PrintHelper.print_info_message("建表脚本为：\n {0}".format(sql_script))
self.source_mysql_server.mysql_exec(sql_script=sql_script)
def get_user_choose_option(self, input_options, input_message):
while True:
PrintHelper.print_info_message(input_message)
str_input = input("")
for input_option in input_options:
if str_input.strip() == input_option:
choose_option = input_option
return choose_option
def archive_range_data(self, current_key, next_key):
try:
if self.is_dry_run:
PrintHelper.print_info_message("未真实执行，未生产SQL文件。")
return True
range_keys = self.get_archive_range_keys(current_key, next_key)
range_data = self.get_data_by_keys(range_keys=range_keys)
PrintHelper.print_info_message("找到满足条件记录{}条".format(
len(range_data)))
PrintHelper.print_info_message("开始删除目标库已有数据")
self.delete_target_data_by_keys(range_keys=range_keys)
PrintHelper.print_info_message("开始向目标库上插入数据")
self.insert_target_range_data(range_data=range_data)
PrintHelper.print_info_message("开始删除目标库归档数据")
self.delete_source_data_by_keys(range_keys=range_keys)
return True
except Exception as ex:
PrintHelper.print_warning_message("在归档过程中出现异常：{}\n堆栈：{}\n".format(
str(ex), traceback.format_exc()))
return False
def delete_data_by_scripts(self, transfer_scripts):
try:
sql_script_list = []
delete_script = transfer_scripts["delete_script"]
temp_script = delete_script, None
sql_script_list.append(temp_script)
sql_script = delete_script
tmp_script = """
USE {0};
""".format(self.source_database_name) + sql_script + """
COMMIT;
SELECT SLEEP('{0}');
##=====================================================##
""".format(self.batch_sleep_seconds)
PrintHelper.write_file(file_path=self.transfer_script_file,
message=tmp_script)
if not self.is_dry_run:
total_affect_rows = self.source_mysql_server.mysql_exec_many(
sql_script_list)
PrintHelper.print_info_message(
"本次归档操作记录{}条".format(total_affect_rows))
self.sleep_with_affect_rows(total_affect_rows)
else:
PrintHelper.print_info_message("生成迁移脚本(未执行)")
PrintHelper.print_info_message(sql_script)
return True
except Exception as ex:
PrintHelper.print_warning_message("在归档过程中出现异常：{}\n堆栈：{}\n".format(
str(ex), traceback.format_exc()))
return False
def archive_data(self):
PrintHelper.print_info_message("开始检查归档配置")
if not self.check_config():
return
confirm_result = self.user_confirm()
if not confirm_result:
return
PrintHelper.print_info_message("开始归档数据")
self.loop_archive_data()
PrintHelper.print_info_message("结束归档数据")
def loop_archive_data(self):
max_key, min_key = self.get_loop_key_range()
if max_key is None or min_key is None:
PrintHelper.print_info_message("未找到满足条件的键区间")
return
current_key = min_key
while current_key < max_key:
if self.has_stop_file():
break
PrintHelper.print_info_message("*" * 70)
next_key = self.get_next_loop_key(current_key=current_key)
if next_key is None:
PrintHelper.print_info_message("未找到下一个可归档的区间，退出归档")
break
if not self.archive_range_data(current_key=current_key,
next_key=next_key):
PrintHelper.print_info_message("执行出现异常，退出归档！")
break
current_key = next_key
PrintHelper.print_info_message("*" * 70)
info = """最小值为：{0},最大值为：{1},当前处理值为：{2}""".format(
min_key, max_key, current_key)
PrintHelper.print_info_message(info)
PrintHelper.print_info_message("*" * 70)
PrintHelper.print_info_message("执行完成")
def has_stop_file(self):
if os.path.exists(self.stop_script_file):
PrintHelper.print_info_message("检查到停止文件，准备退出！")
return True
else:
return False