def ajax_mysql_is_alived(request):
"""ajax 的方式远程执行mysqladmin ping
从而获得MySQL是否存活
"""
is_alived = False
if request.method == 'POST':
# 获得传来的MySQL连接参数 和 所在的操作系统ID
os_id = int(request.POST.get('os_id', '0'))
mysql_host = request.POST.get('mysql_host', '')
mysql_port = int(request.POST.get('mysql_port', '0'))
mysql_user = request.POST.get('mysql_user', '')
mysql_password = request.POST.get('mysql_password', '')
mysql_base_dir = request.POST.get('mysql_base_dir', '')
# 记录日志
info_msg = 'host: {host}, port: {port}'.format(host=mysql_host,
port=mysql_port)
logger.info(info_msg)
if (os_id):
try:
cmdb_os = CmdbOs.objects.get(os_id=os_id)
except CmdbOs.DoesNotExist:
logger.error(traceback.format_exc())
if (cmdb_os and mysql_host and mysql_port and mysql_user
and mysql_password and mysql_base_dir):
# 获得操作系统
mysqladmin = '{dir}/bin/mysqladmin'.format(dir=mysql_base_dir)
# 执行MySQL ping 命令
is_alived = MysqlAdminTool.mysql_is_alived(
mysqladmin=mysqladmin,
mysql_user=mysql_user,
mysql_password=mysql_password,
mysql_host=mysql_host,
mysql_port=mysql_port,
os_ip=IpTool.num2ip(cmdb_os.ip),
os_user=cmdb_os.username,
os_password=cmdb_os.password)
respons_data = json.dumps(is_alived)
return HttpResponse(respons_data, content_type='application/json')
def ajax_mysql_is_alived(request):
"""ajax 的方式远程执行mysqladmin ping
从而获得MySQL是否存活
"""
is_alived = False
if request.method == 'POST':
# 获得传来的MySQL连接参数 和 所在的操作系统ID
os_id = int(request.POST.get('os_id', '0'))
mysql_host = request.POST.get('mysql_host', '')
mysql_port = int(request.POST.get('mysql_port', '0'))
mysql_user = request.POST.get('mysql_user', '')
mysql_password = request.POST.get('mysql_password', '')
mysql_base_dir = request.POST.get('mysql_base_dir', '')
# 记录日志
info_msg = 'host: {host}, port: {port}'.format(
host = mysql_host,
port = mysql_port)
logger.info(info_msg)
if(os_id):
try:
cmdb_os = CmdbOs.objects.get(os_id = os_id)
except CmdbOs.DoesNotExist:
logger.error(traceback.format_exc())
if (cmdb_os and mysql_host and mysql_port and mysql_user
and mysql_password and mysql_base_dir):
# 获得操作系统
mysqladmin = '{dir}/bin/mysqladmin'.format(dir = mysql_base_dir)
# 执行MySQL ping 命令
is_alived = MysqlAdminTool.mysql_is_alived(mysqladmin = mysqladmin,
mysql_user = mysql_user,
mysql_password = mysql_password,
mysql_host = mysql_host,
mysql_port = mysql_port,
os_ip = IpTool.num2ip(cmdb_os.ip),
os_user = cmdb_os.username,
os_password = cmdb_os.password)
respons_data = json.dumps(is_alived)
return HttpResponse(respons_data, content_type='application/json')
def f_ip2num(ip):
return IpTool.ip2num(ip)
def f_num2ip(num):
return IpTool.num2ip(num)
def ajax_sync_database(request):
"""ajax 同步更新当前实例最新数据库列表
Return:
is_ok 0/1/2
0: 获取信息出错
1: 同步成功
2: 不用同步
"""
is_ok = 0
if request.method == 'POST':
mysql_instance_id = int(request.POST.get('mysql_instance_id', '0'))
if not mysql_instance_id: # 没有传入有效ID返回失败
logger.info(traceback.format_exc())
logger.info(
'传入DbmpMysqlInstance ID 无效: {id}'.format(id=mysql_instance_id))
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
try:
# 1.获取MySQL实例
dbmp_mysql_instance = DbmpMysqlInstance.objects.values(
'mysql_instance_id',
'username',
'password',
'port',
'host',
).get(mysql_instance_id=mysql_instance_id)
except DbmpMysqlInstance.DoesNotExist: # 没有获取到实例信息则转跳列表页面
logger.info(traceback.format_exc())
logger.info(
'获取DbmpMysqlInstance实例失败: {id}'.format(id=mysql_instance_id))
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
try:
dbmp_mysql_databases = DbmpMysqlDatabase.objects.values(
'name').filter(mysql_instance_id=mysql_instance_id)
database_names_old = set(
[item.get('name', '') for item in dbmp_mysql_databases])
except: # 没有获取到实例信息则转跳列表页面
logger.info(traceback.format_exc())
logger.info('获取DbmpMysqlDatabase失败')
database_names_old = set([])
database_names_real = MysqlAdminTool.get_database_names(
user=dbmp_mysql_instance.get('username', ''),
passwd=dbmp_mysql_instance.get('password', ''),
host=IpTool.num2ip(dbmp_mysql_instance.get('host', '')),
port=dbmp_mysql_instance.get('port', 3306))
# 获得缺少的数据库名
database_names_lack = list(database_names_real - database_names_old)
# 构造 list 对象
dbmp_mysql_database_list = [
DbmpMysqlDatabase(mysql_instance_id=mysql_instance_id, name=name)
for name in database_names_lack
]
# 获得多余过期的数据库名
database_names_redundant = list(database_names_old -
database_names_real)
try:
with transaction.atomic():
# 删除数据多余过期数据
if database_names_redundant:
DbmpMysqlDatabase.objects.filter(
mysql_instance_id=mysql_instance_id).filter(
name__in=database_names_redundant).delete()
is_ok = 1
# 插入缺少的数据库名
if dbmp_mysql_database_list:
DbmpMysqlDatabase.objects.bulk_create(
dbmp_mysql_database_list)
is_ok = 1
# 不需要更新
if not database_names_redundant and not dbmp_mysql_database_list:
is_ok = 2
except IntegrityError, e:
logger.info(traceback.format_exc())
logger.info('添加失败')
if e.args[0] == 1062:
logger.info('添加失败, 需要添加的相关信息重复')
except Exception, e:
logger.info(traceback.format_exc())
logger.info('保存失败')
logger.info('添加或删除数据库数据失败')
#################################################################
try:
cmdb_os = CmdbOs.objects.values(
'os_id', 'ip', 'username',
'password').get(os_id=dbmp_mysql_instance.get('os_id', 0))
except Exception, e:
logger.error(traceback.format_exc())
logger.error('未找到相关的操作系统(OS)信息')
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
#################################################################
# 获取 当前操作系统存在的 mysqld pid, 通过ps -ef 获得
#################################################################
is_ok, exist_pids, errs = MysqlAdminTool.mysqld_pids(
os_ip=IpTool.num2ip(cmdb_os.get('ip', '')),
os_user=cmdb_os.get('username', ''),
os_password=cmdb_os.get('password', ''))
if not is_ok:
logger.error('获取 当前操作系统存在的 mysqld pid命令错误')
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
#################################################################
# 执行启动MySQL并获得返回的pid并且将这些pid保存到DbmpMysqlInstance中
#################################################################
is_ok, start_pids, errs = MysqlAdminTool.start_mysql_and_pids(
cmd=dbmp_mysql_instance_info.get('start_cmd', ''),
os_ip=IpTool.num2ip(cmdb_os.get('ip', '')),
os_user=cmdb_os.get('username', ''),
os_password=cmdb_os.get('password', ''))
def f_ip2num(ip):
return IpTool.ip2num(ip)
cmdb_os = CmdbOs.objects.values('os_id',
'ip',
'username',
'password').get(
os_id = dbmp_mysql_instance.get('os_id', 0))
except Exception, e:
logger.error(traceback.format_exc())
logger.error('未找到相关的操作系统(OS)信息')
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
#################################################################
# 获取 当前操作系统存在的 mysqld pid, 通过ps -ef 获得
#################################################################
is_ok, exist_pids, errs = MysqlAdminTool.mysqld_pids(
os_ip = IpTool.num2ip(cmdb_os.get('ip', '')),
os_user = cmdb_os.get('username', ''),
os_password = cmdb_os.get('password', ''))
if not is_ok:
logger.error('获取 当前操作系统存在的 mysqld pid命令错误')
respons_data = json.dumps(is_ok)
return HttpResponse(respons_data, content_type='application/json')
#################################################################
# 执行启动MySQL并获得返回的pid并且将这些pid保存到DbmpMysqlInstance中
#################################################################
is_ok, start_pids, errs = MysqlAdminTool.start_mysql_and_pids(
cmd = dbmp_mysql_instance_info.get('start_cmd', ''),
os_ip = IpTool.num2ip(cmdb_os.get('ip', '')),
os_user = cmdb_os.get('username', ''),
os_password = cmdb_os.get('password', ''))
def f_num2ip(num):
return IpTool.num2ip(num)
def ajax_check(request):
"""对SQL进行审核"""
params = {}
params['is_ok'] = False
if request.method == 'POST':
try:
mysql_database_id = int(request.POST.get('mysql_database_id', '0'))
inception_instance_id = int(request.POST.get('inception_instance_id', '0'))
sql_text = request.POST.get('sql_text', '')
charset = request.POST.get('charset', 'utf8mb4')
# 如果获取的数据有问题直接返回失败
if not mysql_database_id or not inception_instance_id or not sql_text:
logger.error('SQL审核传入参数不全')
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
try:
# 获取数据库信息
dbmp_mysql_database = DbmpMysqlDatabase.objects.values(
'mysql_database_id',
'mysql_instance_id',
'name').get(
mysql_database_id = mysql_database_id)
except DbmpMysqlDatabase.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关数据库')
params['inception_info'] = '对不起! 找不到相关数据库'
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
try:
# 数据库实例信息
dbmp_mysql_instance = DbmpMysqlInstance.objects.values(
'username',
'password',
'host',
'port').get(
mysql_instance_id = dbmp_mysql_database.get('mysql_instance_id', 0))
except DbmpMysqlInstance.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关实例信息')
params['inception_info'] = '对不起! 找不到相关实例信息'
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
try:
# Inception实例信息
dbmp_inception_instance = DbmpInceptionInstance.objects.values(
'host',
'port').get(
inception_instance_id = inception_instance_id)
except DbmpInceptionInstance.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关Inception实例')
params['inception_info'] = '对不起! 找不到相关Inception实例'
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
# 对SQL进行审核
is_ok, inception_info = InceptionTool.inception_check_only(
inception_host = IpTool.num2ip(dbmp_inception_instance.get('host', 0)),
inception_port = dbmp_inception_instance.get('port', 0),
mysql_user = dbmp_mysql_instance.get('username', ''),
mysql_password = dbmp_mysql_instance.get('password', ''),
mysql_host = IpTool.num2ip(dbmp_mysql_instance.get('host', 0)),
mysql_port = dbmp_mysql_instance.get('port', 0),
db_name = dbmp_mysql_database.get('name', ''),
sql_text = sql_text,
charset = charset)
params['is_ok'] = is_ok
params['inception_info'] = inception_info
except:
logger.error(traceback.format_exc())
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
def ajax_check(request):
"""对SQL进行审核"""
params = {}
params['is_ok'] = False
if request.method == 'POST':
try:
mysql_database_id = int(request.POST.get('mysql_database_id', '0'))
inception_instance_id = int(
request.POST.get('inception_instance_id', '0'))
sql_text = request.POST.get('sql_text', '')
charset = request.POST.get('charset', 'utf8mb4')
# 如果获取的数据有问题直接返回失败
if not mysql_database_id or not inception_instance_id or not sql_text:
logger.error('SQL审核传入参数不全')
respons_data = json.dumps(params)
return HttpResponse(respons_data,
content_type='application/json')
try:
# 获取数据库信息
dbmp_mysql_database = DbmpMysqlDatabase.objects.values(
'mysql_database_id', 'mysql_instance_id',
'name').get(mysql_database_id=mysql_database_id)
except DbmpMysqlDatabase.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关数据库')
params['inception_info'] = '对不起! 找不到相关数据库'
respons_data = json.dumps(params)
return HttpResponse(respons_data,
content_type='application/json')
try:
# 数据库实例信息
dbmp_mysql_instance = DbmpMysqlInstance.objects.values(
'username', 'password', 'host',
'port').get(mysql_instance_id=dbmp_mysql_database.get(
'mysql_instance_id', 0))
except DbmpMysqlInstance.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关实例信息')
params['inception_info'] = '对不起! 找不到相关实例信息'
respons_data = json.dumps(params)
return HttpResponse(respons_data,
content_type='application/json')
try:
# Inception实例信息
dbmp_inception_instance = DbmpInceptionInstance.objects.values(
'host',
'port').get(inception_instance_id=inception_instance_id)
except DbmpInceptionInstance.DoesNotExist:
logger.error(traceback.format_exc())
logger.error('对不起! 找不到相关Inception实例')
params['inception_info'] = '对不起! 找不到相关Inception实例'
respons_data = json.dumps(params)
return HttpResponse(respons_data,
content_type='application/json')
# 对SQL进行审核
is_ok, inception_info = InceptionTool.inception_check_only(
inception_host=IpTool.num2ip(
dbmp_inception_instance.get('host', 0)),
inception_port=dbmp_inception_instance.get('port', 0),
mysql_user=dbmp_mysql_instance.get('username', ''),
mysql_password=dbmp_mysql_instance.get('password', ''),
mysql_host=IpTool.num2ip(dbmp_mysql_instance.get('host', 0)),
mysql_port=dbmp_mysql_instance.get('port', 0),
db_name=dbmp_mysql_database.get('name', ''),
sql_text=sql_text,
charset=charset)
params['is_ok'] = is_ok
params['inception_info'] = inception_info
except:
logger.error(traceback.format_exc())
respons_data = json.dumps(params)
return HttpResponse(respons_data, content_type='application/json')
def add(request):
params = {}
####################################################################
# GET 请求
####################################################################
if request.method == 'GET':
form = AddForm()
params['form'] = form
return render(request, 'dbmp_mysql_instance/add.html', params)
####################################################################
# POST 请求
####################################################################
if request.method == 'POST':
form = AddForm(request.POST)
params['form'] = form
# 验证表单
if form.is_valid():
os_id = form.cleaned_data['os_id']
host = form.cleaned_data['host']
port = form.cleaned_data['port']
username = form.cleaned_data['username']
password = form.cleaned_data['password']
remark = form.cleaned_data['remark']
my_cnf_path = form.cleaned_data['my_cnf_path']
base_dir = form.cleaned_data['base_dir']
start_cmd = form.cleaned_data['start_cmd']
host = IpTool.ip2num(host)
run_status = 3 # 未知
try:
with transaction.atomic():
# 更新DbmpMysqlInstance
dbmp_mysql_instance = DbmpMysqlInstance(
os_id=os_id,
host=host,
port=port,
username=username,
password=password,
run_status=run_status,
remark=remark)
dbmp_mysql_instance.save()
# 插入数据 dbmp_mysql_instance_info
DbmpMysqlInstanceInfo.objects.create(
mysql_instance_id=dbmp_mysql_instance.
mysql_instance_id,
my_cnf_path=my_cnf_path,
base_dir=base_dir,
start_cmd=start_cmd)
# 保存成功转跳到View页面
request.session['success_msg'].append('添加成功')
view_url = '{base_url}/view/?mysql_instance_id={id}'.format(
base_url=ViewUrlPath.path_dbmp_mysql_instance(),
id=dbmp_mysql_instance.mysql_instance_id)
return HttpResponseRedirect(view_url)
except IntegrityError, e:
logger.info(traceback.format_exc())
request.session['danger_msg'].append('添加失败')
if e.args[0] == 1062:
request.session['alert_message_now']['danger_msg'].append(
'需要添加的相关信息重复')
request.session['alert_message_now']['danger_msg'].append(
e.args)
return render(request, 'dbmp_mysql_instance/add.html', params)
except Exception, e:
logger.info(traceback.format_exc())
# 保存失败转跳会原页面
request.session['alert_message_now']['danger_msg'].append(
'添加失败, 保存数据库错误')
return render(request, 'dbmp_mysql_instance/add.html', params)
def edit(request):
params = {}
####################################################################
# GET 请求
####################################################################
# 点击链接转跳到编辑页面
if request.method == 'GET':
# 获取MySQL实例ID
mysql_instance_id = int(request.GET.get('mysql_instance_id', '0'))
if mysql_instance_id:
try:
# 1.获取MySQL实例
dbmp_mysql_instance = DbmpMysqlInstance.objects.get(
mysql_instance_id=mysql_instance_id)
params['dbmp_mysql_instance'] = dbmp_mysql_instance
# 2.获得MySQL实例额外信息
try:
dbmp_mysql_instance_info = DbmpMysqlInstanceInfo.objects.get(
mysql_instance_id=dbmp_mysql_instance.mysql_instance_id
)
params[
'dbmp_mysql_instance_info'] = dbmp_mysql_instance_info
except DbmpMysqlInstanceInfo.DoesNotExist:
request.session['alert_message_now']['warning_msg'].append(
'该MySQL实例信息设置不完整, 请进行编辑')
# 3.获得操作系统
try:
cmdb_os = CmdbOs.objects.get(
os_id=dbmp_mysql_instance.os_id)
params['cmdb_os'] = cmdb_os
except CmdbOs.DoesNotExist:
logger.info(traceback.format_exc())
# 如果MySQL实例没有指定OS则告警
request.session['alert_message_now']['wraning_msg'].append(
'该MySQL实例没有指定一个OS')
return render(request, 'dbmp_mysql_instance/edit.html', params)
except DbmpMysqlInstance.DoesNotExist:
logger.info(traceback.format_exc())
# 返回点击编辑页面
request.session['danger_msg'].append('对不起! 找不到指定的MySQL实例')
return HttpResponseRedirect(request.environ['HTTP_REFERER'])
else:
request.session['danger_msg'] = '对不起! 找不到指定的MySQL实例!'
# 返回点击编辑页面
return HttpResponseRedirect(request.environ['HTTP_REFERER'])
####################################################################
# POST 请求
####################################################################
# 修改操作
if request.method == 'POST':
form = EditForm(request.POST)
# 验证表单
if form.is_valid():
mysql_instance_id = form.cleaned_data['mysql_instance_id']
mysql_instance_info_id = form.cleaned_data[
'mysql_instance_info_id']
os_id = form.cleaned_data['os_id']
host = form.cleaned_data['host']
port = form.cleaned_data['port']
username = form.cleaned_data['username']
password = form.cleaned_data['password']
remark = form.cleaned_data['remark']
my_cnf_path = form.cleaned_data['my_cnf_path']
base_dir = form.cleaned_data['base_dir']
start_cmd = form.cleaned_data['start_cmd']
host = IpTool.ip2num(host)
try:
with transaction.atomic():
# 更新DbmpMysqlInstance
DbmpMysqlInstance.objects.filter(
mysql_instance_id=mysql_instance_id).update(
os_id=os_id,
host=host,
port=port,
username=username,
password=password,
remark=remark)
if mysql_instance_info_id:
# 更新 dbmp_mysql_instance_info
DbmpMysqlInstanceInfo.objects.filter(
mysql_instance_info_id=mysql_instance_info_id
).update(my_cnf_path=my_cnf_path,
base_dir=base_dir,
start_cmd=start_cmd)
else:
# 插入数据 dbmp_mysql_instance_info
DbmpMysqlInstanceInfo.objects.create(
mysql_instance_id=mysql_instance_id,
my_cnf_path=my_cnf_path,
base_dir=base_dir,
start_cmd=start_cmd)
# 保存成功转跳到View页面
request.session['success_msg'].append('修改成功')
view_url = '{base_url}/view/?mysql_instance_id={id}'.format(
base_url=ViewUrlPath.path_dbmp_mysql_instance(),
id=mysql_instance_id)
return HttpResponseRedirect(view_url)
except IntegrityError, e:
logger.info(traceback.format_exc())
request.session['danger_msg'].append('编辑失败')
if e.args[0] == 1062:
request.session['danger_msg'].append('需要修改的相关信息重复')
request.session['danger_msg'].append(e.args)
return HttpResponseRedirect(request.environ['HTTP_REFERER'])
except Exception, e:
logger.info(traceback.format_exc())
# 保存失败转跳会原页面
request.session['danger_msg'].append('编辑失败, 保存数据库错误')
return HttpResponseRedirect(request.environ['HTTP_REFERER'])