def upload_user_stats():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
db.execute('truncate table meta_table_statistics') # 先truncate 再插入
db.execute('truncate table meta_index_statistics') # 先truncate 再插入
# 由于id为64的E4S 服务器不在平台管辖，先手工剔除 b.id !=64
v_sql = r"""SELECT b.ip,b.port,b.id as instance_id,a.id as schema_id,a.name as db_name from
resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id"""
#print v_sql
upload_tables_list = db.query(v_sql)
if upload_tables_list: # 对实例表进行循环
i=0
for upload_table in upload_tables_list:
instance_id = upload_table['instance_id']
schema_id = upload_table['schema_id']
db_name = upload_table['db_name']
host_ip = upload_table['ip']
mysql_port = upload_table['port']
v_host =host_ip + ':' + str(mysql_port)
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
# 取表的元信息然后插入
v_pl = r"""SELECT table_schema,table_name,rows_read,rows_changed,rows_changed_x_indexes
from table_statistics where table_schema='%s' """ % (db_name)
#print v_pl
table_list = db_remote.query(v_pl)
for table_row in table_list:
table_schema = table_row['table_schema']
table_name = table_row['table_name']
rows_read = table_row['rows_read']
rows_changed = table_row['rows_changed']
rows_changed_x_indexes = table_row['rows_changed_x_indexes']
#try:
v_insert_sql='''insert into meta_table_statistics(instance_id,schema_id,table_schema,
table_name,rows_read,rows_changed,rows_changed_x_indexes)
values(%d,%d,'%s','%s',%d,%d,%d)''' % (
instance_id,schema_id,table_schema,
table_name,rows_read,rows_changed,rows_changed_x_indexes
)
#print v_insert_sql
db.execute(v_insert_sql.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
# 取索引的元信息，然后插入
v_pl2 = r"""SELECT table_schema,table_name,index_name,rows_read from index_statistics
where TABLE_SCHEMA='%s' """ % (db_name)
table_list2 = db_remote.query(v_pl2)
for table_row2 in table_list2:
table_schema = table_row2['table_schema']
table_name = table_row2['table_name']
index_name = table_row2['index_name']
rows_read = table_row2['rows_read']
#try:
v_insert_sql2='''insert into meta_index_statistics(instance_id,schema_id,table_schema,
table_name,index_name,rows_read)
values(%d,%d,'%s','%s','%s',%d)''' % (
instance_id,schema_id,table_schema,
table_name,index_name,rows_read
)
#print v_insert_sql
db.execute(v_insert_sql2.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
db_remote.close()
# 开始生成log_hot_tables 统计表数据
# statistic_time 为统计数据的实际生成时间
v_insert_sql3='''insert into log_hot_tables(instance_id,schema_id,table_schema,
table_name,TABLE_COMMENT,TABLE_CREATE_TIME,rows_read,rows_changed,
rows_changed_x_indexes) select a.instance_id,a.schema_id,a.TABLE_SCHEMA,
a.TABLE_NAME,b.TABLE_COMMENT,b.CREATE_TIME,a.ROWS_READ,a.ROWS_CHANGED,
a.ROWS_CHANGED_X_INDEXES from meta_table_statistics a,meta_tables b where
a.instance_id=%d and a.schema_id=%d and
a.TABLE_SCHEMA='%s' and a.instance_id=b.instance_id and a.TABLE_SCHEMA=b.TABLE_SCHEMA
and a.TABLE_NAME=b.TABLE_NAME''' % (
instance_id,schema_id,table_schema
)
print v_insert_sql3
db.execute(v_insert_sql3.replace('%','%%'))
i=i+1
db.close()
# 开始远程flush 统计信息表
# for remote execute
os_user = '******'
OS_APPS_PASSWD = config.OS_APPS_PASSWD
DB_USER = config.DB_USER
DB_PASSWD = config.DB_PASSWD
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
# 由于id为64的E4S 服务器不在平台管辖，先手工剔除 b.id !=64
v_sql = r"""SELECT distinct b.ip,b.port from
resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id"""
#print v_sql
server_list = db.query(v_sql)
if server_list: # 对实例表进行循环
i=0
for single_server in server_list:
host_ip = single_server['ip']
mysql_port = single_server['port']
# 远程paramiko调用 在本机执行sql
exe_sql = 'flush table_statistics;flush index_statistics;'
result = func.remote_exe_sql(host_ip,os_user,OS_APPS_PASSWD,mysql_port,'information_schema',exe_sql,DB_USER,DB_PASSWD)
if result == '':
result = '执行成功!'
print result
def register_execute_cmd():
if request.method == 'POST':
text_db_ip = request.form['text_db_ip']
text_db_port = request.form['text_db_port']
text_db_sid = request.form['text_db_sid']
text_db_niname = request.form['text_db_niname']
text_db_master = request.form['text_db_master']
s_db_type = request.form['s_db_type']
s_db_inst_cnt = request.form['s_db_inst_cnt']
belong_app = request.form['belong_app']
text_app_name = request.form['text_app_name']
text_app_code = request.form['text_app_code']
text_app_manager = request.form['text_app_manager']
product_dept = request.form['product_dept']
is_new_app = request.form['is_new_app']
os_oracle_username = request.form['os_oracle_username']
os_oracle_password = request.form['os_oracle_password']
app_oracle_username = request.form['app_oracle_username']
app_oracle_password = request.form['app_oracle_password']
exe_sql ="""select instance_name,host_name,version,startup_time,status,name,db_unique_name,log_mode,platform_name from v$instance,v$database;"""
# 远程paramiko调用 在本机执行sql
print text_db_ip,os_oracle_username,os_oracle_password,text_db_port,text_db_sid,exe_sql,app_oracle_username,app_oracle_password
result = func.remote_exe_sql(text_db_ip,os_oracle_username,os_oracle_password,text_db_port,text_db_sid,exe_sql,app_oracle_username,app_oracle_password)
list_add_db = ''
print is_new_app
if result != 'ng' :
result = '执行成功!'
result_type = 1
if is_new_app == '1':
print text_app_code,text_app_name,text_app_manager,session['user_id'],product_dept;
v_ins_app_sql = """insert into T_DB_APPS(app_code,app_name,app_manager,state,creator,plat_id)
values("%s","%s","%s",%d,%d,%d)""" % (
text_app_code,text_app_name,text_app_manager,1,int(session['user_id']),int(product_dept))
print v_ins_app_sql
#current_app.logger.info(v_log_sql)
# 把 exe_sql 中可能包含的%字符串 替换为 %%, 避免因为本身的字符串%格式化倾向，而发生TypeError: not enough arguments for format string 错误
v_pk_app_id = g.db.execute(v_ins_app_sql.replace('%','%%'))
belong_app = v_pk_app_id;
else:
pass
v_ins_db_sql = """insert into T_DB_DATABASES(db_name_en,db_unique_name,db_name_cn,db_type,db_version,app_id,is_cluster,is_archivelog,is_dataguard,os_platform,state,creator,db_master)
values("%s","%s","%s",%d,"%s",%d,%d,%d,%d,"%s",%d,%d,"%s")""" % (
'vv','vv','vv',int(s_db_type),'vv',int(belong_app),0,0,0,'vv',1,session['user_id'],text_db_master)
v_pk_db_id = g.db.execute(v_ins_db_sql.replace('%','%%'))
v_ins_inst_sql = """insert into T_DB_INSTANCES(database_id,ip,listen_port,instance_name,is_cluster,inst_id,host_name,instance_version,state,creator,os_oracle_username,os_oracle_password)
values(%d,"%s","%s","%s",%d,%d,"%s","%s",%d,%d,"%s","%s")""" % (
v_pk_db_id,text_db_ip,text_db_port,text_db_sid,0,1,'vv','vv',1,session['user_id'],os_oracle_username,os_oracle_password)
v_pk_inst_id = g.db.execute(v_ins_inst_sql.replace('%','%%'))
# 新增加的log记录返回给前端
v_reg_add_list_sql = """select A.instance_id, concat(A.ip,':',A.listen_port) as ipport, A.instance_name, B.db_name_en, B.db_name_cn, B.is_cluster, A.inst_id, C.app_name, C.app_manager, D.plat_name, A.create_date from T_DB_INSTANCES A INNER JOIN T_DB_DATABASES B ON A.database_id=B.database_id INNER JOIN T_DB_APPS C ON B.app_id=C.app_id INNER JOIN T_DB_PLATS D ON C.plat_id=D.plat_id and A.instance_id= %d order by A.database_id,A.instance_id """ % (v_pk_inst_id)
list_add_db = g.db.query(v_reg_add_list_sql.replace('%','%%'))
else:
result = '执行失败!'
result_type = 2
print list_add_db
print result;
return jsonify(list_add_db=list_add_db)
