Automatic monitoring of MySQL table structure change scripts

Abstract:

How to monitor MySQL database table structure and table changes, notify related contacts, implement alerts or notifications?



Because the platform uses Django system implementation, so through the following code implementation (code low, can also be written as python file, pass parameters to execute) :

Simple idea:

Md5 is performed for all column values of the user-specified library and stored in the local database. The md5 is executed regularly every time, and the md5 is proofread and the mismatches are found for judgment

The new, deleted, or changed table structure is automatically found

# 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'Monitor table Structure Change Table'
        verbose_name_plural = verbose_name
        permissions = ()
        db_table = "dbaudit_monitor_schema"Copy the code

# 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 there is no record in the current library, initial full synchronization is performed
    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:
        # If present, start checking data
        old_data = list(MonitorSchema.objects.filter(table_schema=kwargs['schema']).values_list('table_name', flat=True))
        new_data = table_list

        Select * from table where table has been deleted
        table_remove = list(set(old_data).difference(set(new_data)))
        if table_remove:
            table_change_data.append({'remove': table_remove})
            Delete the table from the local library
            MonitorSchema.objects.filter(table_schema=kwargs['schema']).filter(table_name__in=table_remove).delete()

        Find the new table and handle it
        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)

        Find the same table and verify the table structure
        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)
                ifnew_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'* 3Update the new table table structure locally
                        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, 'Old table - Table Structure'.'New table - Table Structure', 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} table change [from :{host}, check time :{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()Copy the code

Corresponding HTML file:

# _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; The 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> Dear colleagues, the </p> <p> table structure changes as follows, please check, thank you. </p> {%if table_change_data %}
        {% for row in table_change_data %}
            {% if row.remove %}
                <div class="panel panel-danger">
                    <div class="panel-heading"> Delete table </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"> new table: {{row.add.table_name}}_[table structure]</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= > <div class= > <div class= > <div class="panel-body">
                {{ html_data|safe }}
            </div>
        </div>
    {% endif %}

</div>
</body>
</html>
Copy the code

Finally, add scheduled tasks or polling tasks in the Django background



Mail output result: