MyException - 我的异常网
当前位置:我的异常网» MySQL » 自动监控MySQL表结构变化脚本

自动监控MySQL表结构变化脚本

www.MyException.Cn  网友分享于:2013-12-18  浏览:0次
自动监控MySQL表结构变更脚本
摘要: 如何监控MySQL数据库表结构和表变更,并通知到相关的联系人、实现报警或通知? 由于平台采用django系统实现,因此通过如下代码实现(代码low,也可以写成python文件,传参数执行): 简单思路: 对用户指定库的所有列值进行md5,并存储到本地数据库,每次定时执行,校对md5,并找出不匹配的进行判断 会自动找出新增、删除、变更表结构的表# models.

如何监控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 class="box box-primary">
    <p>各位同仁好:</p>
    <p>  表结构变更如下,请查阅,谢谢。</p>

    {% if table_change_data %}
        {% for row in table_change_data %}
            {% if row.remove %}
                <div class="panel panel-danger">
                    <div class="panel-heading">删除的表</div>
                    <div class="panel-body">
                        {% for j in row.remove %}
                            {{ j }}
                        {% endfor %}
                    </div>
                </div>
            {% endif %}
        {% endfor %}

        {% for row in table_change_data %}
            {% if row.add %}
                <div class="panel panel-info">
                    <div class="panel-heading">新增的表:{{ row.add.table_name }}_[表结构]</div>
                    <div class="panel-body">
                        <pre>{{ row.add.table_stru }}</pre>
                    </div>
                </div>
            {% endif %}
        {% endfor %}
    {% endif %}

    {% if html_data %}
        <div class="panel panel-success">
            <div class="panel-heading">变更的表结构[左侧为变更前表结构、右侧为变更后表结构、标色部分为差异]</div>
            <div class="panel-body">
                {{ html_data|safe }}
            </div>
        </div>
    {% endif %}

</div>
</body>
</html>

最后在django后台添加定时任务或者轮询任务

邮件输出结果:
fbabeb503b7b334e82796db18be8463a403d1fd8
72310593bc9158613ee3796b72c823f9cbc18138


版权声明:本文内容由互联网用户自发贡献,本社区不拥有所有权,也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:yqgroup@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

文章评论

我的丈夫是个程序员
我的丈夫是个程序员
旅行,写作,编程
旅行,写作,编程
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
一个程序员的时间管理
一个程序员的时间管理
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
老程序员的下场
老程序员的下场
Java程序员必看电影
Java程序员必看电影
那些争议最大的编程观点
那些争议最大的编程观点
鲜为人知的编程真相
鲜为人知的编程真相
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
我是如何打败拖延症的
我是如何打败拖延症的
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
10个调试和排错的小建议
10个调试和排错的小建议
编程语言是女人
编程语言是女人
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
程序员的鄙视链
程序员的鄙视链
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
如何成为一名黑客
如何成为一名黑客
程序员都该阅读的书
程序员都该阅读的书
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员和编码员之间的区别
程序员和编码员之间的区别
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
中美印日四国程序员比较
中美印日四国程序员比较
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
漫画:程序员的工作
漫画:程序员的工作
程序员必看的十大电影
程序员必看的十大电影
为什么程序员都是夜猫子
为什么程序员都是夜猫子
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
每天工作4小时的程序员
每天工作4小时的程序员
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
程序员应该关注的一些事儿
程序员应该关注的一些事儿
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有