def __init__(self, user=None, id=None, schema=''):
# MysqlConfig的主键
self.id = id
self.local_user = user
self.schema = schema
self.local_schema = '_'.join(['query', str(id), self.schema])
self.local_password = LOCAL_QUERY_USER_PASSWORD
self.local_host = DATABASES.get('default').get('HOST')
self.local_port = DATABASES.get('default').get('PORT') if DATABASES.get('default').get('PORT') else 3306
def _local_cnx(self):
"""本地连接"""
user = DATABASES.get('default').get('USER')
host = DATABASES.get('default').get('HOST')
password = DATABASES.get('default').get('PASSWORD')
port = DATABASES.get('default').get('PORT') if DATABASES.get(
'default').get('PORT') else 3306
cnx = pymysql.connect(host=host,
user=user,
password=password,
port=port,
max_allowed_packet=1024 * 1024 * 1024,
charset='utf8',
client_flag=CLIENT.MULTI_STATEMENTS,
cursorclass=pymysql.cursors.DictCursor)
# 更改group_concat的默认长度(1024)，太短
with cnx.cursor() as cursor:
cursor.execute(
'set session group_concat_max_len=18446744073709551615;')
return cnx
def __init__(self,
user=None,
sqls=None,
host=None,
port=None,
schema=None):
self.user = user # request.user.username
self.sqls = sqls
# 远程主机信息
self.r_host = host
self.r_port = port if isinstance(port, int) else int(port)
self.r_schema = schema
self.queryset = MysqlConfig.objects.get(host=self.r_host,
port=self.r_port)
# 本地主机信息
self.local_host = DATABASES.get('default').get('HOST')
self.local_port = DATABASES.get('default').get(
'PORT') if DATABASES.get('default').get('PORT') else 3306
self.local_password = LOCAL_QUERY_USER_PASSWORD
self.local_schema = '_'.join(
['query', str(self.queryset.id), self.r_schema])
def periodic_sync_remote_schemameta_to_local():
"""
定时任务
同步sqlaudit_mysql_config表中只读数据库实例，在本地创建对应的库和表结构，不同步任何数据
"""
remote_host = []
# 排除django系统所在的库，避免采集数据的死循环
for row in MysqlConfig.objects.filter(type=1).exclude(
host=DATABASES.get('default').get('HOST')):
remote_host.append({
'id': row.id,
'user': row.user,
'password': row.password,
'host': row.host,
'port': row.port,
'envi_id': row.envi_id,
'comment': row.comment
})
op = SyncMysqlRemoteMeta(remote_host)
op.run()