检查代码
#!/usr/bin/env python
# -*- encoding: utf-8 -*-
"""
@文件 :ora-db_check.py
@说明 :
@时间 :2023/05/25 14:26:10
@作者 :Jaja
@版本 :1.0
"""
import pymysql
from jinja2 import Environment, FileSystemLoader
import yaml
import os
import time
# 获取当前文件的路径
current_path = os.path.abspath(__file__)
# 获取当前文件的父路径
father_path = os.path.abspath(os.path.dirname(current_path) + os.path.sep + ".")
checkmate = time.strftime("%Y%m%d", time.localtime())
# 创建文件夹
if not os.path.exists(father_path + '/' + checkmate):
os.makedirs(father_path + '/' + checkmate)
CheckTitle = "¥¥¥¥¥¥¥Mysql数据库巡检报告"
CheckUserName = "¥¥¥¥"
CheckCompany = "¥¥¥¥¥¥"
CheckScriptVersion = "V1.0"
CheckExplain = "这是SQL查询结果的巡检说明。"
current_datetime = time.strftime("%Y-%m-%d", time.localtime())
def run_query():
# 执行查询并获取结果集的列信息和数据行
queries = [
{
'query': "SELECT now_date,user,CURRENT_USER1,CONNECTION_ID,ifnull(db_name,'') dbname,Server_version,ifnull(all_db_size_MB,'') all_db_size_MB,ifnull(all_datafile_size_MB,'') all_datafile_size_MB,datadir,SOCKET,log_error,autocommit,log_bin,server_id from (SELECT now() now_date,USER() user,CURRENT_USER() CURRENT_USER1,CONNECTION_ID() CONNECTION_ID,DATABASE() db_name,version() Server_version,( SELECT sum( TRUNCATE ( ( data_length + index_length ) / 1024 / 1024, 2 ) ) AS 'all_db_size(MB)' FROM information_schema.TABLES b ) all_db_size_MB,(select truncate(sum(total_extents*extent_size)/1024/1024,2) from information_schema.FILES b) all_datafile_size_MB,( SELECT @@datadir ) datadir,( SELECT @@SOCKET ) SOCKET,( SELECT @@log_error ) log_error,( SELECT @@autocommit ) autocommit,( SELECT @@log_bin ) log_bin,( SELECT @@server_id ) server_id ) V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '数据库基本信息',
'description': '数据库慢查询信息',
'flag': '0'
},
{
"query": "SELECT TABLE_SCHEMA,ENGINE,COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','SYS','MYSQL') AND TABLE_TYPE='BASE TABLE' GROUP BY TABLE_SCHEMA,ENGINE",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '存储引擎分布',
'description': '存储引擎分布',
'flag': '0'
},
{
"query": "SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,ifnull(table_rows,'') table_rows,ifnull(data_size_mb,'') data_size_mb,ifnull(index_size_mb,'') index_size_mb,ifnull(all_size_mb,'') all_size_mb,ifnull(max_size_mb,'') max_size_mb,ifnull(free_size_mb,'') free_size_mb,ifnull(disk_size_mb,'') disk_size_mb from (select a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME,sum(table_rows) as table_rows,truncate(sum(data_length)/1024/1024, 2) as data_size_mb,truncate(sum(index_length)/1024/1024, 2) as index_size_mb,truncate(sum(data_length+index_length)/1024/1024, 2) as all_size_mb,truncate(sum(max_data_length)/1024/1024, 2) as max_size_mb,truncate(sum(data_free)/1024/1024, 2) as free_size_mb,max(f.filesize_M) as disk_size_mb from INFORMATION_SCHEMA.SCHEMATA a left outer join information_schema.tables b on a.SCHEMA_NAME=b.TABLE_SCHEMA left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name,truncate(sum(total_extents*extent_size)/1024/1024,2) filesize_M from information_schema.FILES b group by substring(b.file_name,3,locate('/',b.file_name,3)-3)) f on ( a.SCHEMA_NAME= f.db_name) group by a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME order by sum(data_length) desc, sum(index_length) desc) V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '数据库实例的所有数据库及其容量大小',
'description': '数据库实例的所有数据库及其容量大小',
'flag': '0'
},
{
"query": "select iis.database_name,iis.table_name,iis.index_name,round((iis.stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,s.NON_UNIQUE,s.INDEX_TYPE,GROUP_CONCAT(s.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME from (select * from mysql.innodb_index_stats WHERE index_name not in ('PRIMARY','GEN_CLUST_INDEX') and stat_name='size' order by (stat_value*@@innodb_page_size) desc limit 10) iis left join INFORMATION_SCHEMA.STATISTICS s on (iis.database_name=s.TABLE_SCHEMA and iis.table_name=s.TABLE_NAME and iis.index_name=s.INDEX_NAME) GROUP BY iis.database_name,iis.TABLE_NAME,iis.INDEX_NAME,(iis.stat_value*@@innodb_page_size),s.NON_UNIQUE,s.INDEX_TYPE order by SizeMB",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '占用空间最大的前10个索引',
'description': '占用空间最大的前10个索引',
'flag': '0'
},
{
"query": "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION,ENGINE,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,'SYS','MYSQL','PERFORMANCE_SCHEMA') AND TABLE_TYPE='BASE TABLE' AND ENGINE NOT IN ('INNODB') ORDER BY TABLE_ROWS DESC",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '非 INNODB 存储引擎表',
'description': '非 INNODB 存储引擎表',
'flag': '0'
},
{
"query": "select * from performance_schema.global_variables where VARIABLE_NAME in ( 'datadir','SQL_MODE','socket','TIME_ZONE','tx_isolation','transaction_isolation','autocommit','innodb_lock_wait_timeout','max_connections','max_user_connections','slow_query_log','log_output','slow_query_log_file','long_query_time','log_queries_not_using_indexes','log_throttle_queries_not_using_indexes','log_throttle_queries_not_using_indexes','pid_file','log_error','lower_case_table_names','innodb_buffer_pool_size','innodb_flush_log_at_trx_commit','read_only', 'log_slave_updates','innodb_io_capacity','query_cache_type','query_cache_size','max_connect_errors','server_id','innodb_file_per_table')",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': 'Mysql数据库重要的参数',
'description': 'Mysql数据库重要的参数',
'flag': '0'
},
{
"query": "select * from performance_schema.global_variables where VARIABLE_NAME in ( 'server_id','server_uuid','log_bin','log_bin_basename','sql_log_bin','log_bin_index','log_slave_updates','read_only','slave_skip_errors','max_allowed_packet','slave_max_allowed_packet','auto_increment_increment','auto_increment_offset','sync_binlog','binlog_format','expire_logs_days','max_binlog_size','slave_skip_errors','sql_slave_skip_counter','slave_exec_mode','rpl_semi_sync_master_enabled','rpl_semi_sync_master_timeout','rpl_semi_sync_master_trace_level','rpl_semi_sync_master_wait_for_slave_count','rpl_semi_sync_master_wait_no_slave','rpl_semi_sync_master_wait_point','rpl_semi_sync_slave_enabled','rpl_semi_sync_slave_trace_level')",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': 'Mysql数据库重要的参数(主从复制参数)',
'description': 'Mysql数据库重要的参数(主从复制参数)',
'flag': '0'
},
{
"query": "select * from performance_schema.global_status where VARIABLE_NAME in ( 'connections','uptime','com_select','com_insert','com_delete','slow_queries','Created_tmp_tables','Created_tmp_files','Created_tmp_disk_tables','table_cache','Handler_read_rnd_next','Table_locks_immediate','Table_locks_waited','Open_files','Opened_tables','Sort_merge_passes','Sort_range','Sort_rows','Sort_scan')",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': 'Mysql数据库性能参数统计',
'description': 'Mysql数据库性能参数统计',
'flag': '0'
},
{
"query": "select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and (table_schema, table_name) not in ( select /*+ subquery(materialization) */ a.TABLE_SCHEMA,a.TABLE_NAME from information_schema.TABLE_CONSTRAINTS a where a.CONSTRAINT_TYPE in ('PRIMARY KEY','UNIQUE') and table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') ) AND table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') limit 100",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '无主键或唯一键的表',
'description': '无主键或唯一键的表',
'flag': '0'
},
{
'query': "select * from mysql.slow_log", # 替换为第一个查询语句
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '数据库慢查询信息',
'description': '数据库慢查询信息',
'flag': '0'
},
{
'query': "show slave status", # 替换为第一个查询语句
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '数据库主从同步信息',
'description': '数据库主从同步信息',
'flag': '0'
},
{
'query': "SELECT query,ifnull(db,''),full_scan,exec_count,err_count,warn_count,total_latency,max_latency,avg_latency,rows_sent,rows_sent_avg,rows_examined,rows_examined_avg,first_seen,last_seen,digest from (SELECT sys.format_statement(DIGEST_TEXT) AS query, SCHEMA_NAME as db, IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan, COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, sys.format_time(SUM_TIMER_WAIT) AS total_latency, sys.format_time(MAX_TIMER_WAIT) AS max_latency, sys.format_time(AVG_TIMER_WAIT) AS avg_latency, SUM_ROWS_SENT AS rows_sent, ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_examined, ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, FIRST_SEEN AS first_seen, LAST_SEEN AS last_seen, DIGEST AS digest FROM performance_schema.events_statements_summary_by_digest stmts JOIN sys.x$ps_digest_95th_percentile_by_avg_us AS top_percentile ON ROUND(stmts.avg_timer_wait/1000000) >= top_percentile.avg_us ORDER BY AVG_TIMER_WAIT DESC limit 10) V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '查看平均执行时间值大于95%的平均执行时间的语句',
'description': '查看平均执行时间值大于95%的平均执行时间的语句（可近似地认为是平均执行时间超长的语句），默认情况下按照语句平均延迟(执行时间)降序排序',
'flag': '0'
},
{
'query': "SELECT thd_id,conn_id,user,db,command,state,time,current_statement,statement_latency,progress,lock_latency,rows_examined,rows_sent,rows_affected,tmp_tables,tmp_disk_tables,full_scan,last_statement,last_statement_latency,current_memory,last_wait,last_wait_latency,source,trx_latency,trx_state,trx_autocommit,pid,program_name from (select * from sys.session where conn_id!=connection_id() and trx_state='ACTIVE') V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '查看当前正在执行的语句进度信息',
'description': '查看当前正在执行的语句进度信息',
'flag': '0'
},
{
'query': "SELECT thd_id,conn_id,user,ifnull(db,''),command,ifnull(state,''),time,ifnull(current_statement,''),ifnull(statement_latency,''),ifnull(progress,''),lock_latency,rows_examined,rows_sent,rows_affected,tmp_tables,tmp_disk_tables,full_scan,last_statement,last_statement_latency,current_memory,ifnull(last_wait,''),ifnull(last_wait_latency,''),ifnull(source,''),trx_latency,trx_state,trx_autocommit,pid,ifnull(program_name,'') from (select * from sys.session where conn_id!=connection_id() and trx_state='COMMITTED') V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '查看已经执行完的语句相关统计信息',
'description': '查看已经执行完的语句相关统计信息',
'flag': '0'
},
{
'query': "SELECT query,ifnull(db,''),full_scan,exec_count,err_count,warn_count,total_latency,max_latency,avg_latency,lock_latency,rows_sent,rows_sent_avg,rows_examined,rows_examined_avg,rows_affected,rows_affected_avg,tmp_tables,tmp_disk_tables,rows_sorted,sort_merge_passes,digest,first_seen,last_seen from (SELECT * FROM sys.statement_analysis WHERE full_scan = '*' order by exec_count desc limit 10) V",
'columns_var': 'columns1',
'rows_var': 'rows1',
'name': '执行次数Top10',
'description': '执行次数Top10',
'flag': '0'
}
# 添加更多查询语句和结果集变量
]
results = []
errors = []
for query in queries:
try:
conn = pymysql.connect(host=hosts['ip'], port=hosts['port'], user=hosts['username'],password=hosts['password'], database=hosts['sid'])
cursor = conn.cursor()
if query['flag'] == '0':
cursor.execute(query['query'])
columns = [col[0] for col in cursor.description]
rows = cursor.fetchall()
results.append({
'columns_var': query['columns_var'],
'columns': columns,
'rows_var': query['rows_var'],
'rows': rows,
'name': query['name'],
'description': query['description']
})
cursor.close()
conn.close()
elif query['flag'] == '1':
pass
except pymysql.MySQLError as e:
print('IP：%s 用途为: %s 的数据库无法正常连接,请检查!报错代码: %s' % (hosts['ip'], hosts['use'], e))
break
except ConnectionRefusedError:
print("连接被拒绝，请检查目标计算机或端口是否可用。")
break
except UnboundLocalError:
break
# 关闭数据库连接
# 加载 Jinja2 模板
env = Environment(loader=FileSystemLoader('.'))
template = env.get_template('testjinjia2_v1.0.html') # 替换为实际的模板文件路径
# 渲染模板并生成 HTML 文件
output = template.render(results=results,current_datetime=current_datetime,CheckTitle=CheckTitle,CheckUserName=CheckUserName,CheckCompany=CheckCompany,CheckScriptVersion=CheckScriptVersion,CheckExplain=CheckExplain,errors=errors,CheckTitle_info=CheckTitle_info)
with open(dbcheck_file, 'w') as file:
file.write(output)
print("数据库： %s 实例： %s 巡检报告HTML 文件已生成。" % (hosts['ip'],hosts['sid']))
# 读取YAML配置文件
with open('mysql_config.yaml', 'r') as f:
try:
config = yaml.safe_load(f)
except yaml.scanner.ScannerError as e1:
print("Yaml文件错误 %s" % e1)
for db in config['databases']:
for hosts in db['hosts']:
dbcheck_file = Check_Oracle_file = father_path + '/' + checkmate + '/' + current_datetime + '_' + hosts['use'] + '_' + hosts['sid'] + '_' + hosts['ip'] + '.html'
CheckTitle_info = '<br>' + '巡检数据库' + ':' + hosts['use'] + '<br>' + '巡检主机' + ':' + hosts['ip']
run_query()
f.close()
yaml配置文件
databases:
- type: mysql
hosts:
- ip: 192.168.98.139
port: 3306
username: root
password: Passw0rd@123
sid: mysql
use: 测试mysql数据库主
- ip: 192.168.98.139
port: 3307
username: root
password: Passw0rd@123
sid: mysql
use: 测试mysql数据库备
jinjia2模板
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> {{ CheckTitle }} </title>
<style>
.logo {
width: 200px; /* 自定义宽度 */
height: auto; /* 自定义高度或使用 auto 保持纵横比 */
margin-right: 10px;
}
body {
font-family: Arial, sans-serif;
margin: 20px;
}
header {
display: flex;
align-items: center;
justify-content: center;
flex-wrap: wrap;
text-align: center;
margin-bottom: 30px;
}
h1 {
text-align: center;
font-size: 40px;
margin-bottom: 30px;
}
h0 {
text-align: center;
font-size: 40px;
margin-bottom: 30px;
}
h2 {
margin-top: 40px;
margin-bottom: 16px;
}
table {
border-collapse: collapse;
width: 100%;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
text-align: left;
}
th {
background-color: #4CAF50;
color: white;
}
ul {
list-style-type: none;
margin-left: 20px;
}
li {
margin-bottom: 10px;
}
#top-link {
position: fixed;
bottom: 20px;
right: 20px;
padding: 10px;
background-color: #4CAF50;
color: white;
text-decoration: none;
display: none;
}
.result-set {
margin-bottom: 40px;
}
.result-set-title {
margin-bottom: 20px;
}
.result-set-title a {
font-size: 18px;
}
.result-set-remark {
font-style: italic;
margin-top: 10px;
}
</style>
<script>
window.addEventListener('scroll', function() {
var topLink = document.getElementById('top-link');
if (window.pageYOffset > 200) {
topLink.style.display = 'block';
} else {
topLink.style.display = 'none';
}
});
</script>
</head>
<body>
<header>
<!--<img class="logo" src="jxbank.jpeg" alt="江西银行LOGO">-->
<h1>{{ CheckTitle }} {{ CheckTitle_info }}</h1>
</header>
<header1>
<p>巡检人员: {{ CheckUserName }}</p>
<p>巡检公司: {{ CheckCompany }}</p>
<p>巡检版本: {{ CheckScriptVersion }}</p>
<p>巡检日期: {{ current_datetime }}</p>
<p>巡检说明: {{ CheckExplain }}</p>
</header1>
<a href="#top" id="top-link">返回顶部</a>
<h2>目录</h2>
<ul>
{% for i in range(results|length) %}
<li><a href="#result-set-{{ i+1 }}">{{ results[i]['name'] }}</a></li>
{% endfor %}
{% if errors %}
<li><a href="#sql-errors">SQL 执行错误</a></li>
{% endif %}
</ul>
{% for i in range(results|length) %}
<div class="result-set">
<h2 id="result-set-{{ i+1 }}" class="result-set-title">{{ results[i]['name'] }} <a href="#top">返回顶部</a></h2>
<table>
<tr>
{% for column in results[i]['columns'] %}
<th>{{ column }}</th>
{% endfor %}
</tr>
{% for row in results[i]['rows'] %}
<tr>
{% for value in row %}
<td>{{ value }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
<p class="result-set-remark">{{ results[i]['description'] }}</p>
</div>
{% endfor %}
{% if errors %}
<div class="result-set">
<h2 id="sql-errors" class="result-set-title">SQL 执行错误 <a href="#top">返回顶部</a></h2>
<ul>
{% for error in errors %}
<li>{{ error }}</li>
{% endfor %}
</ul>
</div>
{% endif %}
</body>
</html>
最后修改时间：2023-07-10 10:08:07
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
