def initdb(self):
self.houseadDb = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MOBGI_HOUSEAD['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
self.mobgiCharge = MySQL(config.MOBGI_CHARGE['host'],
config.MOBGI_CHARGE['user'],
config.MOBGI_CHARGE['passwd'],
port=config.MOBGI_CHARGE['port'],
db=config.MOBGI_CHARGE['db'])
return self.houseadDb, self.mobgiCharge
def mysql_update_instance(self):
if self.arch == '主从':
rs = self._conn.getSlaveIP()
conn_slave = MySQL(ip=rs[1],
port=self.port,
user=self.user,
passwd=self.passwd)
rs2 = conn_slave.getReplStatus()
myinstance = self._cluster.mysql_instance
if rs2[3] == self.port: #代表主从是一致的
slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs[1],
port=self.port)
slave[0].master_ip = rs2[1]
slave[0].master_port = rs2[3]
slave[0].role = u'从库'
# slave[0].db_status=u'仅从库'
slave[0].save()
master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs2[1],
port=self.port)
master[0].vist_ip = self.ip
master[0].role = u'主库'
master[0].db_status = u'服务中'
master[0].save()
return True
def initdb(self):
self.db = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MYSQL['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
return self.db
def post(self, request, format=None):
s = MysqlClusterSerializer(data=request.data)
foreign_ip = request.data['foreign_ip']
foreign_port = request.data['foreign_port']
plat_user = request.data['plat_user']
plat_user_passwd = request.data['plat_user_pass']
msg = ""
if s.is_valid(): # 验证
try:
testconn = MySQL(ip=foreign_ip,
port=foreign_port,
user=plat_user,
passwd=plat_user_passwd)
testconn.close()
msg = '数据库连接成功'
except Exception as e:
print(e)
json_data = {'code': 500, 'msg': '数据添加失败，测试连接失败'}
return Response(json_data, content_type="application/json")
s.save() #连接成功后保存数据
json_data = {'code': 200, 'msg': '数据保存成功,' + msg}
json_data['data'] = s.data
return Response(json_data, content_type="application/json")
# return api_response.JsonResponse(s.data,code=status.HTTP_200_OK,msg='success')
json_data = {'code': 500, 'msg': '数据添加失败，请检查数据格式'}
return Response(json_data, content_type="application/json")
def __init__(self, clusterobj, dbname=''):
self.ip = clusterobj.foreign_ip
self.user = clusterobj.plat_user
self.passwd = clusterobj.plat_user_pass
self.db = dbname
self.port = int(clusterobj.foreign_port)
self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd)
def put(self, request, format=None):
try:
data_id = request.data['id']
foreign_ip = request.data['foreign_ip']
foreign_port = request.data['foreign_port']
plat_user = request.data['plat_user']
plat_user_passwd = request.data['plat_user_pass']
except Exception as e:
print(e)
json_data = {'code': 500, 'msg': '数据有错误获取不到id'}
return Response(json_data,status=500)
else:
DATA_MODEL = self.get_object(data_id)
s = MysqlClusterSerializer(DATA_MODEL, data=request.data)
if s.is_valid(raise_exception=True):
try:
testconn = MySQL(ip=foreign_ip, port=foreign_port, user=plat_user, passwd=plat_user_passwd)
testconn.close()
msg = '数据库连接成功'
s.save()
json_data = {'code': 200, 'msg': '更新成功'}
return Response(json_data)
except Exception as e:
json_data = {'code': 500, 'msg': '更新失败，新的用户名密码或者网络不通过'}
return Response(json_data)
json_data = {'code': 500, 'msg': '更新失败,数据格式和内部表不一致'}
return Response(json_data, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
def main():
"""
Youtube Main Crawler. Expected to run once a day
"""
api_list = [
os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
os.environ['API_4'], os.environ['API_5']
]
yc = YoutubeCrawler(api_list, processes=50)
conn = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
conn_02 = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
upsert_cd = 'INSERT INTO YOUTUBE.t_ch_desc \
(ch_title, ch_id, ch_desc, ch_published_at, ch_thumb) \
VALUES (%(title)s, %(ch_id)s, %(description)s, %(publishedAt)s, %(ch_thumb)s) \
ON DUPLICATE KEY UPDATE ch_title=%(title)s, ch_desc=%(description)s, ch_published_at=%(publishedAt)s, ch_thumb=%(ch_thumb)s'
upsert_cc = 'INSERT INTO YOUTUBE.t_ch_cstats \
(ch_id, ch_n_sub, ch_n_view, ch_n_video, ch_n_cmt) \
VALUES (%(ch_id)s, %(subscriberCount)s, %(viewCount)s, %(videoCount)s, %(comment_count)s) \
ON DUPLICATE KEY UPDATE ch_n_sub=%(subscriberCount)s, ch_n_view=%(viewCount)s, ch_n_video=%(videoCount)s, ch_n_cmt=%(comment_count)s'
insert_vid_desc = 'INSERT IGNORE INTO YOUTUBE.t_ch_vid_desc \
(ch_id, upload_id, vid_id, vid_title, vid_desc, vid_published_at, vid_th) \
VALUES (%(ch_id)s, %(upload_id)s, %(vid_id)s, %(vid_title)s, %(vid_desc)s, %(vid_published_at)s, %(vid_th)s) \
'
check = update_check()
youtube_main_crawler(**check)
def __init__(self, clusterid):
self._cluster = MySQLCluster.objects.get(id=clusterid)
self.cluster_id = clusterid
self.ip = self._cluster.foreign_ip
self.port = self._cluster.foreign_port
self.user = self._cluster.plat_user
self.arch = self._cluster.arch
self.passwd = self._cluster.plat_user_pass
self._conn = MySQL(ip=self.ip,
port=self.port,
user=self.user,
passwd=self.passwd)
def trysql(self, sql):
try:
self.mylog("select sql:" + sql)
self.clickdb.queryNotCatch(sql)
except MySQLdb.Error as m:
self.mylog('masterdb reconnecting now')
self.clickdb = MySQL(self.host,
self.user,
self.passwd,
port=self.port,
db=self.db)
self.clickdb.query(sql)
return self.clickdb.fetchAll()
def __get_mysql():
"""
加载MySQL数据库连接池
"""
if services.mysql is None:
services.mysql = dict()
with open('%s/mysql.json' % account_name, 'r') as f:
mysql_account = json.load(f)
for name, info in mysql_account.items():
try:
services.mysql[name] = MySQL(**info)
except mysql_repetitive:
services.logger.exception('MySQL数据库（%s）重复连接！' % name)
def MyfastSQL_result(request):
if request.method=='GET':
id = request.GET['id']
Myfastsql = MysqlFastSQL.objects.get(id=id)
sql = request.GET['sql']
cluster = MySQLCluster.objects.get(id=request.GET['mysqlcluster_id'])
if Myfastsql.exec_posi == u'从库':
pass
else:
try:
ip = cluster.foreign_ip
port = cluster.foreign_port
user = cluster.plat_user
arch = cluster.arch
passwd = cluster.plat_user_pass
conn = MySQL(ip=ip, port=port, user=user, passwd=passwd)
count, datalist, colName = conn.execute(sql=sql)
return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
except Exception as e:
colName=[u'报错']
datalist=[[u'请检查sql语句的错误，查不出来']]
return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
def mysql_update_instance(self):
if self.arch == '主从':
rs = self._conn.getSlaveIP()
conn_slave = MySQL(ip=rs[1],
port=self.port,
user=self.user,
passwd=self.passwd)
slavestatus = conn_slave.getReplStatus() #嗲用的是show slave status
myinstance = self._cluster.mysql_instance
if slavestatus[3] == self.port: #代表主从是一致的
slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs[1],
port=self.port)
slave[0].master_ip = slavestatus[1]
slave[0].master_port = slavestatus[3]
slave[0].role = u'从库'
# slave[0].db_status=u'仅从库'
slave[0].save()
master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=slavestatus[1],
port=self.port)
master[0].vist_ip = self.ip
master[0].role = u'主库'
master[0].db_status = u'服务中'
master[0].save()
else:
print(u'主从添加实例不对阿')
elif self.arch == u'单库':
myinstance = self._cluster.mysql_instance
single = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=self.ip,
port=self.port)
single[0].vist_ip = self.ip
single[0].role = u'单库'
single[0].db_status = u'服务中'
single[0].save()
elif self.arch == u'innodb_cluster':
count, result, columns = self._conn.getInnoClusterInstance()
myinstance = self._cluster.mysql_instance
masterip = ''
master_port = ''
for i in range(count):
ip = result[i][2].split(':')[0]
port = result[i][1]
role_0 = result[i][3]
db_status = result[i][4]
clusertI = myinstance.get_or_create(
dbcluster_id=self.cluster_id, m_ip=ip, port=port)
clusertI[0].vist_ip = ip
if role_0 == u'PRIMARY':
clusertI[0].role = u'主库'
masterip = ip
master_port = port
else:
clusertI[0].role = u'从库'
clusertI[0].master_ip = masterip
clusertI[0].master_port = master_port
if db_status == u'ONLINE':
clusertI[0].db_status = u'服务中'
else:
clusertI[0].db_status = u'故障'
clusertI[0].save()
return True
def __init__(self):
# Selenium headless
self.crawl = Crawl(delay=20, telegram_notify=False, gui=False)
self.mysql = MySQL()
import os
from utils.mysql import MySQL
from YoutubeData.YoutubeCrawler import YoutubeCrawler
api_list = [
os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
os.environ['API_4'], os.environ['API_5']
]
conn = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
def th_url_join(video_list, thumb, t):
for video in video_list:
th_list = []
for key in video[thumb].keys():
th_list.append(video[thumb][key]['url'])
video[thumb] = ', '.join(th_list)
video[t] = video[t][:-1]
return video_list
#!coding=utf8
import time
import io
import utils.datetime as datetime
from utils.mysql import MySQL
pds = []
dns_db = MySQL(host="127.0.0.1",
user="******",
passwd="rootofmysql",
port=3307,
db="IPCIS_DNS_DB")
def read_data():
# 从文件中读取恶意域名信息
with io.open("../data/3mths.txt", "r", encoding="latin1") as f:
for line in f.readlines():
temp = line.strip().split("=")
temp[0] = temp[0].strip()
pds.append(temp[0])
def create_db():
sql = (
"CREATE TABLE IF NOT EXISTS domain_static ( domain_id INT(10) UNSIGNED NOT NULL DEFAULT 0,"
"primary_domain VARCHAR(128) NOT NULL DEFAULT '', "
"is_dga INT(10) UNSIGNED NOT NULL, "
"ttl INT(10) UNSIGNED NOT NULL, "
"credit INT(10) UNSIGNED, "
