231
技術社區[雲棲]
自動監控MySQL表結構變更腳本
如何監控MySQL數據庫表結構和表變更，並通知到相關的聯係人、實現報警或通知？由於平台采用django係統實現，因此通過如下代碼實現（代碼low，也可以寫成python文件，傳參數執行）：
簡單思路：
對用戶指定庫的所有列值進行md5，並存儲到本地數據庫，每次定時執行，校對md5，並找出不匹配的進行判斷
會自動找出新增、刪除、變更表結構的表
# models.py
class MonitorSchema(models.Model):
table_schema = models.CharField(null=False, max_length=512)
table_name = models.CharField(null=False, max_length=512)
table_stru = models.TextField(null=False, default='')
md5_sum = models.CharField(null=False, max_length=256)
class Meta:
verbose_name = u'監控表結構變更表'
verbose_name_plural = verbose_name
permissions = ()
db_table = "dbaudit_monitor_schema"
# tasks.py
import datetime
import hashlib
import difflib
import mysql.connector as mdb
from celery import shared_task
from django.core.mail import EmailMessage
from django.template.loader import render_to_string
from auditdb.settings import EMAIL_FROM
@shared_task
def schema_modify_monitor(**kwargs):
check_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
conn = connect_db(**kwargs)
cursor = conn.cursor(dictionary=True)
query_info = "select table_schema,table_name,group_concat(COLUMN_NAME) as column_name," \
"group_concat(COLUMN_DEFAULT) as column_default,group_concat(IS_NULLABLE) as is_nullable," \
"group_concat(DATA_TYPE) as data_type,group_concat(CHARACTER_MAXIMUM_LENGTH) as char_length," \
"group_concat(COLUMN_TYPE) as column_type,group_concat(COLUMN_COMMENT) as column_comment " \
"from columns where table_schema='{schema}' " \
"group by table_schema,table_name".format(schema=kwargs['schema'])
cursor.execute(query_info)
source_info = []
table_list = []
diff_old_data = ''
diff_new_data = ''
table_change_data = []
for row in cursor.fetchall():
table_schema = row['table_schema']
table_name = row['table_name']
md5_source = ''.join(str(row.values()))
md5_sum = hashlib.md5(md5_source.encode('utf8')).hexdigest()
source_info.append({'table_schema': table_schema, 'table_name': table_name, 'md5_sum': md5_sum})
table_list.append(table_name)
# 如果當前庫沒有記錄，則進行初始化全量同步
if MonitorSchema.objects.filter(table_schema=kwargs['schema']).first() is None:
for row in source_info:
table_schema = row['table_schema']
table_name = row['table_name']
query_table_stru = "show create table {}".format('.'.join((table_schema, table_name)))
cursor.execute(query_table_stru)
for i in cursor:
table_stru = i['Create Table']
row['table_stru'] = str(table_stru)
MonitorSchema.objects.create(**row)
else:
# 如果存在，開始核驗數據
old_data = list(MonitorSchema.objects.filter(table_schema=kwargs['schema']).values_list('table_name', flat=True))
new_data = table_list
# 找出已刪除的表，並處理
table_remove = list(set(old_data).difference(set(new_data)))
if table_remove:
table_change_data.append({'remove': table_remove})
# 從本地庫中刪除該表的記錄
MonitorSchema.objects.filter(table_schema=kwargs['schema']).filter(table_name__in=table_remove).delete()
# 找出新增的表，並處理
table_add = list(set(new_data).difference(set(old_data)))
if table_add:
for i in table_add:
for j in source_info:
if i in j.values():
table_change_data.append({'add': j})
table_schema = j['table_schema']
table_name = j['table_name']
query_table_stru = "show create table {}".format('.'.join((table_schema, table_name)))
cursor.execute(query_table_stru)
for x in cursor:
table_stru = x['Create Table']
j['table_stru'] = str(table_stru)
MonitorSchema.objects.create(**j)
# 找出相同的表，並核驗表結構
table_intersection = list(set(old_data).intersection(set(new_data)))
for row in source_info:
table_schema = row['table_schema']
table_name = row['table_name']
new_md5_sum = row['md5_sum']
if table_name in table_intersection:
old_table = MonitorSchema.objects.get(table_schema=table_schema, table_name=table_name)
if new_md5_sum != old_table.md5_sum:
query_table_stru = "show create table {}".format('.'.join((table_schema, table_name)))
cursor.execute(query_table_stru)
for i in cursor:
table_stru = i['Create Table']
diff_old_data += old_table.table_stru + '\n'*3
diff_new_data += table_stru + '\n'*3
# 更新新表表結構到本地
MonitorSchema.objects.update_or_create(table_schema=table_schema, table_name=table_name,
defaults={'table_stru': table_stru,
'md5_sum': new_md5_sum})
if (diff_old_data and diff_new_data) or table_change_data:
html_data = ''
if diff_old_data and diff_new_data:
diff_data = difflib.HtmlDiff(tabsize=2)
old_table_stru = list(diff_old_data.split('\n'))
new_table_stru = list(diff_new_data.split('\n'))
html_data = diff_data.make_file(old_table_stru, new_table_stru, '舊表-表結構', '新表-表結構', context=False,
numlines=5)
email_html_body = render_to_string('_monitor_table.html', {'html_data': html_data, 'table_change_data': table_change_data})
title = '{db}庫表變更[來自:{host},檢測時間:{check_time}]'.format(db=kwargs['schema'], host=kwargs['describle'], check_time=check_time)
msg = EmailMessage(subject=title,
body=email_html_body,
from_email=EMAIL_FROM,
to=kwargs['receiver'].split(','),
)
msg.content_subtype = "html"
msg.send()
cursor.close()
conn.close()
對應的html文件：
# _monitor_table.html
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<style>
body {
font-family: Monaco, Menlo, Consolas, "Courier New", monospace;
font-size: 12px;
line-height: 1.42857143;
color: #333;
}
.box.box-primary {
border-top-color: #3c8dbc;
}
.box {
position: relative;
border-radius: 3px;
background: #ffffff;
border-top: 3px solid #d2d6de;
margin-bottom: 20px;
width: 100%;
box-shadow: 0 1px 1px rgba(0, 0, 0, 0.1);
}
.panel-danger > .panel-heading {
color: #a94442;
background-color: #f2dede;
border-color: #ebccd1;
}
.panel-info > .panel-heading {
color: #31708f;
background-color: #d9edf7;
border-color: #bce8f1;
}
.panel-success > .panel-heading {
color: #3c763d;
background-color: #dff0d8;
border-color: #d6e9c6;
}
.panel-heading {
padding: 6px 8px;
border-bottom: 1px solid transparent;
border-top-left-radius: 3px;
border-top-right-radius: 3px;
}
.panel-body {
padding: 6px;
color: #3c763d;
background-color: #f5f5f5;
}
</style>
</head>
<body>
<div >
<p>各位同仁好：</p>
<p> 表結構變更如下，請查閱，謝謝。</p>
{% if table_change_data %}
{% for row in table_change_data %}
{% if row.remove %}
<div >
<div >刪除的表</div>
<div >
{% for j in row.remove %}
{{ j }}
{% endfor %}
</div>
</div>
{% endif %}
{% endfor %}
{% for row in table_change_data %}
{% if row.add %}
<div >
<div >新增的表：{{ row.add.table_name }}_[表結構]</div>
<div >
<pre>{{ row.add.table_stru }}</pre>
</div>
</div>
{% endif %}
{% endfor %}
{% endif %}
{% if html_data %}
<div >
<div >變更的表結構[左側為變更前表結構、右側為變更後表結構、標色部分為差異]</div>
<div >
{{ html_data|safe }}
</div>
</div>
{% endif %}
</div>
</body>
</html>
最後在django後台添加定時任務或者輪詢任務
郵件輸出結果：
最後更新：2017-11-13 16:34:29