def binlog2sql_file(args, user):
"""
用于异步保存binlog解析的文件
:param args: 参数
:param user: 操作用户对象，用户消息推送
:return:
"""
binlog2sql = Binlog2Sql()
instance = args.get('instance')
conn_options = fr"-h{instance.host} -u{instance.user} -p'{instance.password}' -P{instance.port}"
args['conn_options'] = conn_options
timestamp = int(time.time())
path = os.path.join(settings.BASE_DIR, 'downloads/binlog2sql/')
os.makedirs(path, exist_ok=True)
if args.get('flashback'):
filename = os.path.join(
path, f"flashback_{instance.host}_{instance.port}_{timestamp}.sql")
else:
filename = os.path.join(
path, f"{instance.host}_{instance.port}_{timestamp}.sql")
# 参数转换
cmd_args = binlog2sql.generate_args2cmd(args, shell=True)
# 执行命令保存到文件
with open(filename, 'w') as f:
p = binlog2sql.execute_cmd(cmd_args, shell=True)
for c in iter(p.stdout.readline, ''):
f.write(c)
return user, filename
def test_binlog2ql_generate_args2cmd(self):
"""
测试binlog2sql参数转换
:return:
"""
args = {
'conn_options': "-hmysql -uroot -p'123456' -P3306 ",
'stop_never': False,
'no-primary-key': False,
'flashback': True,
'back-interval': 0,
'start-file': 'mysql-bin.000043',
'start-position': 111,
'stop-file': '',
'stop-position': '',
'start-datetime': '',
'stop-datetime': '',
'databases': 'account_center',
'tables': 'ac_apps',
'only-dml': True,
'sql-type': 'UPDATE'
}
self.sys_config.set(
'binlog2sql', '/opt/archery/src/plugins/binlog2sql/binlog2sql.py')
self.sys_config.get_all_config()
binlog2sql = Binlog2Sql()
cmd_args = binlog2sql.generate_args2cmd(args, False)
self.assertIsInstance(cmd_args, list)
cmd_args = binlog2sql.generate_args2cmd(args, True)
self.assertIsInstance(cmd_args, str)
def binlog2sql(request):
"""
通过解析binlog获取SQL
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
save_sql = True if request.POST.get('save_sql') == 'true' else False
instance = Instance.objects.get(instance_name=instance_name)
no_pk = True if request.POST.get('no_pk') == 'true' else False
flashback = True if request.POST.get('flashback') == 'true' else False
back_interval = 0 if request.POST.get('back_interval') == '' else int(
request.POST.get('back_interval'))
num = 30 if request.POST.get('num') == '' else int(request.POST.get('num'))
start_file = request.POST.get('start_file')
start_pos = request.POST.get('start_pos') if request.POST.get(
'start_pos') == '' else int(request.POST.get('start_pos'))
end_file = request.POST.get('end_file')
end_pos = request.POST.get('end_pos') if request.POST.get(
'end_pos') == '' else int(request.POST.get('end_pos'))
stop_time = request.POST.get('stop_time')
start_time = request.POST.get('start_time')
only_schemas = request.POST.getlist('only_schemas')
only_tables = request.POST.getlist('only_tables[]')
only_dml = True if request.POST.get('only_dml') == 'true' else False
sql_type = ['INSERT', 'UPDATE', 'DELETE'] if request.POST.getlist(
'sql_type[]') == [] else request.POST.getlist('sql_type[]')
# flashback=True获取DML回滚语句
result = {'status': 0, 'msg': 'ok', 'data': ''}
# 提交给binlog2sql进行解析
binlog2sql = Binlog2Sql()
# 准备参数
args = {
"conn_options":
fr"-h{instance.host} -u{instance.user} -p'{instance.password}' -P{instance.port} ",
"stop_never": False,
"no-primary-key": no_pk,
"flashback": flashback,
"back-interval": back_interval,
"start-file": start_file,
"start-position": start_pos,
"stop-file": end_file,
"stop-position": end_pos,
"start-datetime": start_time,
"stop-datetime": stop_time,
"databases": ' '.join(only_schemas),
"tables": ' '.join(only_tables),
"only-dml": only_dml,
"sql-type": ' '.join(sql_type),
"instance": instance
}
# 参数检查
args_check_result = binlog2sql.check_args(args)
if args_check_result['status'] == 1:
return HttpResponse(json.dumps(args_check_result),
content_type='application/json')
# 参数转换
cmd_args = binlog2sql.generate_args2cmd(args, shell=True)
# 执行命令
try:
p = binlog2sql.execute_cmd(cmd_args, shell=True)
# 读取前num行后结束
rows = []
n = 1
for line in iter(p.stdout.readline, ''):
if n <= num:
n = n + 1
row_info = {}
try:
row_info['sql'] = line.split('; #')[0] + ";"
row_info['binlog_info'] = line.split('; #')[1].rstrip('\"')
except IndexError:
row_info['sql'] = line
row_info['binlog_info'] = None
rows.append(row_info)
else:
break
if rows.__len__() == 0:
# 判断是否有异常
stderr = p.stderr.read()
if stderr:
result['status'] = 1
result['msg'] = stderr
return HttpResponse(json.dumps(result),
content_type='application/json')
# 终止子进程
p.kill()
result['data'] = rows
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(e)
# 异步保存到文件
if save_sql:
args.pop('conn_options')
async_task(binlog2sql_file,
args=args,
user=request.user,
hook=notify_for_binlog2sql,
timeout=-1,
task_name=f'binlog2sql-{time.time()}')
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
