Inception服务的安装以及使用Python 3 实现MySQL的审计

--Inception 开始标志 
/*--user=username;--password=*****;--host=***.***.***.***;--enable-check;--port=3306;*/  
inception_magic_start;  

-- 中间开始是 sql 语句 
use mysql;  
CREATE TABLE adaptive_office(id int);  

--Inception 结束标志 
inception_magic_commit;

#!/usr/bin/python
# -\*-coding: utf-8-\*-
import pymysql
import sys, os
import time

# 执行还是校验 
operation = '--enable-check'
# operation = '--enable-execute;--enable-ignore-warnings;--enable-force'

# 发布目标服务器 
connstr_target = {'host': '***.***.***.***', 'port': 3306, 'user': 'root', 'password': '***', 'db': 'inception_testdb', 'charset': 'utf8mb4'}
# inception server
connstr_inception = {'host': '***.***.***.***', 'port': 6669, 'user': 'root', 'password': '', 'db': '',  'charset': 'utf8mb4'}



# inception 格式要求 
prefix_format = "/*--user={};--password={};--host={};{};--port={};*/ ".format(connstr_target['user'],
                                                                                      connstr_target['password'],
                                                                                      connstr_target['host'],
                                                                                      operation,
                                                                                      connstr_target['port']) \
                + '\n' \
                + "inception_magic_start;"
suffix_format = "inception_magic_commit;"


# 待执行的 sql 语句 
sql_demo1 = ''' use inception_testdb; 
            alter table test_inception 
            add column remark varchar(200);'''


try:
    # 将待执行的 sql 语句组合成 inception 识别的格式 
    sql_demo1_with_format = prefix_format + "\n" + sql_demo1 + "\n" + suffix_format

    print(sql_demo1_with_format)

    # 连接至 inception 服务器 
    conn_inception = pymysql.connect(host=connstr_inception['host'],
                                      port=connstr_inception['port'],
                                      user=connstr_inception['user'],
                                      password=connstr_inception['password'],
                                      charset=connstr_inception['charset'])


    cur = conn_inception.cursor()

    cur.execute(sql_demo1_with_format)
    result = cur.fetchall()
    num_fields = len(cur.description)
    field_names = [i[0] for i in cur.description]
    print(field_names)
    # 打印出来 Inception 对 MySQL 语句的审计结果 
    for row in result:
        print(row[0], "|", row[1], "|", row[2], "|", row[3], "|", row[4], "|", row[5], "|", row[6], "|",  row[7], "|",
              row[8], "|", row[9], "|", row[10])

    cur.close()
    conn_inception.close()

except  Exception as err:
    print(err)
finally:
    print('****************')

['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1']
1 | CHECKED | 0 | Audit completed | None | use inception_testdb | 0 | '0_0_0' | None | 0 | 
2 | CHECKED | 2 | Audit completed | Column 'remark' have existed.
Column 'remark' in table 'test_inception' have no comments.
Column 'remark' in table 'test_inception' is not allowed to been nullable.
Set Default value for column 'remark' in table 'test_inception' | alter table test_inception 
            add column remark varchar(200) | 1 | '0_0_1' | 116_196_94_250_8000_inception_testdb | 0 |