def sql_kill():
user = app.config.get('MYSQL_USER')
password = app.config.get('MYSQL_PASSWORD')
form = MyForm.MyForm_sql_kill()
if form.submit.data:
try:
if form.ip.data and form.port.data:
# 获取页面相关信息
ip = form.ip.data.strip()
port = form.port.data.strip()
keyword = form.select_action.data
MYSQL = Mysql.MYSQL(user, password, ip, port)
cmd = 'show processlist;'
i = 0
for pid in MYSQL.Run(cmd):
if keyword in pid:
pid = pid[0]
cmd = 'kill %s;' % pid
MYSQL.Run(cmd)
i = i + 1
MYSQL.Close()
flash('清理完毕,共清除%i个sql连接......' % i)
except Exception as e:
flash(e)
finally:
return render_template('Message_static.html')
return render_template('mysql_kill.html', form=form)
def sql_create():
form = MyForm.MyForm_sql_create()
if form.submit.data:
try:
tt = time.strftime('%Y%m', time.localtime())
db_name = form.db_name.data.strip()
assert db_name, '数据库库名不能为空!'
sql_master = form.sql_master.data.strip()
assert sql_master, '主库服务器地址不能为空'
db_mysql = db_idc.idc_mysqldb
master_port = db_mysql.query.with_entities(db_mysql.port).filter(
and_(db_mysql.master == '是', db_mysql.ip == sql_master)).all()
assert master_port, '没有找到主库端口信息!'
sql_slave = db_mysql.query.with_entities(
db_mysql.ip, db_mysql.port).filter(
and_(db_mysql.slave == '是',
db_mysql.Master_Host == sql_master)).all()
assert sql_slave, '没有找到相关从库信息!'
#创建数据库
master_port = master_port[0][0]
MYSQL = Mysql.MYSQL(host=sql_master, port=master_port)
MYSQL.Run(
"UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `Grant_priv` = 'N' AND `User` = 'mysql_op';"
)
MYSQL.Run("FLUSH PRIVILEGES;")
Error_info = MYSQL.Run('create database %s;' % db_name)
if Error_info:
raise flash(Error_info)
else:
flash('%s数据库已创建!' % db_name)
Error_info = MYSQL.Run(
"GRANT SELECT,DELETE,UPDATE,INSERT ON {0}.* TO '{0}'@'172.16.%' IDENTIFIED BY '{0}@baihe_{1}';"
.format(db_name, tt))
MYSQL.Run("FLUSH PRIVILEGES;")
if Error_info:
raise flash(Error_info)
else:
flash('%s数据库权限已开通!' % db_name)
MYSQL.Close()
#写入dns配置库
db = db_op.dns_innr
#master
c = db(domain='sql.baihe',
field='%s.master' % db_name.replace('_', '-'),
Type='A',
ip=sql_master,
stats='1',
system='xs')
db_op.DB.session.add(c)
db_op.DB.session.commit()
flash('主库DNS解析完毕!')
#slave
flash('*' * 50)
flash('用户名:%s' % db_name)
flash('密码:%s@baihe_%s' % (db_name, tt))
flash('主库地址:%s 端口:%s' % ('%s.master.sql.baihe' %
db_name.replace('_', '-'), master_port))
for slave_ip, slave_port in sql_slave:
c = db(domain='sql.baihe',
field='%s.slave%s' %
(db_name.replace('_', '-'), slave_ip.split('.')[-1]),
Type='A',
ip=slave_ip,
stats='1',
system='xs')
db_op.DB.session.add(c)
db_op.DB.session.commit()
flash('从库地址:%s 端口:%s' %
('%s.slave%s.sql.baihe' %
(db_name.replace('_', '-'), slave_ip.split('.')[-1]),
slave_port))
flash('*' * 50)
flash('从库DNS解析完毕!')
except Exception as e:
if 'old-style' not in str(e):
flash(e)
return render_template('mysql_create.html',
Main_Infos=g.main_infos,
form=form)
def mysql_parse():
form = MyForm.MyForm_input()
db = db_idc.idc_mysqldb
db_table = db_idc.idc_tableinfo
if form.submit.data:
if form.text.data and form.input.data:
mysql_db = form.input.data.strip()
try:
if ';' in form.text.data.strip():
sql_lines = form.text.data.strip().replace('\r\n', ' ')
for Line in sql_lines.split(';'):
if '.' in Line:
raise flash('%s 语句中不能有库名!' % Line)
if Line:
Line = '%s ;' % Line.strip()
line = Line.lower()
if 'delete' in line:
table_name = Line.split('from')[1].split()[0]
elif 'update' in line:
table_name = Line.split()[1]
elif 'alter' in line:
table_name = Line.split()[2]
else:
flash(Line)
flash('------->该SQL语句不在审查范围内!')
continue
# 从sql语句中抽取表名
val = db_table.query.with_entities(
db_table.ip, db_table.port,
db_table.Engine_name, db_table.Rows).filter(
and_(
db_table.database_name == mysql_db,
db_table.table_name ==
table_name.replace('`', ''))).all()
if val:
mysql_ip, mysql_port, Engine_name, Rows = val[
0]
flash('----' * 24)
flash('数据库IP:{0},数据库端口:{1},表引擎:{2},表行数:{3}'.
format(mysql_ip, mysql_port, Engine_name,
Rows))
flash('----' * 24)
MYSQL = Mysql.MYSQL(user, password, mysql_ip,
mysql_port)
cmd = "explain %s" % Line
flash(Line)
result = MYSQL.Run(cmd)
if isinstance(result, list):
flash('------->语法检查通过!')
else:
raise flash('------->语法有错误! %s' %
str(result))
MYSQL.Close()
if (int(Rows) >= 10000
and Engine_name == 'MyISAM') or (
int(Rows) >= 50000
and Engine_name == 'InnoDB'):
result = 'SQL语句建议进行定时执行!'
else:
result = 'SQL语句可以直接执行!'
flash('----' * 24)
flash('审核结果:{0}'.format(result))
flash('----' * 24)
else:
raise flash('没有找到%s数据库的相关服务器信息!' % mysql_db)
else:
flash('SQL语句没有以分号结尾!')
return render_template('Message.html')
except Exception as e:
if 'old-style' not in str(e):
flash(e)
return render_template('Message_static.html')
else:
flash('输入框内容不能为空!')
return render_template('Message.html')
return render_template('mysql_parse.html', form=form)
def get_mysqldb_info():
try:
db_third = db_idc.third_resource
db_mysqldb = db_idc.idc_mysqldb
IDC_MySql = Mysql.MYSQL(MYSQL_USER, MYSQL_PW, MYSQL_HOST, MYSQL_PORT, 'idc')
IDC_MySql.Run("TRUNCATE TABLE tableinfo;")
infos = IDC_MySql.Run("select ip,port from mysqldb where master = '是' or slave = '是';")
except Exception as e:
logging.error(e)
else:
for info in infos:
ip,port = info
try:
INFO_MySql = Mysql.MYSQL(MYSQL_INFO_USER, MYSQL_INFO_PW, ip, port, 'mysql')
exclude_db = ('mysql', 'test', 'information_schema', 'performance_schema')
cmd = "show databases;"
Lists = INFO_MySql.Run(cmd)
Lists = [db[0] for db in Lists if db[0] not in exclude_db]
db_lists = ','.join(Lists)
except:
continue
else:
try:
cmd = "SHOW SLAVE STATUS;"
result = INFO_MySql.Run(cmd)
if result:
result = list(result[0])
m_ip = result[1].strip()
m_user = result[2].strip()
m_port = result[3]
s_io = result[10].strip()
s_sql = result[11].strip()
#判断获取的master主机信息是否需要解析
if len(m_ip.split('.')) != 4:
ssh_port = db_third.query.with_entities(db_third.ssh_port).filter(and_(db_third.ip==ip,db_third.app_port==port)).all()
ssh_port = ssh_port[0][0]
Ssh = SSH.ssh(ip=ip, ssh_port=ssh_port)
ssh_cmd = "/bin/cat /etc/hosts|/bin/grep {0}".format(str(m_ip))
values = Ssh.Run(ssh_cmd)
if values['stdout']:
m_ip = values['stdout'][0].split()[0]
else:
m_ip = None
if m_ip:
if s_io == 'Yes' and s_sql == 'Yes':
#判断真实的主库服务器信息
values = db_mysqldb.query.with_entities(db_mysqldb.Master_Host,db_mysqldb.Master_Port).filter(
and_(db_mysqldb.ip == m_ip, db_mysqldb.port == m_port,db_mysqldb.slave == '是')).all()
if values:
m_ip,m_port = values[0]
cmd = "update mysqldb set slave='是',master='否',db='%s',Master_Host='%s',Master_Port='%s',Master_User='******' where ip='%s' and port=%i;" % \
(db_lists, m_ip, m_port, m_user, ip, int(port))
else:
check_cmd = "show slave hosts;"
check_result = IDC_MySql.Run(check_cmd)
if check_result:
cmd = "update mysqldb set slave='否',master='是',db='%s',Master_Host='',Master_Port='',Master_User='' where ip='%s' and port=%i;" % (
db_lists, ip, int(port))
else:
cmd = "update mysqldb set slave='否',master='是',db='%s',Master_Host='',Master_Port='',Master_User='' where ip='%s' and port=%i;" % (
db_lists, ip, int(port))
IDC_MySql.Run(cmd)
except Exception as e:
INFO_MySql.Close()
logging.error(e)
try:
version = INFO_MySql.Run("show variables like 'version';")
version = version[0][-1]
for db in Lists:
cmd = "SHOW TABLE STATUS from %s;" % db
results = INFO_MySql.Run(cmd)
if results:
for result in results:
table_name = result[0]
table_engine = result[1]
table_Rows = int(result[4])
table_size = int(result[6]) + int(result[8])
if len(str(table_size)) > 9:
table_size = '%sGB' % (table_size / 1000 / 1000 / 1000)
elif len(str(table_size)) > 6:
table_size = '%sMB' % (table_size / 1000 / 1000)
else:
table_size = '%sKB' % (table_size / 1000)
table_charset = result[14]
cmd = "insert into tableinfo (ip, port, database_name, table_name, Engine_name, Rows,size,Charset, version,update_time) VALUES ('%s',%i,'%s','%s','%s',%i,'%s','%s','%s',now());" % \
(ip, int(port), db, table_name, table_engine, table_Rows, table_size, table_charset, version)
IDC_MySql.Run(cmd)
except Exception as e:
logging.error(e)
INFO_MySql.Close()
finally:
IDC_MySql.Close()
db_idc.DB.session.remove()
def task_run():
try:
try:
# 获取业务访问数据
db_business = db_op.business
db_project = db_op.project_list
business = db_business.query.with_entities(db_business.id, db_business.business).all()
business = {busi[0]: busi[1] for busi in business}
year = time.strftime('%Y', time.localtime())
ot = datetime.datetime.now() - datetime.timedelta(days=0)
ot = ot.strftime('%Y-%m-%dT00:00:00Z')
Key = 'op_business_pv_%s' % ot.split('T')[0]
Influx_cli = InfluxDBClient(influxdb_host, influxdb_port, influxdb_user, influxdb_pw, 'analysis_logs')
for id in business:
business_domain = db_project.query.with_entities(distinct(db_project.domain)).filter(
db_project.business_id == int(id)).all()
if business_domain:
hosts = [domain[0] for domain in business_domain]
pv_sum = []
for host in hosts:
if ',' in host:
hosts.extend(host.split(','))
else:
try:
cmd = 'select sum(mean_pv) from ' + 'analysis%s' % year + " where time >='%s' and host = '%s';" % (
ot, host)
result = Influx_cli.query(cmd)
if result:
for infos in result.get_points():
if infos:
pv_sum.append(infos['sum'] * 60)
except Exception as e:
logging.error(e)
if pv_sum:
pv_sum = reduce(lambda x, y: x + y, pv_sum)
RC_CLUSTER.hset(Key, business[id], pv_sum)
except Exception as e:
logging.error(e)
#获取数据库状态
now_date = datetime.datetime.now()
gd = now_date.strftime('%Y-%m-%dT%H:%M:%SZ')
influx_fields = defaultdict()
db_mysqldb = db_idc.idc_mysqldb
infos = db_mysqldb.query.with_entities(db_mysqldb.ip,db_mysqldb.port).filter(or_(db_mysqldb.master == '是',db_mysqldb.slave == '是')).all()
Influx_cli = InfluxDBClient(influxdb_host, influxdb_port, influxdb_user, influxdb_pw,'mysqld_status')
for info in infos:
try:
ip, port = info
MySql = Mysql.MYSQL(MYSQL_INFO_USER, MYSQL_INFO_PW, ip, port, 'mysql')
vals = MySql.Run("SHOW GLOBAL STATUS;")
except:
continue
else:
if vals:
vals = {val[0]: val[1] for val in vals}
#获取QPS
Key = 'op_mysqld_QPS_%s_%s' %(ip,port)
QPS = RC.getset(Key, int(vals['Questions']))
RC.expire(Key,3600)
if not QPS:
QPS = int(vals['Questions'])
influx_fields['QPS'] = float(int(vals['Questions']) - int(QPS)) / 3000
#获取TPS
Key = 'op_mysqld_TPS_%s_%s' % (ip, port)
TPS = RC.getset(Key, int(vals['Com_commit']) + int(vals['Com_rollback']))
RC.expire(Key,3600)
if not TPS:
TPS = int(vals['Com_commit']) + int(vals['Com_rollback'])
influx_fields['TPS'] = float(int(vals['Com_commit']) + int(vals['Com_rollback']) - int(TPS)) / 3000
#获取读写比
influx_fields['R/W'] = (int(vals['Com_select']) + int(vals['Qcache_hits'])) / (float(int(vals['Com_insert']) +int(vals['Com_update']) + int(vals['Com_delete']) + int(vals['Com_replace']))) * 100
#获取慢查询占比
influx_fields['S/Q'] = float(vals['Slow_queries']) / int(vals['Questions']) * 100
#获取接受流量
Key = 'op_mysqld_Bytes_received_%s_%s' % (ip, port)
Bytes_received =RC.getset(Key, int(vals['Bytes_received']))
RC.expire(Key,3600)
if not Bytes_received:
Bytes_received = int(vals['Bytes_received'])
influx_fields['Bytes_r'] = float((int(vals['Bytes_received']) - int(float(Bytes_received))))/1000
#获取发送流量
Key = 'op_mysqld_Bytes_sent_%s_%s' % (ip, port)
Bytes_sent =RC.getset(Key, float(vals['Bytes_sent']))
RC.expire(Key,3600)
if not Bytes_sent:
Bytes_sent = int(vals['Bytes_sent'])
influx_fields['Bytes_s'] = (int(vals['Bytes_sent']) - int(float(Bytes_sent)))/1000
#获取连接数
influx_fields['Connections'] = len(MySql.Run("SHOW processlist;"))
# 写入influxdb数据库
if influx_fields:
try:
json_body = [{"measurement": "performance", "tags": {"ip": ip, "port":port},
"fields": influx_fields, "time": gd}]
Influx_cli.write_points(json_body)
except Exception as e:
logging.error(e)
MySql.Close()
except Exception as e:
logging.error(e)
finally:
db_idc.DB.session.remove()
db_op.DB.session.remove()
#获取k8s的hpa副本数量
try:
td = time.strftime('%Y-%m-%d',time.localtime())
th = time.strftime('%H:%M',time.localtime())
v1 = client.AutoscalingV1Api()
ret = v1.list_horizontal_pod_autoscaler_for_all_namespaces()
Key = 'op_hpa_chart_%s' %td
for i in ret.items:
RC.hset(Key,'%s_%s'%(i.spec.scale_target_ref.name,th),i.status.current_replicas)
except Exception as e:
logging.error(e)
def tables(db_name=None, table_name=None, host_info=None):
table_infos = {}
tb_index = ('Non_unique', 'Key_name', 'Seq_in_index', 'Column_name',
'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null',
'Index_type', 'Comment', 'Index_comment')
tb_columns = ('Field', 'Type', 'Collation', 'Null', 'Key', 'Default',
'Extra', 'Comment')
tables = ('ip', 'port', '主从', '数据库', '表名', '引擎', '行数', '大小', '编码', '版本')
Indexs = None
values = None
Columns = None
create_table = None
#数据库表列表
if db_name and not table_name and not host_info:
try:
db_tableinfo = db_idc.idc_tableinfo
db_mysqldb = db_idc.idc_mysqldb
values = db_tableinfo.query.with_entities(
db_tableinfo.ip, db_tableinfo.port, db_tableinfo.database_name,
db_tableinfo.table_name, db_tableinfo.Engine_name,
db_tableinfo.Rows, db_tableinfo.size, db_tableinfo.Charset,
db_tableinfo.version).filter(
db_tableinfo.database_name == db_name).all()
if values:
values = [list(v) for v in values]
ips = [val[:2] for val in values]
for info in ips:
ip, port = info
ms_infos = db_mysqldb.query.with_entities(
db_mysqldb.master, db_mysqldb.slave).filter(
and_(db_mysqldb.ip == ip,
db_mysqldb.port == port)).all()
if ms_infos[0][0] == '是':
table_infos['%s:%s' % (ip, port)] = '主库'
elif ms_infos[0][1] == '是':
table_infos['%s:%s' % (ip, port)] = '从库'
else:
table_infos['%s:%s' % (ip, port)] = '未知'
except Exception as e:
logging.error(e)
#表结构和索引详情
if table_name and host_info and db_name:
try:
ip, app_port = host_info.split(':')
INFO_MySql = Mysql.MYSQL(MYSQL_INFO_USER, MYSQL_INFO_PW, ip,
app_port, 'mysql')
Columns = INFO_MySql.Run("SHOW full COLUMNS FROM %s.%s;" %
(db_name, table_name))
Indexs = INFO_MySql.Run("SHOW INDEX FROM %s.%s;" %
(db_name, table_name))
create_table = INFO_MySql.Run("SHOW CREATE TABLE %s.%s;" %
(db_name, table_name))
create_table = create_table[0][1]
except Exception as e:
logging.error(e)
finally:
INFO_MySql.Close
return render_template('tables_show.html',
values=values,
tables=tables,
db_name=db_name,
table_name=table_name,
table_infos=table_infos,
Columns=Columns,
tb_columns=tb_columns,
tb_index=tb_index,
Indexs=Indexs,
create_table=create_table)